5 Replies Latest reply: Sep 13, 2013 3:01 PM by user515689 RSS

    embedded conditional within select statement

    user515689


      Hi folks.

       

      I'm trying to set up a conditional block within a select statement. I've browsed the forums and seen suggestions with DECODE and the recurring insistence that IF/ELSE won't or can't work within non PL/SQL, which is what this would be.

       

      I'm trying to plug in a simple string value if a column's value is null. The WHEN block either doesn't work either or I'm doing it wrong, but I'd take any suggestions from those of you more knowledgeable than I on this.

       

      Here's the block:

      ------------------

      SELECT c.CHCTRLID AS "CC ID", c.STATUS3, b.VPTIMSTAMP,
      a.VP_DATE AS "Submit Date", a.CRITI_EXP_DT AS "Critical Date", c.REQ_TYPE, c.SUBJLETTER,

      c.PGCOUNT AS "Page Total", c.EFFECT_PGCOUNT,
      c.PUBTYPE AS "Publication Type", c.COMPLEXITYLEVEL,
      c.PUBDATE, a.BRANCH, a.BRANCH_SEC, a.PRIORITY, c.WRITER, a.REQ_INFO_DESCRIPT, a.DEPT,

      Abs(Round((PUBDATE-VPTIMSTAMP),0)) AS "SLA Level in Days",

       

       

      --   WHEN PUBDATE IS NULL
      --   then return 'n/a' AS "Secondary SLA",
      --    WHEN OTHERS THEN
      --    RETURN Abs(Round(SYSDATE-VPTIMSTAMP),0)) AS "Secondary SLA",
       

      a.OPS_MAN_CHG, a.OPS_MAN_PPCHAP,a.STRATEGY_UPDATE, a.CRITICAL_EXP

      FROM CHG_CTRL_ADM b INNER JOIN CHG_CTRL_USR a
      ON b.CHCTRLID = a.CHCTRLID
      INNER JOIN ADM_SUPP_INDX c ON b.CHCTRLID = c.CHCTRLID

      WHERE c.STATUS3 != 'Cancelled'  AND a.VP_DATE >   '04-APR-11'
      ORDER BY a.CHCTRLID

      ---------------------------

      I've commented out the WHEN block because it wouldn't work, but essentially, that's what I'm trying to accomplish.

       

      Thank you.

        • 1. Re: embedded conditional within select statement
          thomaso

          Use Oracle CASE:

          SELECT c.CHCTRLID AS "CC ID", c.STATUS3, b.VPTIMSTAMP,
          a.VP_DATE AS "Submit Date", a.CRITI_EXP_DT AS "Critical Date", c.REQ_TYPE, c.SUBJLETTER,

          c.PGCOUNT AS "Page Total", c.EFFECT_PGCOUNT,
          c.PUBTYPE AS "Publication Type", c.COMPLEXITYLEVEL,
          c.PUBDATE, a.BRANCH, a.BRANCH_SEC, a.PRIORITY, c.WRITER, a.REQ_INFO_DESCRIPT, a.DEPT,

          Abs(Round((PUBDATE-VPTIMSTAMP),0)) AS "SLA Level in Days",

          (CASE

          WHEN PUBDATE IS NULL then 'n/a',
          ELSE TO_CHAR(Abs(Round(SYSDATE-VPTIMSTAMP),0)))

          END) AS "Secondary SLA",
          a.OPS_MAN_CHG, a.OPS_MAN_PPCHAP,a.STRATEGY_UPDATE, a.CRITICAL_EXP

          FROM CHG_CTRL_ADM b INNER JOIN CHG_CTRL_USR a
          ON b.CHCTRLID = a.CHCTRLID
          INNER JOIN ADM_SUPP_INDX c ON b.CHCTRLID = c.CHCTRLID

          WHERE c.STATUS3 != 'Cancelled'  AND a.VP_DATE >   '04-APR-11'
          ORDER BY a.CHCTRLID

           

          http://www.oracle-base.com/articles/9i/case-expressions-and-statements-9i.php


          HTH

          T.

          • 2. Re: embedded conditional within select statement
            Sven W.

            Decode is possible, but CASE is better readable.

             

            Try this as a column value

             

            CASE WHEN PUBDATE IS NULL
              then 'n/a' 
                else to_char(Abs(Round(SYSDATE-VPTIMSTAMP),0)) )  end AS "Secondary SLA",
            



            second possibility for NULL check is use NVL or NVL2.

            The datatype of both results needs to match.


            NVL2


            as in

            nvl2(PUBDATE, to_char(Abs(Round(SYSDATE-VPTIMSTAMP))) , 'n/a')
            


            • 3. Re: embedded conditional within select statement
              user515689

              Well, I thought that would do it, but for some reason I'm still getting an 00905 error, missing keyword FROM.

               

              Not really sure why, though. The syntax looks correct. I'll keep tweaking it and see.

              • 4. Re: embedded conditional within select statement
                Sven W.

                missing keyword FROM.

                 

                usually means you forgot a comma in the column list.

                 

                It also looks like that I added one closing parenthesis too many in the case version. Remove one and test again. Sorry for the typo in that case.

                • 5. Re: embedded conditional within select statement
                  user515689

                  Well, Sven, I wished that had done it but while there may have been one too many closing parentheses characters, removing it and retesting still shows the same error, ORA-00905: missing keyword.

                   

                  Ok, I'll try the NVL2 function.

                   

                  Ok, Sven - good news! THIS worked, the NVL2 function. I'll duly document it as such for further embedded SQL queries!

                   

                  Thank you so much!