Post reply

Single query to return conditional calculated field from two tables?

  • April 14, 2008 at 5:20 am

    #189099

    There are two tables rable1 and table 2.

    I am showing some calculated fields from table1 in a report.

    table2 also has same columns.

    I want to filter my report on a parameter

    if @parameter=true then show result with calulated field in table1 equaling the same calulated field in table2 i.e. result for only matching data.

    and if @parameter=false then show all result.

    I can use a table type variable and fill it according to the value of the parameter and then select from this table to return as the report data.

    But can I manage it in a single query?

  • April 16, 2008 at 6:19 am

    #803026

    I assume you are using SSRS.....

    You create a parameter in SSRS which is boolean, let's say this is called @Param

    In your SQL Query you use the following

    SELECT

    T1.Field1

    ,T1.Field2

    ,T2.Field11

    ,T2.Field12

    ,T1.Field1 * T1.Field2 AS Field3

    FROM Table1 as T1

    LEFT OUTER JOIN Table2 AS T2

    on (case when @param=1 then T1.Field1 * T1.Field2 else rd.pk_reportingdate end) = T2.Field11

    Basically the case statement is saying that when the parameter is set to true, join the two tables using field1 * field2 equal to field11 in table 2. It also has an else that is pretty meaningless in that it will never work, however, it must be there otherwise you will get errors, it should also be using matching data types.

    Good luck,

    Nigel West
    UK

Viewing 2 posts - 1 through 1 (of 1 total)

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