locked
SQL help needed for parameters and a non-existent column RRS feed

  • Question


  • Using shared dataset concept in SSRS i am running the SQL below for various servers.(CH_PRD, LA_PRD, FL_PRD). When i say servers i mean connections. So all these Connections have same tables, views, objects.. etc with slight variations. These are the databases of our different entities.

    CH_PRD and FL_PRD has "NET_AMT" and "TAXABLE_AMT" columns but LA_PRD doesn't have it. So i get "SF.TAXABLE_AMT" is an invalid identifier(makes sense because those columns don't exist in LA_PRD) when i run this for LA_PRD. I want those columns to display a Zero when i run it for LA_PRD. SO is there a way to solve this please help me guys. Thanks in advance.


    SELECT Account, CASE WHEN @Server= 'LA_PRD' THEN NULL ELSE SF.NET_AMT END AS NET_AMT, CASE WHEN @Server= 'LA_PRD' THEN NULL ELSE SF.TAXABLE_AMT END AS TAXABLE_AMT   FROM CIRC.VW_FINANCIAL_DRW SF

    where trans_date between @startdate and @enddate

    and pub in (@pub)


    Note: I am not allowed to change the views.


    svk


    • Edited by czarvk Tuesday, March 19, 2013 1:24 AM
    Tuesday, March 19, 2013 1:22 AM

Answers

  • Easy way is to create New View based on current one and implement logic to return 0 for columns which don't exist and return Values AS IS if they exisit. So, call new view in your application logic instead of calling old view.
    • Proposed as answer by Fanny Liu Wednesday, March 27, 2013 3:35 AM
    • Marked as answer by Mike Yin Thursday, March 28, 2013 4:51 PM
    Tuesday, March 19, 2013 2:58 AM