Home » SQL & PL/SQL » SQL & PL/SQL » Decode statement
Decode statement [message #213294] Wed, 10 January 2007 04:06 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have a condition to be incorporated in a Select statement. Below is the condition:-

if IsNull (CO.F3) then 0 else 
IF ((fcst.DMDGROUP <> 'PL2PK') and (fcst.DMDGROUP <> 'PN2PK')) THEN CO.F3 ELSE (CO.F3/2)


I have written this code but this is not giving correct data as result. Can you let me know as what is wrong with this statement.

DECODE(co.f3,NULL,0,
                  DECODE(fcst.dmdgroup, 'PL2PK', (co.f3/2), 'PN2PK', (co.f3/2), co.f3))


Thanks,
Mona
Re: Decode statement [message #213299 is a reply to message #213294] Wed, 10 January 2007 04:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you try to rewrite a nested IF statement to SQL, try to use CASE. It is far more readable then decode.
Re: Decode statement [message #213306 is a reply to message #213299] Wed, 10 January 2007 04:41 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Frank,
is the below code correct with respect to the condition ?

CASE 
WHEN co.f3 IS NULL THEN 0
WHEN fcst.dmdgroup <> 'PL2PK' AND fcst.dmdgroup <> 'PN2PK' THEN co.f3
ELSE co.f3/2
END	 


I am still not getting the correct value. Please let me know if this is not correct as per the condition below :

if IsNull (CO.F3) then 0 else 
IF ((fcst.DMDGROUP <> 'PL2PK') and (fcst.DMDGROUP <> 'PN2PK')) THEN CO.F3 ELSE (CO.F3/2)


Thanks,
Mona

[Updated on: Wed, 10 January 2007 05:04]

Report message to a moderator

Re: Decode statement [message #213316 is a reply to message #213306] Wed, 10 January 2007 05:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hi, Frank! ./fa/451/0/

I think that your code isn't quite correct, Mona. Perhaps something like this would be better?
CASE
  WHEN co.f4 IS NULL THEN 0
  ELSE
    CASE
      WHEN fcst.dmdgroup = 'PL2PK' THEN co.f3/2
      WHEN fcst.dmdgroup = 'PN2PK' THEN co.f3/2
      ELSE co.f3
    END
END
Re: Decode statement [message #213339 is a reply to message #213316] Wed, 10 January 2007 06:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yo Littlefoot!
Been drinking again? Where did you get f4 from? Or did Mona update her reply after you added your post? Wink

I think Mona's code (as it is now) is correct, apart from possible NULL-values for dmdgroup.

Maybe she can enlighten us and explain a bit more why this code does not return what she wants.

[Updated on: Wed, 10 January 2007 06:33]

Report message to a moderator

Re: Decode statement [message #213355 is a reply to message #213339] Wed, 10 January 2007 07:24 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I found 4 next to 3. At least, I believe I did. ./fa/1600/0/

It was a typo. Sorry.
Previous Topic: bitand(x,y)
Next Topic: How to Change Left outer join in ANSI
Goto Forum:
  


Current Time: Sun Dec 11 04:16:32 CST 2016

Total time taken to generate the page: 0.09447 seconds