Posted by Erland Sommarskog on 11/23/05 13:47

Marco van de Voort (marcov@stack.nl) writes:
> On 2005-11-22, Erland Sommarskog <esquel@sommarskog.se> wrote:
>> Marco van de Voort (marcov@stack.nl) writes:
>>> I'm a relative db newbie, and total SQL server newbie and I garbled up
>>> the following query ( :xxx are parameters that are filled in by the
>>> app)
>>>
>>>
>>> SELECT PERSON.*,
>>> CASE ISNULL(cap.captiveid, 0) WHEN 0 THEN cast(0 AS bit)
>>> ELSE cast(1 AS bit) END AS ItemPresent
>>> FROM PERSON p
>>> INNER JOIN ADRES a ON (a.ADRESID = p.ADRES_ID)
>>> LEFT JOIN CAPTIVE cap ON (a.ORGANISATIONID = CAP.BUYER_ID)
>>> INNER JOIN DEFAUCTION auc
>>> ON (auc.LESSOR = cap.ORGANISATION_ID)
>>> WHERE
>>> (ITEMPRESENT=0 AND :MYCAPTIVE=0) OR
>>> (auc.DEFID=:DEFID AND
>>> ((:MYCAPTIVE = 0 AND CAP.CAPTRELATIONTYPE = 1) OR
>>> (:MYCAPTIVE= 1))
>>> )
>>>
>>> It seems sqlserver doesn't allow a calculated field as itempresent in
>>> the where clause. Does sb see a workaround or alternate way ?
>>
>> Does not allow? Do you get an error message, please post it.
>
> Invalid columnname. (itempresent)

If ITEMPRESENT=0 in the WHERE clause is intended to related to the
CASE expression in the column list, it is indeed not permitted to use
it here. This is not peculiar to SQL Server, but applies to most DB
engines, and this behaviour is ANSI compliant. The one exception I know
of is Access.

The way you should do this in ANSI-compliant SQL engines (e.g. SQL Server
or Oracle) is to use a derived table:

SELECT *
FROM (SELECT ..., myvalue = CASE WHEN .... END
FROM tbl) AS x
WHERE myvalue = 123

But in this case, it's certainly a lot more practical to just replace
"ITEMPRESENT = 0" with "cap.captiveid IS NULL".

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]