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.