Where and Union Clauses

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.
.

Relevant Pages

  • Re: total result of query less than 10K an display as 1 result
    ... You need to create two queries. ... The UNION statement simply slaps the results of the second ... query onto the bottom of the first query. ... > I have a query that shows me all Work providers an their totals but i ...
    (microsoft.public.access.queries)
  • Workaround for Maximum number of user tables under all sides of a UNION statement...
    ... I'm creating a query with 46 UNION ALL statements. ... Maximum number of user tables under all sides of a UNION statement can ... command to conform to the requirement specified in the error message. ... I can't simplify the SQL anymore than it already is. ...
    (comp.databases.sybase)
  • Re: Need net results from (2) queries
    ... In the second part of the UNION statement, does the "A" you included after ... I assume I simply use the query name in place of ... >> corresponding labor hours for the previous month. ... >> Query 1: JobsWithHoursLastMonth ...
    (microsoft.public.access.queries)