Efficient Way To Use Case Statement in where clause

Efficient Way To Use Case Statement in where clause

Post by Derek Har » Fri, 15 Feb 2002 10:46:40



In the following sniplet I use a case statement to alter the where clause,
but it seems that I am faking out the system.  Is there a more efficient way
to do this?


And tblCommissionWKsht.AgentID <> 1)

Derek

 
 
 

Efficient Way To Use Case Statement in where clause

Post by msnews.microsoft.co » Fri, 15 Feb 2002 12:48:27


Derek,

You have not used 'Case' here.  However, you can only use case in the
'select' part of an SQL statement.  This where clause looks fine to me.
Watch how you word your questions or you wont get any replies.

David


> In the following sniplet I use a case statement to alter the where clause,
> but it seems that I am faking out the system.  Is there a more efficient
way
> to do this?


> And tblCommissionWKsht.AgentID <> 1)

> Derek


 
 
 

Efficient Way To Use Case Statement in where clause

Post by Greg Obleshchu » Fri, 15 Feb 2002 14:03:00


Hi David,
    You can use a case statement in the order by section as well

--
I hope this helps
regards
Greg O MCSD
Document any SQL server database
AGS SQL Scribe http://www.ag-software.com/ags/ags_scribe_index.asp


> Derek,

> You have not used 'Case' here.  However, you can only use case in the
> 'select' part of an SQL statement.  This where clause looks fine to me.
> Watch how you word your questions or you wont get any replies.

> David



> > In the following sniplet I use a case statement to alter the where
clause,
> > but it seems that I am faking out the system.  Is there a more efficient
> way
> > to do this?


0
> > And tblCommissionWKsht.AgentID <> 1)

> > Derek

 
 
 

Efficient Way To Use Case Statement in where clause

Post by BP Margoli » Fri, 15 Feb 2002 14:32:52


Derek,

You might well get a superior query plan if you can break the query into two
... something like:


  select ...
  from ...
  where tblCommissionWKsht.AgentID <> 1
else
  select ...
  from ...
  where tblCommissionWksht.AgentID = 1

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> In the following sniplet I use a case statement to alter the where clause,
> but it seems that I am faking out the system.  Is there a more efficient
way
> to do this?


> And tblCommissionWKsht.AgentID <> 1)

> Derek

 
 
 

Efficient Way To Use Case Statement in where clause

Post by Umachandar Jayachandra » Fri, 15 Feb 2002 17:34:25


    Try this trick in SQL2000:

create function cwa (

)
returns table
as
return (
        select *
          from (

= 1
             union all

= 0
             ) as a
)
go

select * from cwa( 1 )

    This is very efficient because of the table-valued UDF. SQL Server will
eliminate one entire query in the UNION ALL & basically this is like
short-circuiting the SELECT statement based on some expression. This is
simply the best way to implement these type of searches in SQL2000. The key
is the independent WHERE clauses with the variable values + UNION ALL. So
put as many UNION ALL statements as you want & this is like executing one
single dynamically generated query.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

Efficient Way To Use Case Statement in where clause

Post by Paul Thornet » Sat, 16 Feb 2002 10:13:34


Marvellous! Thanks very much.

--
Paul Thornett


>     Try this trick in SQL2000:

> create function cwa (

> )
> returns table
> as
> return (
>         select *
>           from (

> = 1
>              union all

> = 0
>              ) as a
> )
> go

> select * from cwa( 1 )

 
 
 

1. using case statement with an order by clause

I am using a case statement with an order by clause as
follows:


AS
  select DoctorID, Initials, LastName, FullName FROM  
stblDoctor
order by

    when 0 then  LastName
    when 1 then Initials
    when 2 then FullName
   end

This works fine, but if I add a second index to one of
these: i.e.  '...when 2 then FullName, Initials ...'
then I get an error.  Is there a way to do this?
Thanks for any help
Suzanne Haig

2. DTS Function GetExecutionErrorInfo from Package

3. Using CASE statement in WHERE Clause - MVP Help requested

4. JDBC-driver for Oracle 7.1.6?

5. Where can I find a different driver?

6. Using CASE statement in ORDER BY clause

7. Stored Procedure: extract OUT result error: The memory allocation is insufficient

8. Using Case Statements in Order by Clause

9. Using a CASE statement in the ORDER BY clause

10. Using a Case statement in a WHere clause

11. Using case stement in a where clause with an in clause

12. Efficient ways of updating a huge no of Records