Loading ...
Sorry, an error occurred while loading the content.

Oracle and DECODE

Expand Messages
  • Andrew Mills
    Hello Everyone: I know in Oracle, you can use a DECODE statement to do an If..Else type scenario. But I am having a heck of a hard time getting the DECODE
    Message 1 of 1 , Aug 2, 2011
    • 0 Attachment
      Hello Everyone:



      I know in Oracle, you can use a "DECODE" statement to do an "If..Else" type
      scenario. But I am having a heck of a hard time getting the DECODE to work.
      I have the following statements that were building a string and then I would
      execute the value of the string (dynamic SQL) but I have been told I am not
      allowed to use this approach, hence trying to get it working with DECODE.
      I have two sets of "IF" statements, all checking a parameter value
      (p_TaskStatus and also p_From / ToShipDate)



      The best I have so far is the following DECODE statement for the first "IF"
      check. My thought is each "IF Section" is another DECODE so I would have
      three total. But I am having a heck of a time getting the second one
      correct (and I am not certain the first one is)...

      AND NVL(te.act_end_date, '~') = DECODE(p_TaskStatus, 'I',
      '~', DECODE(p_TaskStatus, 'C', '!', ''), '!');





      IF (p_TaskStatus = 'I') THEN

      whereClauseStatus := ' AND act_end_date IS NULL ';

      ELSIF (p_TaskStatus = 'C') THEN

      whereClauseStatus := ' AND act_end_date IS NOT NULL ';

      ELSE

      whereClauseStatus := '';

      END IF;





      IF(p_TaskStatus = 'C') THEN

      IF(p_FromShipDate IS NOT NULL) THEN

      whereClauseFromDate := ' AND
      TRUNC(te.act_end_date) >= TO_DATE(''' || p_FromShipDate ||
      ''',''MM/dd/yyyy'') ';

      END IF;

      IF (p_ToShipDate IS NOT NULL) THEN

      whereClauseToDate := ' AND
      TRUNC(te.act_end_date) <= TO_DATE(''' || p_ToShipDate || ''',''MM/dd/yyyy'')
      ';

      END IF;

      ELSE

      IF(p_FromShipDate IS NOT NULL) THEN

      whereClauseFromDate := ' AND
      TRUNC(te.currdate) >= TO_DATE(''' || p_FromShipDate || ''',''MM/dd/yyyy'')
      ';

      END IF;

      IF (p_ToShipDate IS NOT NULL) THEN

      whereClauseToDate := ' AND
      TRUNC(te.currdate) <= TO_DATE(''' || p_ToShipDate || ''',''MM/dd/yyyy'') ';

      END IF;

      END IF;







      Thanks,

      Andy Mills





      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.