SQL Server Performance

Expressions

Discussion in 'SQL Server Reporting Services' started by Reddy, Mar 15, 2006.

  1. Reddy New Member

    When i add the following code to my text box as an expression, it doesnt work, why?

    SELECT
    CASE WHEN address2 IS NULL
    THEN address1
    ELSE address2
    END
    FROM tablename


    Thanks!
    "He laughs best who laughs last"

  2. Tahsin New Member

    What is the error message you are getting and what are the data types for address1 and address2? For instance if address1 is of type numeric, then you may need to convert the column name.

    Try:
    SELECT
    CASE WHEN address2 IS NULL
    THEN CAST(address1 AS varchar)
    ELSE address2
    END
    FROM tablename
  3. Reddy New Member

    i have both fielsd as varchar but its not working.

    Thanks!
    "He laughs best who laughs last"

  4. Reddy New Member

    I also tried adding this code in a SP and created new dataset for my report but when i run report it displays only 1st row of resultset

    Thanks!
    "He laughs best who laughs last"

  5. Adriaan New Member

    You probably want to show Address2 in case Address1 is blank, right?

    Your expression is saying ...
    show Address1 in case Address2 is blank,
    and show Address2 if Address2 is not blank.

    SELECT
    CASE WHEN address1 IS NULL
    THEN address2
    ELSE address1
    END
    FROM tablename

    Also, you may find that Address1 or Address2 perhaps contains a zero-length string, which is also a blank, but not the same as a NULL value. You might want to try the ISNULL() function to cover both NULL and '' ...

    SELECT CASE WHEN ISNULL(address1, '') <> '' THEN address2 ELSE address1 END
    FROM tablename
  6. Reddy New Member

    I am ok with my select statement, i got the result i need but when when i add this select to my expression in my report its not working. not sure if i am adding it in right way to my expression in report.

    Thanks!
    "He laughs best who laughs last"

  7. mkmathi New Member

    HI,
    I think you cant run queries in Textbox expressions .
    Please use iif function to get the result.

    IIf(Cond,field!Address1,field!addrtess2.value)

    Mathi

    Thanks & Regards
    Mathivanan K
    Great work may have to pass through these stages - ridicule, opposition, and then acceptance. Each man who thinks ahead of his time will probably be greatly misunderstood.- vivekananda

Share This Page