Case Statement Help Please

Hello,
I have tried the following syntax:
UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
SET WIND =
CASE
WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
END
and it is not working - I get the error of
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '='.
I'm not sure what is missing/wrong on that line?
Thanks!Your syntax is wrong, the THEN clause of a CASE cannot be a boolean
expression -- it must be scalar. Please refer to SQL Server Books Online for
syntax details.
Based on a brief glance through the code, the comparable t-SQL code would be
something along the lines of:
UPDATE stage_phx_fact_policy_rf
SET wind = ( SELECT short_code_2
FROM wh_view_endorsement v1
WHERE v1.policy_number
= stage_phx_fact_policy_rf.policy_number
AND v1.policy_date_time
= stage_phx_fact_policy_rf.policy_date_time
AND v1.reference_number
= stage_phx_fact_policy_rf.sequence_number )
WHERE occupancy = 'poultry'
AND EXISTS ( SELECT *
FROM wh_view_endorsement v1
WHERE v1.policy_number
= stage_phx_fact_policy_rf.policy_number
AND v1.policy_date_time
= stage_phx_fact_policy_rf.policy_date_time
AND v1.reference_number
= stage_phx_fact_policy_rf.sequence_number ) ;
If only unique columns are participating in the correlation, you can use
t-SQL FROM clause as well.
Anith|||Patrice,
You have to put the END statement at the point you are finished with the
CASE clause:
UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
SET WIND =
CASE WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
END
WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
John Scragg
"Patrice" wrote:

> Hello,
> I have tried the following syntax:
> UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
> SET WIND =
> CASE
> WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
> THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
> WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
> WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
> END
>
>
>
> and it is not working - I get the error of
> Server: Msg 170, Level 15, State 1, Line 6
> Line 6: Incorrect syntax near '='.
>
> I'm not sure what is missing/wrong on that line?
> Thanks!
>

CASE NOT equal

I have the following statement...
CASE
WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
THEN 'Not Equal'
ELSE
'Equal'
END
The value for SRV.srv_package is NULL and the value for
PkgtoSRV.Package_SRV is 2006-05-05. So why does this query return...Equal?
When they are clearly NOT equal. Am I failing to use CASE incorrectly
here? Can I NOT use <>?
Any help would be GREATLY appreciated...
wnfisbaNo, nulls do not come under anything. So, usually nulls are not predictable.
add this to the first line and try
SET ANSI_NULLS OFF
and try it..
if it still doesn't work
then do this... hope this helps.
CASE
WHEN isnull(SRV.srv_package,0) <> isnull(PkgtoSRV.Package_SRV,0)
THEN 'Not Equal'
ELSE
'Equal'
END
"wnfisba" wrote:

> I have the following statement...
> CASE
> WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
> THEN 'Not Equal'
> ELSE
> 'Equal'
> END
> The value for SRV.srv_package is NULL and the value for
> PkgtoSRV.Package_SRV is 2006-05-05. So why does this query return...Equal?
'
> When they are clearly NOT equal. Am I failing to use CASE incorrectly
> here? Can I NOT use <>?
> Any help would be GREATLY appreciated...
> wnfisba|||NULL is unknown. Repeating something I posted yesterday:
<snip>
Nothing will ever = NULL, since the definition of NULL is unknown.
Think about it this way, if I have a form with a field that says "gender"
and I forget to check either male or female, can you say with any certainty
that I am:
(a) male?
(b) female?
(c) not male?
(d) not female?
Further, can you say with any certainty that someone else, who also forgot
to specify their gender, is:
(a) the same gender as me?
(b) the opposite gender from me?
(c) not the same gender as me?
(d) not the opposite gender from me?
</snip>
So, in order to do this comparison, you either need to account for NULLs in
the ELSE, or use COALESCE to allow bogus values into the comparison.
CASE
WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
THEN 'Not Equal'
WHEN SRV.srv_package = PkgtoSRV.Package_SRV
THEN 'Equal'
ELSE 'Unknown - one or both are NULL'
END
-- assuming -1 is not a possible value
-- though, I don't even have an idea what data type you are using
CASE WHEN COALESCE(SRV.srv_package, -1) = COALESCE(PkgtoSRV.Package_SRV, -1)
THEN 'Equal'
ELSE 'Not Equal'
END
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:E25E0D4E-0113-401A-A07C-FB7B86C7DC96@.microsoft.com...
>I have the following statement...
> CASE
> WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
> THEN 'Not Equal'
> ELSE
> 'Equal'
> END
> The value for SRV.srv_package is NULL and the value for
> PkgtoSRV.Package_SRV is 2006-05-05. So why does this query
> return...Equal?
> When they are clearly NOT equal. Am I failing to use CASE incorrectly
> here? Can I NOT use <>?
> Any help would be GREATLY appreciated...
> wnfisba

