Post reply

select statement

  • October 18, 2004 at 4:35 pm

    #163594

    I'm trying to select from a table containing a type column, a lastname column and a org name column. I want to select AS line1 and AS line2 two based on the state of the fields in question. For instance if type = x then line1 = orgname if not empty else line1 = lastname.  This works fine in access using iif but I can't get it to fly in t-sql. I keep getting an error that isemtpy is not a function.

    Thanks in advance

  • October 18, 2004 at 5:14 pm

    #527100

    The IsEmpty Function in SQL Server is for Analysis Server only!

    Show us your code and we can go from there to assist you.

    Kindest Regards,

  • October 18, 2004 at 6:23 pm

    #527104

    Jeff, I'm not entirely sure what you mean.  Although, if the MS Access iif statement is what you need, take a look at SELECT CASE in SQL's books on line (BOL).

    Ryan

  • October 18, 2004 at 8:47 pm

    #527110

    And if the 'empty' columns are actually 'Null' check out 'is null' or function IsNull in BOL.

     


    The systems fine with no users loggged in. Can we keep it that way ?br>

  • October 19, 2004 at 1:54 am

    #527127

    Hi Jeff,

    if the 'empty' in fact means NULL and only NULL, then you could use COALESCE or ISNULL function. If empty string is possible - which probably is, then CASE should solve it (e.g. SELECT CASE WHEN ISNULL(orgname,'') = '' THEN lastname ELSE orgname END AS Name FROM Tablename ....). ISNULL function assures that both NULLs and empty strings are treated the same way, i.e. as 'no entry, select from the other column instead'. BTW, Empty string ('') is two single quotes, not one double quote.

    HTH, Vladan

  • October 19, 2004 at 7:56 am

    #527183

    Thanks to all replies, the select case is what I needed.

Viewing 6 posts - 1 through 5 (of 5 total)

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