We help IT Professionals succeed at work.

Using Decode in Oracle

Medium Priority
485 Views
Last Modified: 2013-12-19
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

Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
flow01IT-specialist
CERTIFIED EXPERT

Commented:
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')

Author

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
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
Case when trim(c_type) in ('P','PI') and h_type = 0 then cnt
else
h_type
end;
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
oops, no semicolon
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.