In the following query:

SELECT distinct obpair_id,
isnull(case when b.ofc=0 then 0
when b.ofc=1 and a.tpc <> 0
then (case when a.tpc>130 then 10 else 5 end) end,0)
FROM tbl0 x, tbl1 s, cc a, cc b
where ( x.aml_rec_id = s.aml_rec_id
and s.country1 *= a.code
and s.country2 *= b.code)

I know what isnull does...

My question is what behavior can I expect when the first LEFT OUTER
JOIN produces a row in table "s" that does not join with table
"a" ... we know that a.column_value will be NULL, but how does that
affect the clause:
"when b.ofc=1 and a.tpc <> 0 " assuming b equals 1. and how does it
affect the
clause (case when a.tpc>130 then 10 else 5 end)

And now what if the second LEFT OUTER JOIN produces a row in table
"s" that does not join with table "b" ... what result can I expect
from the expression
case when b.ofc=0 then 0

Please post DDL and sample data. And a spec as to what you want to
get back from the query. However, you have two problems:

1) ISNULL() is dialect and needs to be replaced with ANSI/ISO Standard
COALESCE(). This is minor (but they actually are a little different),
but it tells anyone maintaining your code that they can expect a lot
of dialect and not much current SQL.

2) *= has been replaced by with ANSI/ISO Standard LEFT OUTER JOIN. I
would have to think about how *= works in SQL Server -- Other vendors
that had "extended equality" outer join syntax are all different.
That is why nobody has used it in years.

Here is a completely wild guess at what you want; some of the data
element names are vague, so I fixed them:

SELECT DISTINCT obpair_id, -- no table qualifier?
COALESCE(CASE WHEN CC2.ofc = 0 THEN 0
WHEN CC2.ofc =1 AND CC1.tpc <> 0
THEN (CASE WHEN CC1.tpc > 130
THEN 10 ELSE 5 END)
END, 0) AS magic_nameless_integer
FROM Tbl_0 AS X
INNER JOIN
(Tbl_1 AS S
LEFT OUTER JOIN
CC AS CC1
ON S.something_country1 = CC1.something_code
LEFT OUTER JOIN
CC AS CC2
ON S.something_country2 = CC2.something_code;
ON X.aml_rec_id = S.aml_rec_id;

By re-arranging the joins and/or moving predicates to the WHERE
clause, we can change the outcome.

"metaperl" <metaperl@gmail.com> wrote in message
news:ae6be7f1-cfe0-4169-9cca-0309d9926646@a70g2000hsh.googlegroups.com...
> In the following query:
>
> SELECT distinct obpair_id,
> isnull(case when b.ofc=0 then 0
> when b.ofc=1 and a.tpc <> 0
> then (case when a.tpc>130 then 10 else 5 end) end,0)
> FROM tbl0 x, tbl1 s, cc a, cc b
> where ( x.aml_rec_id = s.aml_rec_id
> and s.country1 *= a.code
> and s.country2 *= b.code)
>
> I know what isnull does...
>
> My question is what behavior can I expect when the first LEFT OUTER
> JOIN produces a row in table "s" that does not join with table
> "a" ... we know that a.column_value will be NULL, but how does that
> affect the clause:
> "when b.ofc=1 and a.tpc <> 0 " assuming b equals 1. and how does it
> affect the
> clause (case when a.tpc>130 then 10 else 5 end)
>
> And now what if the second LEFT OUTER JOIN produces a row in table
> "s" that does not join with table "b" ... what result can I expect
> from the expression
> case when b.ofc=0 then 0
>

Don't use *=. Use LEFT OUTER JOIN.

The rule is that the JOIN / ON gets evaluated first and the WHERE clause is
applied to the result of the join. Unfortunately I don't think that is
necessarily true for *=. I'm not even sure there is a simple explanation of
how *= behaves in these circumstances. That's one reason why it is
deprecated.

--
David Portas

Well thanks for all the comments on the poor form of the SQL I have
come upon

Actually the subject was about comparing null values to those of a
data type, in this case INTEGER... I managed to find the answer via a
couple of SQL queries:



SELECT
case when NULL<>0 then 0
else 666
end

SELECT
case when NULL=0 then 0
else 666
end

and because the Number of the Beast is the result in both cases, we
see that NULL is neither equal to a number nor not equal to it... now
I can solve my problem.

Thanks dudes.

"metaperl" <metaperl@gmail.com> wrote in message
> news:ae6be7f1-cfe0-4169-9cca-0309d9926646@a70g2000hsh.googlegroups.com...
>> In the following query:
>>
>> SELECT distinct obpair_id,
>> isnull(case when b.ofc=0 then 0
>> when b.ofc=1 and a.tpc <> 0
>> then (case when a.tpc>130 then 10 else 5 end) end,0)
>> FROM tbl0 x, tbl1 s, cc a, cc b
>> where ( x.aml_rec_id = s.aml_rec_id
>> and s.country1 *= a.code
>> and s.country2 *= b.code)
>>
>> I know what isnull does...
>>
>> My question is what behavior can I expect when the first LEFT OUTER
>> JOIN produces a row in table "s" that does not join with table
>> "a" ... we know that a.column_value will be NULL, but how does that
>> affect the clause:
>> "when b.ofc=1 and a.tpc <> 0 " assuming b equals 1. and how does it
>> affect the
>> clause (case when a.tpc>130 then 10 else 5 end)

If there is no row in A, a.tpc is null, and the condition

b.ofc = 1 and a.tpc <> 0

will evaluate to UNKNOWN and b.ofc is = 1, and the isnull expression
will return 0. As it will also if b.ofc has any other value.

>> And now what if the second LEFT OUTER JOIN produces a row in table
>> "s" that does not join with table "b" ... what result can I expect
>> from the expression
>> case when b.ofc=0 then 0

The next WHEN clause will be evaluated as b.ofc = 0 isn't TRUE here.

As David said, by all means use LEFT JOIN. *= is deprecated for all
good reasons. I have assumed here that the behaviour is the same as
with LEFT JOIN, but you never know.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Right,

"metaperl" <metaperl@gmail.com> wrote in message
news:ceabd154-c863-40be-9698-9130ac3df431@8g2000hse.googlegroups.com...
> Well thanks for all the comments on the poor form of the SQL I have
> come upon

Oh and Celko was pulling punches too ":-)

>
> Actually the subject was about comparing null values to those of a
> data type, in this case INTEGER... I managed to find the answer via a
> couple of SQL queries:
>

Right. NULL is never equal to anything, even another NULL.

It's also not even NOT equal to NULL.

NULL is a funny beast. :-)

