Post reply

Help with another select query please

  • November 9, 2009 at 5:54 am

    #156388

    Hi all,

    Would anyone be able to help with this?

    I want to create a select query based on a table similar to the following.

    I want to return the order number, 1st name, last name and now the crucial bit, the zip code. But I only want one zip code returned. The shipping zip code will only exist if the order has a separate zip code to the billing zip code. So if the shipping zip exists then I want this, but if this is NULL then I want the billing zip.

    Many thanks,

    Sunil

    CREATE TABLE TEST(

    [ORD_NBR] [int] NOT NULL,

    [1ST_NME] [varchar](50) NOT NULL,

    [LST_NME] [varchar](50) NOT NULL,

    [SHP_ZIP] [nchar](10) NULL,

    [BILL_ZIP] [nchar](10) NOT NULL,

    CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED

    (

    [ORD_NBR] ASC

    )

    INSERT INTO TEST VALUES (1, 'Jack', 'Bower', 12345, NULL)

    INSERT INTO TEST VALUES (2, 'Kim', 'Bower', 54321, 12345)

    INSERT INTO TEST VALUES (3, 'Tony', 'Alemda', 22235, NULL)

    INSERT INTO TEST VALUES (4, 'Ryan', 'Chappel', 76889, 44444)

  • November 9, 2009 at 6:05 am

    #1076620

    use

    isnull(SHP_ZIP,BILL_ZIP)


    Clear Sky SQL
    My Blog[/url]

  • November 9, 2009 at 6:32 am

    #1076626

    Hi,

    How can u insert a NULL value in a NOT NULL column {BILL_ZIP} ??

    If the NULL constraint was removed on the last column,is this the solution u require ?

    select [ORD_NBR] ,[1ST_NME],[LST_NME],[SHP_ZIP] from TEST where [SHP_ZIP] is not null

    union

    select [ORD_NBR] ,[1ST_NME],[LST_NME],[BILL_ZIP] from TEST where [SHP_ZIP] is null

  • November 9, 2009 at 12:44 pm

    #1076876

    Seems like the insert statement doesn't reflect your situation (I'd assume [SHP_ZIP] to have NULL values...).

    In that case, (to complete Daves hint):

    SELECT

    [ORD_NBR],

    [1ST_NME],

    [LST_NME] ,

    ISNULL([SHP_ZIP],[BILL_ZIP]) AS zipcode

    FROM TEST

    would return:

    ORD_NBR 1ST_NME LST_NME zipcode

    1 Jack Bower 12345

    2 Kim Bower 12345

    3 Tony Alemda 22235

    4 Ryan Chappel 44444


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 10, 2009 at 2:40 am

    #1077128

    Hi guys,

    Yes you are all right, sorry I got the nullage the wrong way round!

    But the ISNULL function is exactly what I was looking for, many thanks for all your useful replies 🙂

    Sunil

  • November 10, 2009 at 3:01 am

    #1077131

    Okay one more question on the subject. What if, instead if SHP_ZIP having NULL values, it had '0000' in each entry where no shipping zip existed. Then the ISNULL function would not work as it would just return '0000'.

    How would you go about doing this?

    Thanks again.

    Sunil

  • November 10, 2009 at 3:07 am

    #1077135

    Okay worked that one out, I can use CASE.

    Thanks again.

    Sunil

  • November 10, 2009 at 3:11 am

    #1077139

    SELECT

    [ORD_NBR],

    [1ST_NME],

    [LST_NME] ,

    ISNULL(REPLACE([SHP_ZIP],'0000',NULL),[BILL_ZIP]) AS zipcode

    FROM TEST

    --
    Thiago Dantas
    @DantHimself

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply