代码之家  ›  专栏  ›  技术社区  ›  Moin Khan

查询不比较负值

  •  0
  • Moin Khan  · 技术社区  · 5 年前

    我想比较查询中的两个值。当我传递正常值时,下面的查询可以正常工作,但是当我传递负值时,我没有得到正确的答案。我怎么能解决这个问题。

    IF value1 < min1 AND value1 > max1 AND  (lower(severity1) = lower('Severe-1') OR lower(severity1) = lower('Critical'))
        THEN
        update audit_ques_link SET line_stop = 0 where audit_question_id IN(SELECT max(audit_question_id) FROM audit_ques_link);
        RETURN QUERY Select cast(0 as numeric) as msg,cast('Stop line' as character varying), am.audit_id, ddl8.dd_value  as stageno,
            ddl1.dd_value as stage, ddl2.dd_value, ddl3.activity_id as activity_id, ddl4.dd_value as activity, 
            ddl5.dd_value as process, ddl3.product as product, ddl3.assembly as assembly, ddl6.dd_value as line_no,
            ddl7.dd_value as area
            from audit_ques_detail am 
            left join dd_type_details ddl1 on ddl1.dd_id= am.stage
            left join dd_type_details ddl2 on ddl2.dd_id= am.stage_no
            left join audit_master ddl3 on ddl3.audit_id= am.audit_id
            left join dd_type_details ddl4 on ddl4.dd_id= ddl3.activity_id
            left join dd_type_details ddl5 on ddl5.dd_id = ddl3.process
            left join dd_type_details ddl6 on ddl6.dd_id = ddl3.line_no
            left join dd_type_details ddl7 on ddl7.dd_id = ddl3.area
            left join dd_type_details ddl8 on ddl8.dd_id =  am.stage_no
            where audit_ques_id = (select question_id from audit_ques_link  order by audit_question_id desc limit 1);
        ELSE
    
        IF value1 < min1 OR value1 > max1
        THEN
        update audit_ques_link SET isCorrect = 0 where audit_question_id IN(SELECT max(audit_question_id) FROM audit_ques_link);
        ELSe
        update audit_ques_link SET isCorrect = 1 where audit_question_id IN(SELECT max(audit_question_id) FROM audit_ques_link);
        END IF;
    
        update audit_ques_link SET line_stop = 1 where audit_question_id IN(SELECT max(audit_question_id) FROM audit_ques_link);
        RETURN QUERY Select cast(1 as numeric) as msg,cast('Correct answer' as character varying), am.audit_id, ddl8.dd_value  as stageno,
            ddl1.dd_value as stage, ddl2.dd_value, ddl3.activity_id as activity_id, ddl4.dd_value as activity, 
            ddl5.dd_value as process, ddl3.product as product, ddl3.assembly as assembly, ddl6.dd_value as line_no,
            ddl7.dd_value as area
            from audit_ques_detail am 
            left join dd_type_details ddl1 on ddl1.dd_id= am.stage
            left join dd_type_details ddl2 on ddl2.dd_id= am.stage_no
            left join audit_master ddl3 on ddl3.audit_id= am.audit_id
            left join dd_type_details ddl4 on ddl4.dd_id= ddl3.activity_id
            left join dd_type_details ddl5 on ddl5.dd_id = ddl3.process
            left join dd_type_details ddl6 on ddl6.dd_id = ddl3.line_no
            left join dd_type_details ddl7 on ddl7.dd_id = ddl3.area
            left join dd_type_details ddl8 on ddl8.dd_id =  am.stage_no
            where audit_ques_id = (select question_id from audit_ques_link  order by audit_question_id desc limit 1);
        END IF;
    
    0 回复  |  直到 5 年前