Posted by Marco van de Voort on 11/23/05 12:28

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 you say:
>
> SELECT ...
> FROM a
> LEFT JOIN b ON a.col = b.col
> WHERE b.othercol = 1
>
> You have in fact changed the outer join into an inner join. This is
> because first you take all rows in a, and hang on all columns in b.
> Where there is a macthing row in b, there will be values in those
> columns, else there will be NULLs. Thats FROM a LEFT JOIN b.

I understand your explanation thnx. Pulling in the subquery into the from
didn't work since it needed data from tables later in the join.

I solved it atm with a union that strings together the different cases
using union, and uses a very ugly subquery to exclude items.

(so the basic structure is a bit like
query1
union
query2 and not in query1
)

The personid is indexed though, so probably won't hurt that bad. (at least
IIRC this wasn't too bad in Oracle, so assume SQLS the same, person has +/-
3000 entries):

select p.* , CAST(0 as bit) AS NONCAPT from defauction a
join captive c on a.lessor=c.organisation_id
join adres adr on c.buyer_id = adr.organisationid
join person p on p.adres_id=adr.adresid
where (a.defid=30) and (a.Lessor > 0) and
(((a.captive = 1 ) and (c.captrelationtype in (1,2))) or
((a.captive = 0) and (c.captrelationtype=1) )
)
union
select p.*, CAST(1 as bit) AS NONCAPT from person p
join adres a on p.adres_id=a.adresid
join organisation o on o.organisationid=a.organisationid
where
(o.orgtype=3) and
exists (select * from defauction where defid=30 and captive=0) and
not exists
( select * from defauction a
join captive c on a.lessor=c.organisation_id
join adres adr on c.buyer_id = adr.organisationid
join person p2 on p.adres_id=adr.adresid
where (a.defid=30) and (p2.personid=p.personid)
)

[Back to original message]