10 Replies Latest reply: Feb 26, 2013 7:29 AM by 982108 RSS

    My count(*) > 0, but the error message displays anyway.

    982108
      WITH client_codes AS (
        SELECT prod_cd code, prod_desc description
        FROM products
        UNION ALL 
        SELECT code code, code_value_text description
        FROM code_list_dtls
        WHERE code_list_name = 'OLD_CLIENT_NAMES')
      SELECT COUNT(*)
      INTO vCount
      FROM ae_cases aec
      LEFT JOIN Client_Codes cc ON aec.custom_02 = cc.code
      WHERE aec.custom_01 = cc.description
      and vCaseID = aec.case_id;
      I have a case where the custom_01 field does match the description and my count = 1, but the FAIL error message continues to print. My test should pass!
      IF vCount > 0 then
      vErrorText := 'PASS';
      ELSE 
      vErrorText := 'FAIL';
        • 1. Re: My count(*) > 0, but the error message displays anyway.
          TPD-Opitz
          what's the value of vCaseId?

          bye
          TPD
          • 2. Re: My count(*) > 0, but the error message displays anyway.
            982108
            Case ID : 2012P1000030
            prod_Cd: 2
            prod_desc: Kelly Industries

            custom_02: 2
            custom_01: Kelly Industries
            • 3. Re: My count(*) > 0, but the error message displays anyway.
              user1983440
              Can you post the segment of code that follows the code below, up through and including the code that "prints" the value for vCount?
              IF vCount > 0 then
              vErrorText := 'PASS';
              ELSE 
              vErrorText := 'FAIL';
              • 4. Re: My count(*) > 0, but the error message displays anyway.
                rp0428
                We can't debug code that we can't see. Post the code that you are using; not just bits and pieces of it. And if you have code or a session that you are using to call a procedure/function you need to post what you are doing and the result you are getting.
                >
                I have a case where the custom_01 field does match the description and my count = 1,
                >
                Again - bits and pieces of based on some unknown set of data.

                You are using an equi-join so only data where the field matches the description will be selected. We have no way of knowing how much data that might be.
                >
                IF vCount > 0 then
                vErrorText := 'PASS';
                ELSE
                vErrorText := 'FAIL';
                >
                That code won't 'print' anything at all.

                If vErrorText has a value of 'FAIL' then vCount IS NOT greater than zero regardless of what you may think.
                • 5. Re: My count(*) > 0, but the error message displays anyway.
                  riedelme
                  979105 wrote:
                  WITH client_codes AS (
                  SELECT prod_cd code, prod_desc description
                  FROM products
                  UNION ALL 
                  SELECT code code, code_value_text description
                  FROM code_list_dtls
                  WHERE code_list_name = 'OLD_CLIENT_NAMES')
                  SELECT COUNT(*)
                  INTO vCount
                  FROM ae_cases aec
                  LEFT JOIN Client_Codes cc ON aec.custom_02 = cc.code
                  WHERE aec.custom_01 = cc.description
                  and vCaseID = aec.case_id;
                  I have a case where the custom_01 field does match the description and my count = 1, but the FAIL error message continues to print. My test should pass!
                  IF vCount > 0 then
                  vErrorText := 'PASS';
                  ELSE 
                  vErrorText := 'FAIL';
                  Find out what value you are getting for vCount. I suspect NULL from no rows returned by the query but that's just a guess. Find out what the value really is.
                  • 6. Re: My count(*) > 0, but the error message displays anyway.
                    982108
                    BEGIN
                    WITH client_codes AS (
                      SELECT prod_cd code, prod_desc description
                      FROM products
                      UNION 
                      SELECT code code, code_value_text description
                      FROM code_list_dtls
                      WHERE code_list_name = 'OLD_CLIENT_NAMES')
                    SELECT COUNT(*)
                    INTO vCount
                    FROM ae_cases aec
                    LEFT JOIN Client_Codes cc ON aec.custom_02 = cc.code
                    WHERE aec.custom_01 = cc.description
                    and vCaseID = aec.case_id;
                    
                    IF vCount > 0 then
                       ionGrandChildSeqNbr := 0;
                      ovDiscrep_Values      := NULL;
                      ovErrorFlag := 'y';
                      vErrorText := 'PASS';
                    ElSE
                      ionGrandChildSeqNbr := 0;
                      ovDiscrep_Values      := NULL;
                      ovErrorFlag := 'Y';
                      
                      vErrorText := 'FAIL.' || 'VCount: ' || vcount;
                         
                    END IF;
                         ovErrorText := vErrorText;
                    SET serveroutput ON
                    DECLARE
                         vCaseID           VARCHAR2(50) := '2012P1000030';
                         vRuleName         VARCHAR2(50) := 'CLIENT_ID_CHECK';
                         vParentValue      VARCHAR2(50) := 1;
                         vErrorCd          NUMBER := '-99999';
                         vErrorFlag        VARCHAR2(50);
                         vErrorText        VARCHAR2(100);
                         vChildSeqNbr      NUMBER;
                         vGrandChildSeqNbr NUMBER;
                         vCurPosition      VARCHAR2(50);
                         vDiscrep_values   VARCHAR2(50);
                    BEGIN
                         PROSAR_CUSTOMUTILITY.CLIENT_ID_CHECK (vCaseID,
                                                                      vRuleName,
                                                                      vParentValue,
                                                                      vErrorCd,
                                                                      vErrorFlag,
                                                                      vErrorText,
                                                                      vChildSeqNbr,
                                                                      vGrandChildSeqNbr,
                                                                      vCurPosition,
                                                                      vDiscrep_values);
                         
                         DBMS_OUTPUT.PUT_LINE('vCaseID = ' || vCaseID);
                         DBMS_OUTPUT.PUT_LINE('vRuleName = ' || vRuleName);
                         DBMS_OUTPUT.PUT_LINE('vParentValue = ' || vParentValue);
                         DBMS_OUTPUT.PUT_LINE('vErrorCd = ' || vErrorCd);
                         DBMS_OUTPUT.PUT_LINE('vErrorFlag = ' || vErrorFlag);
                         DBMS_OUTPUT.PUT_LINE('vErrorText = ' || vErrorText);
                         DBMS_OUTPUT.PUT_LINE('vChildSeqNbr = ' || vChildSeqNbr);
                         DBMS_OUTPUT.PUT_LINE('vGrandChildSeqNbr = ' || vGrandChildSeqNbr);
                         DBMS_OUTPUT.PUT_LINE('vCurPosition = ' || vCurPosition);
                         DBMS_OUTPUT.PUT_LINE('vDiscrep_values = ' || vDiscrep_values);
                    END;
                    Results:
                    anonymous block completed
                    vCaseID = 2012P1000030
                    vRuleName = CLIENT_ID_CHECK
                    vParentValue = 1
                    vErrorCd = -99999
                    vErrorFlag = Y
                    vErrorText = FAIL.VCount: 0
                    vChildSeqNbr =
                    vGrandChildSeqNbr = 0
                    vCurPosition =
                    vDiscrep_values =
                    WITH client_codes AS (
                      SELECT prod_cd code, prod_desc description
                      FROM products
                      UNION 
                      SELECT code code, code_value_text description
                      FROM code_list_dtls
                      WHERE code_list_name = 'OLD_CLIENT_NAMES')
                    SELECT COUNT(*)
                    FROM ae_cases aec
                    LEFT JOIN Client_Codes cc ON aec.custom_02 = cc.code
                    WHERE aec.custom_01 = cc.description
                    and aec.case_id = '2012P1000030'
                    Results: COUNT(*) = 1

                    I do not understand wh the select statement when ran on it's own will return 1, but the procedure call returns 0.
                    • 7. Re: My count(*) > 0, but the error message displays anyway.
                      rp0428
                      Thanks for posting SOME of the code. Starting to look at it now.

                      Is there some reason you can't post the entire code? You didn't provide the signature of the function or the variable declarations so we can see what default values, if any, might be being used.

                      But since it is 'vCount' that you think is the issue wouldn't it make sense to print out the value of 'vCount' so you can actually see what it is?

                      Please edit you post to include a printout of 'vCount' so we can see what value it has.

                      Also, a very common cause of things (query or code) working differently in PL/SQL that when you use a manual query or anonymous block is that ROLES ARE DISABLED in PL/SQL. So you need to confirm that your manual query actually uses the same tables/views as the procedure does. When things actually work but results are different there are often public synonyms involved that alter the scope.

                      1. What user created the procedure?
                      2. What user owns the tables/views that the procedure is using?
                      3. Is the same user being used to execute the procedure test AND the manual test?
                      4. Do any of those tables/views have public synonyms on them.
                      • 8. Re: My count(*) > 0, but the error message displays anyway.
                        Peter Gjelstrup
                        Hello,

                        What's the data type for ae_cases.case_id. CHAR maybe?
                        SQL> create table t (id char(5));
                        
                        Table created.
                        
                        SQL> insert into t values ('A');
                        
                        1 row created.
                        
                        SQL> commit;
                        
                        Commit complete.
                        
                        SQL>
                        SQL> select * from t where id = 'A';
                        
                        ID
                        -----
                        A
                        
                        SQL>
                        SQL> create or replace function f(iv varchar2) return number
                          2  as
                          3     n number;
                          4  begin
                          5     select count(*)
                          6     into n
                          7     from t
                          8     where id = iv;
                          9
                         10     return n;
                         11  end;
                         12  /
                        
                        Function created.
                        
                        SQL>
                        SQL> select f('A') from dual;
                        
                            F('A')
                        ----------
                                 0
                        
                        SQL> select f(cast('A' as CHAR(5))) from dual;
                        
                        F(CAST('A'ASCHAR(5)))
                        ---------------------
                                            1
                        
                        
                        SQL>
                        SQL> create or replace function f(iv t.id%type) return number
                          2  as
                          3     n number;
                          4  begin
                          5     select count(*)
                          6     into n
                          7     from t
                          8     where id = iv;
                          9
                         10     return n;
                         11  end;
                         12  /
                        
                        Function created.
                        
                        
                        SQL> select f('A') from dual;
                        
                            F('A')
                        ----------
                                 1
                        
                        SQL>
                        Regards
                        Peter
                        • 9. Re: My count(*) > 0, but the error message displays anyway.
                          982108
                          Thank you all for trying to assist me. As it turns out the issue was a syntax error. I had been assigning a null case value by using vCaseID instead of ivCaseID, which is what gets passed.
                          • 10. Re: My count(*) > 0, but the error message displays anyway.
                            982108
                            This was answered outside of the forum.