3 Replies Latest reply: Feb 7, 2013 4:46 PM by Frank Kulash RSS

    Case statement with variable

    7688438
      Hi Experts,

      I'm trying to write a case statement with a variable in it.


      SELECT

      CASE WHEN MST.JOB_NAME=#LOAD_NAME THEN TO_CHAR(LAST_SUCCESSFUL_RUN_TIME,'YYYYMMDD')
      ELSE

      TO_CHAR(TO_DATE('19590101','YYYYMMDD')
      END

      FROM KSTGDB.KW_ETL_JOB_RUN_MSTR MST WHERE JOB_NAME=#LOAD_NAME;

      DESC KW_ETL_JOB_RUN_MSTR;

      Name Null Type
      ------------------------ -------- ------------------
      JOB_NAME NOT NULL VARCHAR2(255 CHAR)
      LAST_SUCCESSFUL_RUN_TIME DATE


      My scenario:

      if the JOB_NAME =#LOAD_NAME THEN GET THE LAST_SUCCESSFUL_RUN_TIME
      else
      get the default value as 19590101


      Need to write any procedure or function??

      Help please?


      Regards

      R
        • 1. Re: Case statement with variable
          user639304
          Hi,

          Is #LOAD_NAME a column name or a parameter?
          • 2. Re: Case statement with variable
            7688438
            It's a parameter like

            'XYZ'

            or

            'ABC'


            Regards

            R
            • 3. Re: Case statement with variable
              Frank Kulash
              Hi,
              7688438 wrote:
              Hi Experts,

              I'm trying to write a case statement with a variable in it.
              What front end are you using? The ways you define variables, and sometiemds even how you use them in SQL statements, differs in different environments.
              >
              SELECT

              CASE WHEN MST.JOB_NAME=#LOAD_NAME THEN TO_CHAR(LAST_SUCCESSFUL_RUN_TIME,'YYYYMMDD')
              ELSE

              TO_CHAR(TO_DATE('19590101','YYYYMMDD')
              Why not just '19590101'?
              END

              FROM KSTGDB.KW_ETL_JOB_RUN_MSTR MST WHERE JOB_NAME=#LOAD_NAME;

              DESC KW_ETL_JOB_RUN_MSTR;

              Name Null Type
              ------------------------ -------- ------------------
              JOB_NAME NOT NULL VARCHAR2(255 CHAR)
              LAST_SUCCESSFUL_RUN_TIME DATE


              My scenario:

              if the JOB_NAME =#LOAD_NAME THEN GET THE LAST_SUCCESSFUL_RUN_TIME
              else
              get the default value as 19590101


              Need to write any procedure or function??
              No, you shouldn't need anything like that.
              Help please?
              What exactly is the problem? Assuming #LOAD_NAME is a string variable in your environment, the CASE expression above should work.

              If I were doing this in SQL*Plus, I might use a bind variable:
              VARIABLE  load_name  VARCHAR2 (255)
              EXEC      :load_name := 'FUBAR';
              
              SELECT  CASE 
                        WHEN  job_name = :load_name 
                        THEN  TO_CHAR (last_successful_run_time, 'YYYYMMDD')
                        ELSE  '19590101'
                   END          AS last_run_time
              FROM      kstgdb.kw_etl_job_run_mstr 
              WHERE     job_name = :load_name
              ;