-
DECODE returning multiple rows
Hi All,
I have a SQL statement which is not returning information in the way i expect it. Here is the SQL:
Code:
SELECT rts.RtS_RepairID,
DECODE(rts.rts_channel1,-1,DECODE(COR.Ch_ChannelSort,1,COR.CH_Direction,NULL),NULL) AS Channel1_Direction,
DECODE(rts.rts_channel2,-1,DECODE(COR.Ch_ChannelSort,2,COR.CH_Direction,NULL),NULL) as Channel2_Direction,
DECODE(rts.rts_channel1,-1,DECODE (COR.CH_ChannelSort, 1,COR.CH_ChannelNo,NULL),NULL) AS Channel1,
DECODE(rts.rts_channel1,-1,DECODE (COR.CH_ChannelSort, 2,COR.CH_ChannelNo,NULL),NULL) AS Channel2,
DECODE(rts.rts_channel1,-1,DECODE (COR.CH_ChannelSort, 1,COR.CH_Description,NULL),NULL) AS Description1,
DECODE(rts.rts_channel1,-1,DECODE (COR.CH_ChannelSort, 2,COR.CH_Description,NULL),NULL) AS Description2
FROM RepairsToSites rts INNER JOIN ChannelsOnRoutes COR ON rts.rts_SiteRef = COR.CH_SOR_Siteref
The results, however, look something like this:
Repair_ID Ch1_Direction Ch2_Direction Channel1 Channel 2
584 WB 223
584 EB 224
This should be one row, but is returning two. I have spent an age trying to figure out why, including trawling through various forums, without much luck. Can anyone advise?
Thanks
Dan
-
Try this:
Code:
SELECT rts.RtS_RepairID,
max(DECODE(rts.rts_channel1,-1,DECODE (COR.Ch_ChannelSort,1,COR.CH_Direction,NULL),NULL) ) AS Channel1_Direction,
max(DECODE(rts.rts_channel2,-1,DECODE(COR.Ch_ChannelSort,2,COR.CH_Direction,NULL),NULL)) as Channel2_Direction,
max(DECODE(rts.rts_channel1,-1,DECODE (COR.CH_ChannelSort, 1,COR.CH_ChannelNo,NULL),NULL)) AS Channel1,
max(DECODE(rts.rts_channel1,-1,DECODE (COR.CH_ChannelSort, 2,COR.CH_ChannelNo,NULL),NULL)) AS Channel2,
max(DECODE(rts.rts_channel1,-1,DECODE (COR.CH_ChannelSort, 1,COR.CH_Description,NULL),NULL)) AS Description1,
max(DECODE(rts.rts_channel1,-1,DECODE (COR.CH_ChannelSort, 2,COR.CH_Description,NULL),NULL)) AS Description2
FROM RepairsToSites rts
INNER JOIN ChannelsOnRoutes COR
ON rts.rts_SiteRef = COR.CH_SOR_Siteref
group by rts.RtS_RepairID
Tamil
-
Hi Tamil,
Thanks for that, did try it yesterday and it did work, but for the sake of simplicity in this thread i removed a number of other fields and tables that are used in this query, and the concept of providing max(field) for every field just doesn't look right. Are there any drawbacks to using the aggregate function on fields which do not need to be aggregated?
-
If you want one row per repair id, and your source data has multiple rows per repair id, then you need the group by on repair id.
All your other columns need an aggregate function to tell it which of the multiple rows it needs to return.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|