• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

Using Decode in Oracle

I want add an additional condition in the below decode function. If c_type = p or px and h_type is zero then CNT.

Please let me know if anything is not clear
LTRIM(TO_CHAR(DECODE(TRIM(C_TYPE), 'P', H_TYPE, 'PI', H_TYPE, CNT),
     DECODE(LEAST(GREATEST(DECODE(TRIM(C_TYPE),'P',H_TYPE,'PI',H_TYPE,CNT), 400),
                                  500),
                            600,
                            '000',
                            '0000')))

Open in new window

0
cutie_smily
Asked:
cutie_smily
  • 3
1 Solution
 
sdstuberCommented:
you have 2 decodes I'm not sure which, or both you wanted to modify.

you will have to NOT use decode for this though since decode can't do "AND" or "OR" conditions
you will need to use CASE

maybe something like...


LTRIM(TO_CHAR(
                 CASE WHEN TRIM(c_type) IN ('P', 'PX') 
                       AND h_type = 0 THEN cnt END,
                 DECODE(
                     LEAST(
                         GREATEST(
                             CASE WHEN TRIM(c_type) IN ('P', 'PX') 
                                   AND h_type = 0 THEN cnt END,
                             400
                         ),
                         500
                     ),
                     600,
                     '000',
                     '0000'
                 )
             ))

Open in new window

0
 
flow01Commented:
LTRIM(TO_CHAR(DECODE(TRIM(C_TYPE), 'P', decode(H_TYPE,0,CNT,H_TYPE), 'PI', H_TYPE, CNT),

TYPE= PX  value is always CNT ( type = not 'P'  nor 'PI')
0
 
cutie_smilyAuthor Commented:
Thanks for the quick help.

I have to change both the decode functions. I want something like below

when c_type in ('P','PI') then I have to see if the H_type is 0 if yes then CNT else it should be H_Type.  I tried to put it in a case. Please correct it if something is wrong. Do I have to add an else in the inner case statement after then?

CASE WHEN TRIM(C_TYPE) IN ('H','HX') THEN (CASE WHEN H_type = 0 THEN CNT_ ELSE H_type END) END
0
 
sdstuberCommented:
Case when trim(c_type) in ('P','PI') and h_type = 0 then cnt
else
h_type
end;
0
 
sdstuberCommented:
oops, no semicolon
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now