This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
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


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
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
 
Similar Threads
Mock Exam Q.12.page 428 HFEJB
Two more mock questions
questions in HFE!
java.sql.SQLException: ORA-01008: not all variables bound
Cant get multiple row result in UI