return a row (with constants) even if query comes up blank

  • hi all,

    so i have a problem i cannot solve. i run a query that contains a "constant" (hardcoded) variable, and if the other variables from the db table come up blank, i still want a row returned (with null / blank for all db columns, but the hardcoded variables to show)

    for example:

    select name, address, '12345' as zip from mytable

    where name='my_name'

    if no entry exists in mytable where name='my_name', i get 0 rows.

    but i want at least 1 row, showing (blank) for name, (blank) for address, and 12345 for zip.

    i tried something like:

    select isnull(name, ''), isnull(address, ''), '12345' as zip from mytable

    where name='my_name'

    but still 0 rows returned.

    can someone please help?

  • untested, but it should work:

    IF EXISTS(SELECT 1 FROM mytable WHERE name ='myname')

    SELECT name, address, '12345' as zip from mytable WHERE name ='myname'

    ELSE

    SELECT 'name' AS name, 'address' AS address, '12345' as zip



    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]

  • edit: identical post.



    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]

  • edit: identical post.



    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]

  • You could also put a UNION after your main query, with nulls for the database columns and the hard-coded constants, EG

    select name, address, '12345' as zip from mytable

    where name='my_name'

    UNION ALL

    select null, null,'12345'

    This will always return a row with nulls and your hard-coded constants, even if your first query returned results. This may be something the app receiving your data has to cope with.

  • You can build up a table (table variable) with your hard-coded values, parameters or whatever:

    DROP TABLE MyTable

    CREATE TABLE MyTable (

    PersonIdint,

    FullNamevarchar(30),

    Address1varchar(50)

    )

    GO

    INSERT MyTable (

    PersonId,

    FullName,

    Address1

    ) VALUES (

    123,

    'John Smith',

    '3325 Nowhere Ln'

    )

    -- Hard-coded values, passed-in parameters, whatever.

    -- Edit the value of @Parm2 to see it work.

    DECLARE

    @Parm1varchar(10),

    @Parm2int

    SELECT

    @Parm1 = '12345',

    @Parm2 = 999 -- 123

    -- However you got them, stuff them into a table

    DECLARE @Values TABLE (

    Parm1varchar(10),

    Parm2int

    )

    INSERT @Values (

    Parm1,

    Parm2

    ) VALUES (

    @Parm1,

    @Parm2

    )

    SELECT

    COALESCE(T.FullName, '') AS FullName,

    COALESCE(T.Address1, '') AS Address1,

    V.Parm1,

    V.Parm2

    FROM

    @Values V

    LEFT JOIN MyTable T

    ON 1 = 1

    AND T.PersonId = @Parm2

    [whine]Except for the fact that the code tags really uglify my code, [/whine] this seems to meet the OP's requirements re: no rows returned without introducing any extra rows when data *is* found.


    Regards,

    John Hopkins

  • Adding to the post by christine.lawrie you can construct the UNION to only add the row if necessary:

    SELECT [name], [address], '12345' AS zip

    FROM mytable

    WHERE [NAME] = 'my_name'

    UNION ALL

    SELECT TOP 1 NULL, NULL,'12345'

    FROM mytable

    WHERE (

    SELECT COUNT(*)

    FROM mytable

    WHERE [name]='my_name'

    ) = 0

  • thanks a ton everyone! here is what i ended up with:

    select top 1 *

    from

    (

    select name, address, zip from mytable where name='my_name'

    union all

    select '', '', '12345'

    ) t

    order by name desc

    very close to some of your replies - props to Andreas at sqlteam.com

    i was so close, so many times - SQL WILL be my friend one day!

    case closed!

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

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