|
|
Posted by Marco van de Voort on 11/22/05 17:43
Hi,
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 ?
The basic problem is that there are persons that are not "attached" via the
joins to the defauction row where defauction.defid=<parameter>
that should be included if :MYCAPTIVE=0, but not if
:MYCAPTIVE=1 (hence the left join)
If :MYCAPTIVE=1 and the person can be linked to defauction ":defid" then there
are some extra conditions.
:MYCAPTIVE is the result of a subquery (select captive from auction where defid=:defid)
which is guaranteed to have one result, and one result only.
I haven't decided yet if I should substitute that, since I don't know yet if
I also need that value for UI purposes, and need a separate select anyway.
Thnks a lot for any comments. I'm really stuck here: _)
Navigation:
[Reply to this message]
|