>
>
> SELECT
> case when NULL<>0 then 0
> else 666
> end
>
> SELECT
> case when NULL=0 then 0
> else 666
> end
>
> and because the Number of the Beast is the result in both cases, we
> see that NULL is neither equal to a number nor not equal to it... now
> I can solve my problem.
>
> Thanks dudes.

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

--CELKO-- wrote:

> 1) ISNULL() is dialect and needs to be replaced with ANSI/ISO Standard
> COALESCE(). This is minor (but they actually are a little different),
> but it tells anyone maintaining your code that they can expect a lot
> of dialect and not much current SQL.

Out of curiosity, is there any functional difference other than
COALESCE() not being limited to two arguments?

Also, is there a particular reason to wrap (S, CC1, CC2) in
parentheses, rather than just do the following (which keeps
each join condition adjacent to its corresponding join)?

FROM Tbl_0 AS X
INNER JOIN Tbl_1 AS S ON X.aml_rec_id = S.aml_rec_id
LEFT OUTER JOIN CC AS CC1 ON S.something_country1 = CC1.something_code
LEFT OUTER JOIN CC AS CC2 ON S.something_country2 = CC2.something_code

Also, it might be clearer to flatten the nested CASEs into one level:

COALESCE(CASE WHEN CC2.ofc = 0 THEN 0
WHEN CC2.ofc = 1 AND CC1.tpc > 130 THEN 10
WHEN CC2.ofc = 1 AND CC1.tpc <> 0 THEN 5
ELSE 0 END),0) AS magic_nameless_integer

>> Out of curiosity, is there any functional difference other than COALESCE() not being limited to two arguments? <<

COALESCE correctly promotes its result to the highest data type in the
expression list:

13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5

The proprietary ISNULL() uses the first data type and gets things
wrong

13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6

You would need to write:

13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)

>> Also, is there a particular reason to wrap (S, CC1, CC2) in parentheses, rather than just do the following (which keeps each join condition adjacent to its corresponding join)? <<

To force an order of execution that will keep X as the preserved
table. I am not sdure what the guy really meant to say.

>> Also, it might be clearer to flatten the nested CASEs into one level: <<

Agreed