MS SQL Server MSEQ - Where and Union Clauses

Author

Where and Union Clauses

Fez

2005-08-23, 8:24 pm

I am having a little problem creating this query.

I have a stored procedure that accepts 4 parameters.
One of the parameters tells the query which column to sort on.

Select
Case
When @ReportParam = 'Col1' Then Col1
When @ReportParam = 'Col2' Then Col2
End as ReportOrder,
Col3
From Table

Now I also have to add the zero records.
To do this I am using a union statement to add the zero records.
This approach worked fine when I had 1 column to sort on but now with this
parameter telling me which column to sort on I don't know how to union to get
the zero records.

Select
Case
When @ReportParam = 'Col1' Then Col1
When @ReportParam = 'Col2' Then Col2
End as ReportOrder,
Col3
From Table

Union

Select Case
When @ReportParam = 'Col1' Then Col1
When @ReportParam = 'Col2' Then Col2
End as ReportOrder,
Col3
From Table
where ReportOrderCol not in(Select (Col1, Col2, Col3) from Table)

Is there a way to use a case or If statement to choose which sql statement
to use
when using a union statement.

Let me know if there is any info that i left out.

Thanks in advance.

Hugo Kornelis

2005-08-25, 8:24 pm

On Tue, 23 Aug 2005 13:17:08 -0700, Fez wrote:

>I am having a little problem creating this query.
(snip)
>Thanks in advance.

Hi Fez,

I'm not sure if I understand what you are trying to achieve. A better
way to explain would be to post CREATE TABLE statements (including
constraints and properties), INSERT statements with some sample data and
expected output. Include the code from your last attempt and the results
of running that code as well.

See www.aspfaq.com/5006 for more details.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links