Solved

"ALL" keyword in WHERE clause

Posted on 2001-07-06
3
239 Views
Last Modified: 2008-03-06
Is "All" a keyword in SQL?  I have this WHERE clause:

SELECT T1."Descr" c1, T2."ITMSCP" c2, T2."ITMITM" c3, T2."ITMCBL" c4, T2."ITMSTS" c5, T2."ITMTYP" c6, T2."ITMNME" c7, T2."ITMCRD" c8, T2."ITMPHN" c9, T2."ITMEML" c10, T2."ITMDTE" c11, T2."ITMEDT" c12, T3."ALSCDE" c13, T4."ICTNME" c14, T4."ICTCOD" c15

FROM "ACCOUNT" T1, "KITTRAK"."dbo"."ICTMST1" T4, ("ITMMST1" T2 LEFT OUTER JOIN "dbo"."ALSMST1" T3 on T2."ITMCMP"=T3."ALSCMP" and T2."ITMITM"=T3."ALSITM")

WHERE T2."ITMCMP"=T1."AccountID" and T2."ITMCAT"=T4."ICTCOD" and ('All'=lower ('all') or T2."ITMITM" like 'All%') and T2."ITMSCP" in ('LAN') and T2."ITMEDT">=20010101 and T2."ITMEDT"<=20011231 and T2."ITMCAT" in ('D')

ORDER BY 2 asc, 14 asc, 3 asc, 13 asc


Any ideas?  Can't seem to understand.
0
Comment
Question by:svfafel
3 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 100 total points
Comment Utility
In this context - no.  The condition 'All' = lower('all') will never be true.

However, if this query string is built dynamically, I would believe that this is a syntax error.  I would speculate that one of these fields ('All' or 'all') are added to the string on the select statement to handle specific cases of matches, and the syntax is just incorrect.

Examining the selection further, I would say that the 'All' is the variable value, and is used theoretically to indicate that all items are being selected - in this case, the syntax should be:

lower('All') = 'all'

0
 
LVL 3

Expert Comment

by:ibro
Comment Utility
Agree with bhess1 - your condition ['All'=lower('all') will never be true. You can also optimize some clause in the where clause, so you query can run faster:
T2."ITMCAT" in ('D') will run faster if it written as
T2."ITMCAT"='D'
The same is valid for:
T2."ITMEDT">=20010101 and T2."ITMEDT"<=20011231
It will become:
T2."ITMEDT" between 20010101 and 20011231







0
 

Author Comment

by:svfafel
Comment Utility
Thanks...much appreciated!!!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now