wood burning stoves*
The moose likes Oracle/OAS and the fly likes DECODE function in Oracle gives duplicate data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "DECODE function in Oracle gives duplicate data" Watch "DECODE function in Oracle gives duplicate data" New topic
Author

DECODE function in Oracle gives duplicate data

Pawan Choure
Greenhorn

Joined: May 11, 2008
Posts: 27

Hi,
I want to retrieve distinct result using decode function and given below are the queries for that.

SELECT DISTINCT O.*, DECODE(TA,'AP',AMT,'AC1',AMT,'AC2',AMT,'AC3',AMT,'AC4',AMT,NULL) AS CA,
DECODE(TA,'AD1',AMT,NULL) AS DA FROM HT O, HIS H, V_CV
WHERE O.HID=H.HID AND H.HTYP='TRN' AND O.HID = V.HID AND V.CID = '5' ORDER BY CA ASC;

which gives me

HID TA PT AMT CA DA
2 A V 4.1 4.1 null
3 C V 14.02 14.02 null
1 D V 5.1 null 5.1
4 D V 6.21 null 6.21

which is correct but i want to do sorting on two different column which do not exit in the database and they are dynamic.So i Modified the Query but i am getting duplicate results

SELECT DISTINCT O.*,
DECODE(o.TA,cr.TA,o.AMT,NULL) AS CA,
DECODE(o.TA,de.TA,o.AMT,NULL) AS DA
FROM HT O,
HIS H,
V_CV,
(select TA from RTD where Ttype= 'C') cr,
(select TA from RTD where Ttype= 'D') de
WHERE
O.HID=H.HID
AND H.HTYP='TRN'
AND O.HID = V.HID
AND V.CID = '5'
ORDER BY CA ASC

which gives me

HID TA PT AMT CA DA
2 A V 4.1 4.1 null
3 C V 14.02 14.02 null
3 C V 14.02 null null
1 D V 5.1 null 5.1
1 D V 5.1 null null
4 D V 6.21 null 6.21
2 A V 4.1 null null

which is obviously giving HID 2,3,1 as duplicate value since DECODE select a value and compares with all item where i want only single comparison.

Any help regarding this would be appreciated.


Pawan Choure
Santhosh ayiappan
Ranch Hand

Joined: Jan 30, 2007
Posts: 80

You have added a table "RTD" to get the dynamic values, but I dont see any where clause for the alias cr and de ? You want to retrieve all the values from the table RTD for the "Ttype" ?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: DECODE function in Oracle gives duplicate data