Microsoft SQL Server forum

Author

query problem

Marco van de Voort

2005-11-22, 11:23 am

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: _)

Erland Sommarskog

2005-11-22, 8:23 pm

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.

> 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)

Since you did not give a clear description of what is going wrong, this
will have to be guessworks.

But there is one thing that is possibly wrong with the outer join, so
permit me to discuss that.

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.

Once the from clause (which includes all JOIN operators) has resulted
into a table, this table is filtered by WHERE. Since WHERE here says
b.othercol = 1, then all rows with NULL in b.othercol are dropped from
the result set.

If you instead do:

SELECT ...
FROM a
LEFT JOIN b ON a.col = b.col
AND b.othercol = 1

It's a different story. Now the JOIN results in a table where the columns
from b are NULL when the key from a did not match - or othercol had any
other value than 1.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Marco van de Voort

2005-11-23, 7:24 am

On 2005-11-22, Erland Sommarskog <esquel@sommarskog.se> wrote:
> Marco van de Voort (marcov@stack.nl) writes:
>
> 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)
)

Erland Sommarskog

2005-11-23, 7:24 am

Marco van de Voort (marcov@stack.nl) writes:
> On 2005-11-22, Erland Sommarskog <esquel@sommarskog.se> wrote:
>
> 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sponsored Links