2 Replies Latest reply on Aug 6, 2015 5:05 AM by Gianni Ceresa

    Error when using case statement

    00649e65-9309-43f1-9049-27cb93601177

      I'm getting the following error when running a report:

       

      "Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

      State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. Please have your System Administrator look at the log for more details on this error. (HY000)"


      The report I've created has a column that calculates TAT and then a column with a Case statement to group TAT results. Case statement is the following:


      CASE  WHEN CAST (TIMESTAMPDIFF(SQL_TSI_HOUR, "Service Record"."Open Date/Time", "Service Record"."Close Date/Time") AS Int) <= 24 THEN '24 Hrs' WHEN CAST (TIMESTAMPDIFF(SQL_TSI_HOUR, "Service Record"."Open Date/Time", "Service Record"."Close Date/Time") AS Int) <= 168 THEN '7 Days' WHEN CAST (TIMESTAMPDIFF(SQL_TSI_HOUR, "Service Record"."Open Date/Time", "Service Record"."Close Date/Time") AS Int) <= 720 THEN '30 Days' ELSE CAST (TIMESTAMPDIFF(SQL_TSI_HOUR, "Service Record"."Open Date/Time", "Service Record"."Close Date/Time") AS Int) END


      The report will run with only the TAT column, but returns the error when the Case statement is added. Does anyone have any ideas about what might be wrong with my case statement? Thank you in advance.

        • 1. Re: Error when using case statement
          Ebin

          The criteria mentioned in the case statement is overlapping with each other.. For example the the records whose hour difference is greater than 24 can fall under either '24 Hrs' or '7 Days'. You need to define the boundaries in case statement. Try something like this :

           

          CASE 

          WHEN CAST (TIMESTAMPDIFF(SQL_TSI_HOUR, "Service Record"."Open Date/Time", "Service Record"."Close Date/Time") AS Int) <= 24 THEN '24 Hrs'

           

          WHEN CAST (TIMESTAMPDIFF(SQL_TSI_HOUR, "Service Record"."Open Date/Time", "Service Record"."Close Date/Time") AS Int) <= 168 AND CAST (TIMESTAMPDIFF(SQL_TSI_HOUR, "Service Record"."Open Date/Time", "Service Record"."Close Date/Time") AS Int) > 24 THEN '7 Days'

           

          WHEN CAST (TIMESTAMPDIFF(SQL_TSI_HOUR, "Service Record"."Open Date/Time", "Service Record"."Close Date/Time") AS Int) <= 720 AND CAST (TIMESTAMPDIFF(SQL_TSI_HOUR, "Service Record"."Open Date/Time", "Service Record"."Close Date/Time") AS Int) > 168 THEN '30 Days'

           

          ELSE CAST (TIMESTAMPDIFF(SQL_TSI_HOUR, "Service Record"."Open Date/Time", "Service Record"."Close Date/Time") AS Int) END

          • 2. Re: Error when using case statement
            Gianni Ceresa

            Hi,

            Why don't you use SQL_TSI_DAY? so you can make the test on 1, 7 & 30, will be easier to read in case you have a doubt of how many days are 168 &  720 hours (just need to double-check once that there is no rounding in the result like 23h rounded to 1day but if I don't remember wrong there isn't any)  .

            I don't see a reason to do a cast on the result of timestampdiff in the WHEN, it's a number that you get, not a date or text, so it can be used directly.

             

            You must cast the ELSE as varchar as the others are varchars and the ELSE return a int, that's can be confusing for OBIEE in knowing what that column type is.

             

            The overlapping criteria isn't an issue, I guess that has wrote, the first matching WHEN is used.