#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    3
    Rep Power
    0

    Question Not returning <null> values?


    Hi,

    I'm trying to do a count on the number of combinations of country/state codes that are invalid (in Australia) for driving licences.

    Code:
    select COUNTRY_CODE, STATE_CODE, count(*) from 
    (select COUNTRY_CODE, STATE_CODE from  CUSTOMER_TABLE
      where DRIVING_LICENCE is not null 
      and not (COUNTRY_CODE in ('AUST') AND STATE_CODE in ('VIC', 'NSW', 'SA', 'QLD', 'NT', 'TAS', 'WA', 'ACT')))
      group by COUNTRY_CODE, STATE_CODE
    The output is okay...for example I get these results:

    INT INT
    NZ SI
    NZ INT
    AUST INT
    NZ NSW
    NZ <null>

    However, what I am missing is the combination of "AUST" & <null> for country/state respectively.

    Am I writing the code correctly?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    3
    Rep Power
    0
    Nevermind, I realised that using NOT statements including 'NULLs' even if I don't specify it..


    Originally Posted by thedunnyman
    Hi,

    I'm trying to do a count on the number of combinations of country/state codes that are invalid (in Australia) for driving licences.

    Code:
    select COUNTRY_CODE, STATE_CODE, count(*) from 
    (select COUNTRY_CODE, STATE_CODE from  CUSTOMER_TABLE
      where DRIVING_LICENCE is not null 
      and not (COUNTRY_CODE in ('AUST') AND STATE_CODE in ('VIC', 'NSW', 'SA', 'QLD', 'NT', 'TAS', 'WA', 'ACT')))
      group by COUNTRY_CODE, STATE_CODE
    The output is okay...for example I get these results:

    INT INT
    NZ SI
    NZ INT
    AUST INT
    NZ NSW
    NZ <null>

    However, what I am missing is the combination of "AUST" & <null> for country/state respectively.

    Am I writing the code correctly?

IMN logo majestic logo threadwatch logo seochat tools logo