DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: DECODE returning multiple rows

  1. #1
    Join Date
    Jan 2006
    Posts
    12

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  3. #3
    Join Date
    Jan 2006
    Posts
    12
    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?

  4. #4
    Join Date
    Feb 2005
    Posts
    158
    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
  •  


Click Here to Expand Forum to Full Width