Home » RDBMS Server » Server Administration » SQL
| SQL [message #373665] |
Tue, 01 May 2001 03:47  |
ram
Messages: 95
Registered: November 2000
|
Member |
|
|
Hi
I want to replace decode in my query with generic code.
the query is:
DECODE(B.INDICATOR,'Absolute',SHARINGVALUE,'Percentage',(B.PAYMENTAMOUNT-B.COSTAMOUNT)*(sharingvalue/100)),
'Destination',DECODE(B.INDICATOR,'Absolute',(SHARINGVALUE+B.COSTAMOUNT),
'Percentage',((B.PAYMENTAMOUNT-B.COSTAMOUNT)*(sharingvalue/100)+B.COSTAMOUNT))),
'Destination',decode(b.costincurredat,'Origin',DECODE(B.INDICATOR,'Absolute',(-1)*(B.PAYMENTAMOUNT-SHARINGVALUE),
'Percentage',(-1)*((B.PAYMENTAMOUNT-B.COSTAMOUNT)*(100-sharingvalue)/100)+B.COSTAMOUNT),
'Destination',DECODE(B.INDICATOR,'Absolute',SHARINGVALUE,'Percentage',
(B.PAYMENTAMOUNT-B.COSTAMOUNT)*(sharingvalue/100)))) AMOUNT,SR.EXCHANGERATE
FROM FS_FR_HOUSEDOCHDR A, FS_FR_HOUSEDOCCHARGES B,FS_FR_FRTINVOICEMASTER I,FS_AC_SREXCHGRATE SR
WHERE A.HOUSEDOCID=B.HOUSEDOCID AND SR.housedocid(+)=a.housedocid and SR.terminalid(+)=A.DESTTERMINAL
AND I.HOUSEDOCID=A.HOUSEDOCID AND I.TERMINALID=
DECODE(B.PAYAT,'Origin',A.ORIGINTERMINAL,'Destination',A.DESTTERMINAL) AND B.CHARGEID=1 AND
A.DESTTERMINAL='SNTFRA' AND A.ORIGINTERMINAL IN('SNTSIN','SNTHKG') AND TRUNC(SR.SRDATE(+))
BETWEEN TO_DATE('01/04/2001','DD/MM/YYYY') AND TO_DATE('30/04/2001','DD/MM/YYYY')
AND TRUNC(A.HOUSEDOCDATE) BETWEEN TO_DATE('01/04/2001','DD/MM/YYYY') AND TO_DATE('30/04/2001','DD/MM/YYYY');
Here I want to pass a.originterminal if b.payat='Origin' and a.destterminal if b.payat='Destination' for a column I.terminalid.
Please help me as we want to adopt generic code to support all databases.
Thanks,
Ram
|
|
|
|
| Re: SQL [message #373668 is a reply to message #373665] |
Tue, 01 May 2001 04:54  |
Ramanarsaiah Katam
Messages: 4
Registered: May 2001
|
Junior Member |
|
|
My query is this, and I want solution to replace the decode in the where clause.
SELECT A.ORIGINTERMINAL, A.DESTTERMINAL,A.HOUSEDOCID,A.MASTERDOCID,B.CURRENCYID,b.payat,
B.PAYMENTAMOUNT revenue,b.costincurredat,B.COSTAMOUNT cost,(B.PAYMENTAMOUNT-B.COSTAMOUNT) MARGIN,
SHARINGVALUE||B.INDICATOR SHARING,b.indicator,DECODE(B.PAYAT,'Origin',decode(b.costincurredat,'Origin',
DECODE(B.INDICATOR,'Absolute',SHARINGVALUE,'Percentage',(B.PAYMENTAMOUNT-B.COSTAMOUNT)*(sharingvalue/100)),
'Destination',DECODE(B.INDICATOR,'Absolute',(SHARINGVALUE+B.COSTAMOUNT),
'Percentage',((B.PAYMENTAMOUNT-B.COSTAMOUNT)*(sharingvalue/100)+B.COSTAMOUNT))),
'Destination',decode(b.costincurredat,'Origin',DECODE(B.INDICATOR,'Absolute',(-1)*(B.PAYMENTAMOUNT-SHARINGVALUE),
'Percentage',(-1)*((B.PAYMENTAMOUNT-B.COSTAMOUNT)*(100-sharingvalue)/100)+B.COSTAMOUNT),
'Destination',DECODE(B.INDICATOR,'Absolute',SHARINGVALUE,'Percentage',
(B.PAYMENTAMOUNT-B.COSTAMOUNT)*(sharingvalue/100)))) AMOUNT,SR.EXCHANGERATE
FROM FS_FR_HOUSEDOCHDR A, FS_FR_HOUSEDOCCHARGES B,FS_FR_FRTINVOICEMASTER I,FS_AC_SREXCHGRATE SR
WHERE A.HOUSEDOCID=B.HOUSEDOCID AND SR.housedocid(+)=a.housedocid and SR.terminalid(+)=A.DESTTERMINAL
AND I.HOUSEDOCID=A.HOUSEDOCID AND I.TERMINALID=
DECODE(B.PAYAT,'Origin',A.ORIGINTERMINAL,'Destination',A.DESTTERMINAL) AND B.CHARGEID=1 AND
A.DESTTERMINAL='SNTFRA' AND A.ORIGINTERMINAL IN('SNTSIN','SNTHKG') AND TRUNC(SR.SRDATE(+))
BETWEEN TO_DATE('01/04/2001','DD/MM/YYYY') AND TO_DATE('30/04/2001','DD/MM/YYYY')
AND TRUNC(A.HOUSEDOCDATE) BETWEEN TO_DATE('01/04/2001','DD/MM/YYYY') AND TO_DATE('30/04/2001','DD/MM/YYYY')
Thanks
Ram
|
|
|
|
| Re: SQL [message #373724 is a reply to message #373668] |
Wed, 02 May 2001 16:41 |
Sundar Venkatasubramaniam
Messages: 26
Registered: May 2001
|
Junior Member |
|
|
assuming A.ORIGINTERMINAL and A.DESTTERMINAL are not null
the following part of the query
I.TERMINALID=
DECODE(B.PAYAT,'Origin',A.ORIGINTERMINAL,'Destination',A.DESTTERMINAL)
can be replced by
(( I.TERMINALID=A.ORIGINTERMINAL and B.PAYAT='Origin') or
( I.TERMINALID=,A.DESTTERMINAL and B.PAYAT=''Destination'))
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 09:21:44 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
