I am having QUERY/ DATASET problem with SQL Reports.

Hi every1,

I really need your help.

I have a web form which is having Drop Down box in which I have 2 selection 'P' and 'C'. Depending upon the selection I have to call the Dataset( If I can make it in code behind) or I can do it in one Complex query if you guys will help me.

Query should be like this.

select a.* , case when selection='P' then select p.column1 from ptable p

case when selection='C' then select c.column1 from ctable c

else a.column1

from atable a

where

acolumn2 = "USERINPUT"

but now the problem is in FROM and Where Clause.

when selection is 'P' then I have to include ptable in FROM Clause and in where clause I have to include two condition like (p.column1 = a.column1 and p.column2="USERINPUT1" or

if selection is 'C' then I have to include ctable in FROM clause and in where clause I have to include two different condition like (c.column1 = a.column1 and c.column2="USERINPUT")

So at once the Query will be any of one like below:

(1) some times this:

select a.* , case when selection='P' then select p.column1 from ptable p

case when selection='C' then select c.column1 from ctable c

else a.column1

from atable a, ptable p

where

a.column2 = "USERINPUT" and

p.column1 = a.column1 and p.column2="USERINPUT1"

-

(2) some times this:

select a.* , case when selection='P' then select p.column1 from ptable p

case when selection='C' then select c.column1 from ctable c

else a.column1

from atable a, ctable c

where

a.column2 = "USERINPUT" and

c.column1 = a.column1 and c.column2="USERINPUT"

Please let me know if you have any kind of confusion so I can clear this but if you got everything from my description please do help me to solve this. I am really struggling for this.

One way I was thinking to generate 3 dataset one(query or dataset) for 'P' , one for 'C' and one for else so I can get the input (selection) from the form and then decide in codebehind which dataset I have to make call so no need to write complex query. But this is my thought and I dont know how to make a call and everything either so please help me in this as well, if you like this solution .

Or try to solve the above query please.

Thanks

Roy

The best way to do this is to construct the query dynamically as a string.

And the best way to do THAT -- IMHO -- is to have your query say only:

Code Snippet

=Code.GetSQL()

... now you can write a simple little VB function that evaluates your parameters and builds up the string, returning that string. You will find it a lot more comfortable to do it this way than doing it in the query window. If you have never written a custom function before, don't worry, you don't need to build a DLL for something like this -- just use the Code tab in the Report Properties dialog. This is the kind of code that IMHO really *deserves* to be embedded in a report, since it is specific to that report and that report's parameters.

If you have the ability to write stored procedures or table-valued functions in the database, that is another option. You can pass your parameters to the procedure or function, and have that code evaluate the parameters and provide the result. It really depends on two things: what your permissions are (for writing to the database) and what your relative comfort level is (for writing VB or writing T-SQL).

HTH

>L<