• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DECODE function in Oracle gives duplicate data

 
Pawan Choure
Greenhorn
Posts: 27
Eclipse IDE Oracle Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
Santhosh ayiappan
Ranch Hand
Posts: 80
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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" ?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic