4 Replies Latest reply on Jun 9, 2017 10:16 AM by Hari Ankem

    SQL - impossible to extract with 'Case When'

    Felipe de Castro

      Hi everyone,

        

          I'm trying to extract with a custom SQL, and it's returning ORA-01858 error: a non-numeric character found where a digit was expected.

       

          I know that the problem is the 'Case When' statement that i included in the SQL, because before that, it was working.

       

          Note: In the PL it is working well, the problem is only on Tableau.

       

          The SQL:

      (

      SELECT

                 a.cd_att,

                 h.ds_ori_att,

                 ....

                 ....

                 ....        

       

                 CASE WHEN TRUNC(a.dt_mov) <= dt_range.date - 1 AND TRUNC(NVL(a.dt_lib, sysdate)) > dt_range.date - 1 THEN dt_range.date ELSE TO_DATE(01/01/1960) END "Report Date"    

          

            FROM mov a

            ...

            ...

            ...

            ...

           

            JOIN (SELECT

                           TRUNC(sysdate - 11) + rownum date

                        FROM all_tables

                        WHERE (sysdate - 11) + rownum <= sysdate - 6) dt_range

               ON (TRUNC(a.dt_mov) <= dt_range.date - 1 and TRUNC(NVL(a.dt_lib, sysdate)) > dt_range.date - 1) 

       

      )

       

      I'll be thankful fro any help.