Home » SQL & PL/SQL » SQL & PL/SQL » decode script (form builder 6i)
icon5.gif  decode script [message #326922] Fri, 13 June 2008 02:11 Go to next message
fadhzone
Messages: 61
Registered: April 2008
Member
hi all,

how to write a script that can select data in a field but if the data is null, then it will select the data in other field.

E.G : i want to select invoice no from field invoice_no but if the invoice no is null, then it will select PO No from PO_NO field.

i tried using decode statement but didn't work.

kindly help me...

thanks.

[Updated on: Fri, 13 June 2008 02:16]

Report message to a moderator

Re: decode script [message #326924 is a reply to message #326922] Fri, 13 June 2008 02:15 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
i tried using decode statement but didn't work.

Show us what you tried.
Re: decode script [message #326925 is a reply to message #326924] Fri, 13 June 2008 02:19 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
SELECT SLIP_NO,DECODE(INVOICE_NO,0,PO_NO) AS INV_NO
FROM GOODS_RECEIVED_HDR
WHERE SLIP_NO IN (8,48,49,50,51,61,62);
Re: decode script [message #326926 is a reply to message #326922] Fri, 13 June 2008 02:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think the NVL function might be what you're looking for.
Re: decode script [message #326929 is a reply to message #326926] Fri, 13 June 2008 02:24 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
SELECT SLIP_NO,DECODE(INVOICE_NO,0,PO_NO) AS INV_NO
FROM GOODS_RECEIVED_HDR
WHERE SLIP_NO IN (8,48,49,50,51,61,62);

You said that you are wanting to test for NULL, why then, are you testing for 0 (Zero) the are two very different things.
DECODE(YourCol, NULL, OtherCol, YourCol)

@JRowbottom
I think it would need to be the NVL2 function, (or using the DECODE function correctly)
NVL2(YourCol,YourCol, OtherCol)
Re: decode script [message #326932 is a reply to message #326929] Fri, 13 June 2008 02:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I really do think that
NVL(Invoice_No,Po_No)
meets the requirement
Quote:
select invoice no from field invoice_no but if the invoice no is null, then it will select PO No from PO_NO field
quite nicely.

Granted,
NVL2(Invoice_No,Invoice_No,Po_No)
does exactly the same thing, and has the added advantage of making people ask you what NVL2 is.
Re: decode script [message #326935 is a reply to message #326922] Fri, 13 June 2008 02:31 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
great it work already...

thanks for you kind help!!

Embarassed
Re: decode script [message #326936 is a reply to message #326932] Fri, 13 June 2008 02:38 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
You are absolutely correct (as usual). Caffiene obviously wasn't strong enough today Sad Time for espresso
Re: decode script [message #326937 is a reply to message #326936] Fri, 13 June 2008 02:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good plan. It's too early in the morning for thinking without chemical assistance.
Re: decode script [message #326940 is a reply to message #326937] Fri, 13 June 2008 02:46 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
./fa/1598/0/
Previous Topic: Odd Select Query
Next Topic: an interesting sql query with difficult o/p
Goto Forum:
  


Current Time: Fri Dec 09 13:26:55 CST 2016

Total time taken to generate the page: 0.06069 seconds