Parameters in select clause

Hi all,
My query needs to work with different back ends say oracle, sql server,db2
etc. so i need to make use of only the ansi sql. I need something like
="select col1,col2,"&_
"(case when parameters!gender.value='m' then 'some calculation'"&_
"when parameters!gender.value='f' then 'some other calculation'"&_
"end) as calc_name,"&
"from table1" & parameters!where_clause.value
but i could not make the above code work. Please let me the correct syntax
to get the above kind of query work.
Thanks,
RSUserWhat you should do is set that expression to a textbox first so you can see
what you are getting. Otherwise any little thing gets the same result, it
doesn't work but you don't know why. I notice below you have an comma right
before from.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RSUser" <RSUser@.discussions.microsoft.com> wrote in message
news:C0AA0793-2606-4D38-AC7A-C37162EA4EDE@.microsoft.com...
> Hi all,
> My query needs to work with different back ends say oracle, sql server,db2
> etc. so i need to make use of only the ansi sql. I need something like
> ="select col1,col2,"&_
> "(case when parameters!gender.value='m' then 'some calculation'"&_
> "when parameters!gender.value='f' then 'some other calculation'"&_
> "end) as calc_name,"&
> "from table1" & parameters!where_clause.value
> but i could not make the above code work. Please let me the correct syntax
> to get the above kind of query work.
> Thanks,
> RSUser
>|||Hi, RSUser
you can use an immediate if :
="select col1,col2," & IIF(Parameters!Gender.Value ='M', "some
calculation","some other calculation") & " as calc_name from table1" &
Parameters!where_clause.value"
Just copy and paste it.
Please, vote.
ProJester, MSN Programmer.
"RSUser" wrote:
> Hi all,
> My query needs to work with different back ends say oracle, sql server,db2
> etc. so i need to make use of only the ansi sql. I need something like
> ="select col1,col2,"&_
> "(case when parameters!gender.value='m' then 'some calculation'"&_
> "when parameters!gender.value='f' then 'some other calculation'"&_
> "end) as calc_name,"&
> "from table1" & parameters!where_clause.value
> but i could not make the above code work. Please let me the correct syntax
> to get the above kind of query work.
> Thanks,
> RSUser
>|||Use the Immediate If as Projester indicates. The reason is that the dynamic
query is parsed and fixed up as a VBScript first. Then the results are sent
to SQL ... The SQL Case you are using should be replaced by IIF so that the
VBScript can generate the correct SQL.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"RSUser" <RSUser@.discussions.microsoft.com> wrote in message
news:C0AA0793-2606-4D38-AC7A-C37162EA4EDE@.microsoft.com...
> Hi all,
> My query needs to work with different back ends say oracle, sql server,db2
> etc. so i need to make use of only the ansi sql. I need something like
> ="select col1,col2,"&_
> "(case when parameters!gender.value='m' then 'some calculation'"&_
> "when parameters!gender.value='f' then 'some other calculation'"&_
> "end) as calc_name,"&
> "from table1" & parameters!where_clause.value
> but i could not make the above code work. Please let me the correct syntax
> to get the above kind of query work.
> Thanks,
> RSUser
>|||Hi all,
Thanks for your replies. My case statement is so complex as i have to check
for 10 different values and do different calculations for each case unlike
the example i provided where there was just two, male and female. Thus i need
further help as to decide if it is good to use iif or to use external code
like vb.net or c# and solve the problem.
Thanks,
RSUser
"Wayne Snyder" wrote:
> Use the Immediate If as Projester indicates. The reason is that the dynamic
> query is parsed and fixed up as a VBScript first. Then the results are sent
> to SQL ... The SQL Case you are using should be replaced by IIF so that the
> VBScript can generate the correct SQL.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "RSUser" <RSUser@.discussions.microsoft.com> wrote in message
> news:C0AA0793-2606-4D38-AC7A-C37162EA4EDE@.microsoft.com...
> > Hi all,
> >
> > My query needs to work with different back ends say oracle, sql server,db2
> > etc. so i need to make use of only the ansi sql. I need something like
> >
> > ="select col1,col2,"&_
> > "(case when parameters!gender.value='m' then 'some calculation'"&_
> > "when parameters!gender.value='f' then 'some other calculation'"&_
> > "end) as calc_name,"&
> > "from table1" & parameters!where_clause.value
> >
> > but i could not make the above code work. Please let me the correct syntax
> > to get the above kind of query work.
> >
> > Thanks,
> > RSUser
> >
>
>