Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    10

    Unanswered: SELECT CASE when no rows

    Sorry this one is a bit of a headache. I'll start with the example:

    Tables:

    TownCountry

    Record | Town | CountryCode
    -------+--------+-------------
    1 | London | A1
    2 | Cardiff| A2
    3 | Hull | A1
    4 | Luton | A2

    ReFData

    Type | Code | Country
    --------+---------+-------------
    Country | A1 | England
    Country | A2 | Wales

    If my query is:

    select a.Town, b.Country from TownCountry a, RefData b, TownCountry c
    where a.Record=1
    and b.Code=c.CountryCode and c.Record=2

    I get:

    London | Wales

    However, if I change the code for Wales to A3, and keep the query the same, by result returns no rows.

    What I want, in the example where Wales is A3, is for my result to be:

    London | (empty)

    I've tried COALESCE:

    select a.Town, COALESCE(b.Country,'empty') from TownCountry a, RefData b, TownCountry c
    where a.Record=1
    and b.Code=c.CountryCode and c.Record=2

    but this returned no rows

    I also tried select case, right and left joins, but still no rows.

    Here's a simpler example that my good friend just gave me while discussing:

    Towns

    Record | Town
    -------+--------
    1 | London
    2 | Cardiff
    4 | Luton

    select a.Town, b.Town, c.town, d.Town
    from Towns a, Towns b, Towns c, Towns d
    where a.Reocrd=1 and b.Reocrd=2 and c.Reocrd=3 and a.Reocrd=4

    I want to return

    a.Town | b.Town | c.Town | d.Town
    -------+--------+--------+--------
    London | Cardiff| NULL | Luton

    Any help much appreciated.

    Thanks Paul
    Last edited by P4ul; 07-16-11 at 19:52.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't see the table definition for TownCount which is table a in your first query. Neither is there a join predicate for that table.

    But in reading your question, I suspect you may want to look out "left outer join" so that a result will returned if the main table has any matching rows, even if there are no joined rows in other tables that match it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    P4ul. I am not sure if I follow what you want. But your first example is wrong. You say you have
    Code:
    select a.Town
         , b.Country 
    from TownCount  a
       , RefData     b
       , TownCountry c
    where a.Record = 1
      and b.Code   = c.CountryCode 
      and c.Record = 2
    You don't have Table TownCount defined. Is it supposed to be TownCountry?
    Making an assumption it is, you have A.Town that returns London which is fine. But there is no way that B.Country could be Cardiff since B is the RefData table and does not contain Cardiff. If you meant London, England, then I would agree with you.
    Code:
    WITH TOWNCOUNTRY (RECORD, TOWN, COUNTRYCODE)
      AS (
    SELECT 1, 'London' , 'A1' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 2, 'Cardiff', 'A2' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 3, 'Hull'   , 'A1' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 4, 'Luton'  , 'A2' FROM SYSIBM.SYSDUMMY1
         )
       , REFDATA (TYPE, CODE, COUNTRY)
      AS (
    SELECT 'Country', 'A1','England' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'Country', 'A2','Wales'   FROM SYSIBM.SYSDUMMY1
         )
    SELECT A.TOWN, B.COUNTRY
    FROM TOWNCOUNTRY A
       , REFDATA     B
       , TOWNCOUNTRY C
    WHERE A.RECORD = 1
      AND B.CODE   = C.COUNTRYCODE
      AND C.RECORD = 2
    ;
    TOWN    COUNTRY
    ------- -------
    London  England
    The Reason you get 0 rows when you change the CountryCode to A3, which isn't in the table is that part of the Query returns 0 rows and Inner Joins can not match to something that doesn't exist so none of the Joins would return rows either.

    What you probably want is a Left Outer Join. But without knowing exactly what you want, I am not sure I can show you what to do.

    For example, if you just want to return the Town from TownCountry and the Country from RefCode, why are you involving 3 tables?

    Code:
    SELECT A.TOWN, B.COUNTRY
    FROM TOWNCOUNTRY A
       , REFDATA     B
    WHERE A.RECORD = 1
      AND A.COUNTRYCODE = B.CODE
    TOWN    COUNTRY
    ------- -------
    London  England
    This Query doesn't care what the Code is for Wales is (as long as it isn't the same A1 used by England).

    If you want a row returned, even if a Country Code doesn't exist, use a left outer join. Changing London CountryCode to A3 you get:
    Code:
    WITH TOWNCOUNTRY (RECORD, TOWN, COUNTRYCODE)
      AS (
    SELECT 1, 'London' , 'A3' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 2, 'Cardiff', 'A2' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 3, 'Hull'   , 'A1' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 4, 'Luton'  , 'A2' FROM SYSIBM.SYSDUMMY1
         )
       , REFDATA (TYPE, CODE, COUNTRY)
      AS (
    SELECT 'Country', 'A1','England' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'Country', 'A2','Wales'   FROM SYSIBM.SYSDUMMY1
         )
    SELECT A.TOWN, B.COUNTRY
    FROM 
         (SELECT TOWN, COUNTRYCODE
          FROM TOWNCOUNTRY
          WHERE RECORD = 1
          ) AS A
           LEFT OUTER JOIN
           REFDATA     B
             ON A.COUNTRYCODE = B.CODE
    ;
    TOWN    COUNTRY
    ------- -------
    London  -

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •