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

Ed Murphy (emurphy42@socal.rr.com) writes:
> Out of curiosity, is there any functional difference other than
> COALESCE() not being limited to two arguments?

Yes. Try this:

DECLARE @x sql_variant,
@y varchar(10)

SELECT @x = isnull(@y, 0)
SELECT sql_variant_property(@x, 'BaseType')
SELECT @x = coalesce(@y, 0)
SELECT sql_variant_property(@x, 'BaseType')

The return type from isnull, is always the type of the first value.
The return value of coalesce is as for any other CASE expression: the
data type with the highest precedence.

There are also situations where you can only use one of them. For instance,
when building indexed views, only isnull is permitted, but not coalesce.


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