Results 1 to 7 of 7

Thread: DECODE help

  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: DECODE help

    I am using Oracle 8.0.6.3.0

    I need help with a decode statement that will do the following:

    If ITEM.P_DAFEHVIND or ITEM.P_DAFLEYIND is 1 , return “TRP-D”

    If ITEM.P_FODENLEYIND is 1 return “TRP-F”

    If (ITEM.P_DAFEHVIND or ITEM.P_DAFLEYIND is 1) and (ITEM.P_FODENLEYIND is 1) return “TRP-D”

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink


    If each is a separate decode do this:
    Code:
    Decode(SIGN(ITEM.P_DAFEHVIND+ITEM.P_DAFLEYIND),1,'TRP-D',NULL)
    Decode(ITEM.P_FODENLEYIND,1,'TRP-F',NULL)
    Decode(SIGN(ITEM.P_DAFEHVIND+ITEM.P_DAFLEYIND),1,
         Decode(ITEM.P_FODENLEYIND,1,'TRP-D',NULL),NULL)
    Else you can combine into one BIG decode:
    Code:
    Decode(SIGN(ITEM.P_DAFEHVIND+ITEM.P_DAFLEYIND),1,'TRP-D',
    Decode(ITEM.P_FODENLEYIND,1,'TRP-F',
    Decode(SIGN(ITEM.P_DAFEHVIND+ITEM.P_DAFLEYIND),1,
         Decode(ITEM.P_FODENLEYIND,1,'TRP-D',NULL),NULL)))

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Unfortunately, 8.0.6 doesn't support CASE; but, if you - some day in the future - decide to upgrade (to at least 8i), this might work:
    Code:
    SELECT CASE
             WHEN p_dafehvind = 1 OR p_dafleyind = 1 THEN 'TRP_D'
             WHEN P_FODENLEYIND = 1 THEN 'TRP_F'
             WHEN (p_dafehvind = 1 OR p_dafleyind = 1) AND
                  (p_fodenleyind = 1) THEN 'TRP_D'
           END
    FROM your_table;
    Until then, LKBrwn_DBA is your only hope.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Will 8.0.6 do INLINE VIEWS?

    Code:
    SELECT DECODE(D,'TRP-D',D, 
            DECODE(F,'TRP-F',F, 
             DECODE(D2,'TRP-D',D2,null)))
    FROM (SELECT
          Decode(SIGN(ITEM.P_DAFEHVIND+ITEM.P_DAFLEYIND),1,'TRP-D',NULL) as D
          Decode(ITEM.P_FODENLEYIND,1,'TRP-F',NULL) as F
          Decode(SIGN(ITEM.P_DAFEHVIND+ITEM.P_DAFLEYIND),1,
           Decode(ITEM.P_FODENLEYIND,1,'TRP-D',NULL),NULL) as D2
          FROM TABLE)
    I dunno, in looking at it, I wonder if it is less complex.
    -cf

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In this situation, CASE is clean and DECODE isn't; too bad it can't be used.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    I am using Oracle 8.0.6.3.0
    Maybe it's time to UPGRADE

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Sep 2005
    Posts
    220
    Unfortunately I cannot upgrade. It's out of my hands. This query is close but not quite there yet. Here is the query:

    Code:
    SELECT ITEM 
         , ITEM.P_PMVACODE 
         , NVL(ITEM.P_DAFEHVIND,0)P_DAFEHVIND
    	 , NVL(ITEM.P_DAFLEYIND,0) P_DAFLEYIND
    	 , NVL(ITEM.P_FODENLEYIND,0) P_FODENLEYIND 
         , DECODE(SIGN(ITEM.P_DAFEHVIND+ITEM.P_DAFLEYIND),1,'TRP-D', 
    	   DECODE(ITEM.P_FODENLEYIND,1,'TRP-F',						
    	   DECODE(SIGN(ITEM.P_DAFEHVIND+ITEM.P_DAFLEYIND),1,		
           DECODE(ITEM.P_FODENLEYIND,1,'TRP-D',NULL),NULL))) msg	
    FROM ITEM
    WHERE ITEM.P_PMVACODE IN ('14','17','24','27','87')
    and here are the data conditions and how I need them set:

    Code:
     
    P_DAFEHVIND	P_DAFLEYIND	P_FODENLEYIND	MSG	   
          1	            0	            0	      TRP-D	   
          0	            1	            0	      TRP-D	   
          1	            1	            0	      TRP-D	   
    				   
          0	            0	            1	      TRP-F	   
          1	            0	            1	      TRP-D	   
          0	            1	            1	      TRP-D	   
    				   
          1	            0	            1	      TRP-D	   
          0	            1	            1	      TRP-D	   
          1	           1 	            1	      TRP-D

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •