using nvl in decode function [message #387387] |
Thu, 19 February 2009 01:45 |
radhavijaym
Messages: 65 Registered: December 2008 Location: singapore
|
Member |
|
|
hi
im using decode fn and nvl in order by clause.
but im not getting correct o/p if i give the query like this.
if i give order by as custmer name,num n status im getting correct o/p.
if i dont select any thing as my query it should print accourding to cust name but its not printing like that so please help me
ORDER BY DECODE(nvl(UPPER('&&order_by'),party.party_name),'CUSTOMER NAME',PARTY.PARTY_NAME,'CUSTOMER NUMBER',CUST_ACC.ACCOUNT_NUMBER,'STATUS',LOOKUP.MEANING)
|
|
|
Re: using nvl in decode function [message #387404 is a reply to message #387387] |
Thu, 19 February 2009 02:11 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
It'd help if you formatted your decode statement.
I just did and got this:
1 ORDER BY DECODE(nvl(UPPER('&&order_by'),party.party_name),
2 'CUSTOMER NAME',
3 PARTY.PARTY_NAME,
4 'CUSTOMER NUMBER',
5 CUST_ACC.ACCOUNT_NUMBER,
6 'STATUS',
7 LOOKUP.MEANING)
Looking at it like that tells me your DECODE is missing a default value.
In future when posting can you please follow the posting guidelines found in the orafaq forum guide: http://www.orafaq.com/forum/t/88153/0/
Other than that - if you're on Oracle 9i or higher I strongly recommend you use the CASE statement for this rather than DECODE, it's a lot easier to use.
EDIT: typo
[Updated on: Thu, 19 February 2009 02:12] Report message to a moderator
|
|
|
|
Re: using nvl in decode function [message #387423 is a reply to message #387405] |
Thu, 19 February 2009 03:45 |
radhavijaym
Messages: 65 Registered: December 2008 Location: singapore
|
Member |
|
|
im formatting my decode statement:
ORDER BY
DECODE(nvl(UPPER('&&order_by'),party.party_name),
'CUSTOMER NAME',PARTY.PARTY_NAME,
'CUSTOMER NUMBER',CUST_ACC.ACCOUNT_NUMBER,
'STATUS',LOOKUP.MEANING))
ITS WORKING CORRECTLY WHEN I SELECT CUSTNAME,NUMBER OR STATUS BUT IF I DONT SELECT ANY OPTION IT MUST PRINT ACCORDING TO CUSTNAME.
THE NVL FUNCTION IS NOT WORKING CORRECTLY please HELP ME IS THERE ANY WRONG IN MY DECODE STATEMENT.
|
|
|
|
Re: using nvl in decode function [message #387432 is a reply to message #387423] |
Thu, 19 February 2009 04:02 |
radhavijaym
Messages: 65 Registered: December 2008 Location: singapore
|
Member |
|
|
I HAD SOLVED IT.
THE CORRECT STATEMENT IS:
ORDER BY
DECODE(UPPER('&&order_by'),'CUSTOMERNAME',PARTY.PARTY_NAME,
'CUSTOMER NUMBER',CUST_ACC.ACCOUNT_NUMBER,
'STATUS',LOOKUP.MEANING, PARTY.PARTY_NAME)
|
|
|
|
|