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