Case help and Identity help

I have this query

SELECT 'bracket' = CASE
WHEN income BETWEEN 0 AND 49 THEN '0-49'
WHEN income BETWEEN 50 AND 99 THEN '50-99'
WHEN income BETWEEN 100 AND 499 THEN '100-499'
WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
ELSE 'Other' END, count(income) AS number
FROM #persons
GROUP BY CASE
WHEN income BETWEEN 0 AND 49 THEN '0-49'
WHEN income BETWEEN 50 AND 99 THEN '50-99'
WHEN income BETWEEN 100 AND 499 THEN '100-499'
WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
ELSE 'Other' END
ORDER BY min(income) ASC

which returns

bracket number
--- ----
50-99 4
100-499 4
500-1000 2

I want it to return this

bracket number
--- ----
0-49 0
50-99 4
100-499 4
500-1000 2
Other 0

Showing that there are no incomes within the 0-49 category and 0
incomes in the other category. Halp?

AND

I need to get the numbers 1-1000 into a table called #thousand using
the identity function. Help?Consider creating a table Brackets, which would allow:

SELECT B.bracket, count(income) AS number
FROM Brackets as B
LEFT OUTER JOIN #persons as P
ON O.income BETWEEN B.FromIncome AND B.ToIncome
GROUP BY B.bracket
ORDER BY min(income) ASC

You will have to add one for negative numbes, and another for positive
numbers 1000, both assigned 'Other' for the bracket column.

Roy Harvey
Beacon Falls, CT

On 23 Aug 2006 12:14:02 -0700, mutemode@.gmail.com wrote:

Quote:

Originally Posted by

>I have this query
>
>SELECT 'bracket' = CASE
>WHEN income BETWEEN 0 AND 49 THEN '0-49'
>WHEN income BETWEEN 50 AND 99 THEN '50-99'
>WHEN income BETWEEN 100 AND 499 THEN '100-499'
>WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
>ELSE 'Other' END, count(income) AS number
>FROM #persons
>GROUP BY CASE
>WHEN income BETWEEN 0 AND 49 THEN '0-49'
>WHEN income BETWEEN 50 AND 99 THEN '50-99'
>WHEN income BETWEEN 100 AND 499 THEN '100-499'
>WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
>ELSE 'Other' END
>ORDER BY min(income) ASC
>
>which returns
>
>bracket number
>--- ----
>50-99 4
>100-499 4
>500-1000 2
>
>I want it to return this
>
>bracket number
>--- ----
>0-49 0
>50-99 4
>100-499 4
>500-1000 2
>Other 0
>
>Showing that there are no incomes within the 0-49 category and 0
>incomes in the other category. Halp?
>
>
>AND
>
>I need to get the numbers 1-1000 into a table called #thousand using
>the identity function. Help?

|||On 23 Aug 2006 12:14:02 -0700, mutemode@.gmail.com wrote:

(snip)

Quote:

Originally Posted by

>AND
>
>I need to get the numbers 1-1000 into a table called #thousand using
>the identity function. Help?

Hi mutemode,

SELECT TOP 1000 IDENTITY(int, 1,1) AS id
INTO #ten
FROM sysobjects AS a, sysobjects AS b

--
Hugo Kornelis, SQL Server MVP|||(mutemode@.gmail.com) writes:

Quote:

Originally Posted by

I need to get the numbers 1-1000 into a table called #thousand using
the identity function. Help?

Why IDENTITY?

Here is a script for a million numbers. It's a tad slow for a temp
table, but why temp table? A table of numbers comes in handy in
several places.

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0

If you insist on exactly 1000 numbers, you can easily cut it down. It
will probably run a lot faster than when inserting a million numbers.

--
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|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:

Quote:

Originally Posted by

On 23 Aug 2006 12:14:02 -0700, mutemode@.gmail.com wrote:
>
(snip)

Quote:

Originally Posted by

>>AND
>>
>>I need to get the numbers 1-1000 into a table called #thousand using
>>the identity function. Help?

>
Hi mutemode,
>
SELECT TOP 1000 IDENTITY(int, 1,1) AS id
INTO #ten
FROM sysobjects AS a, sysobjects AS b

IF (SELECT COUNT(*) FROM #ten) < 1000 OR
(SELECT MIN(id) FROM #ten) <1 OR
(SELECT MAX(id) FROM #ten) <1000
BEGIN
RAISERROR ('Fill of #ten failed!', 16, 1)
RETURN 1
END

That is, I don't think one should trust the code above to always return
what you looking for. Adding some paranoia can avoid incorrect results.

--
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|||On Wed, 23 Aug 2006 21:51:18 +0000 (UTC), Erland Sommarskog wrote:

Quote:

Originally Posted by

>Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:

Quote:

Originally Posted by

>On 23 Aug 2006 12:14:02 -0700, mutemode@.gmail.com wrote:
>>
>(snip)

Quote:

Originally Posted by

>>>AND
>>>
>>>I need to get the numbers 1-1000 into a table called #thousand using
>>>the identity function. Help?

>>
>Hi mutemode,
>>
>SELECT TOP 1000 IDENTITY(int, 1,1) AS id
>INTO #ten
>FROM sysobjects AS a, sysobjects AS b

>
>IF (SELECT COUNT(*) FROM #ten) < 1000 OR
(SELECT MIN(id) FROM #ten) <1 OR
(SELECT MAX(id) FROM #ten) <1000
>BEGIN
RAISERROR ('Fill of #ten failed!', 16, 1)
RETURN 1
>END
>
>That is, I don't think one should trust the code above to always return
>what you looking for. Adding some paranoia can avoid incorrect results.

Hi Erland,

Some paranoia is good, but too much is, well, too much <g>

I agree with the test for a COUNT(*) of less than 1000 (though even in
an empty database, sysobjects has 47 rows so the cross join should be
good 2209 rows).

The tests for MIN and MAX remind me of the examples of "defensive
programming" I have seen when I still programmed PL/I on a mainframe. In
T-SQL equivalent, the code read something like this:
SET @.SomeVariable = 15;
IF @.SomeVariable <15
BEGIN;
RAISERROR ('The DBMS has a bug!', 16, 1);
END;
Assuming that the IDENTITY function works as advertised, you'll never be
able to get a situation with MIN(id) other than 1 and MAX(id) other than
1000 (assuming the COUNT(*) check is passed).

Finally, the COUNT(*) check can be replaced by a much more efficient
check for @.@.ROWCOUNT. The end result would be (correcting my error in
the requested table name while I'm at it:

SELECT TOP 1000 IDENTITY(int, 1,1) AS id
INTO #thousand
FROM sysobjects AS a, sysobjects AS b;

IF @.@.ROWCOUNT < 1000
BEGIN;
RAISERROR ('Fill of #thousand failed - not enough rows in sysobjects!
Please add an extra occurence of sysobejcts to the FROM clause.', 16,
1);
ROLLBACK TRANSACTION;
END;

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:

Quote:

Originally Posted by

Assuming that the IDENTITY function works as advertised, you'll never be
able to get a situation with MIN(id) other than 1 and MAX(id) other than
1000 (assuming the COUNT(*) check is passed).

It's difficult to say what is advertised. We know that you should not
reply on ORDER BY. Here is a TOP, that I don't really whether I can trust.

The paranoid check is cheap. The cost for an unexpected result is expensive.

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