Case Statements

Can anyone help me with the case statements?
I have customized a new report for our application (vendor software). I want the end user to check 3 boxes to print (they can choose all or one or two).

Below is the case statement I tired in the where clause

Where
STATUS = CASE
WHEN REPORT.SEPARATED = 'y' THEN 'S'
WHEN REPORT.TERMINATED = 'y' THEN 'T'
WHEN REPORT.RETIRED ='y' THEN 'R'
END

I want the user to check one box , two or 3 . Rightnow it is working fine if any one option is checked. Please let me if there is any other way to do this.

many thanks

Quote:

Originally Posted by anishap

Can anyone help me with the case statements?
I have customized a new report for our application (vendor software). I want the end user to check 3 boxes to print (they can choose all or one or two).

Below is the case statement I tired in the where clause

Where
STATUS = CASE
WHEN REPORT.SEPARATED = 'y' THEN 'S'
WHEN REPORT.TERMINATED = 'y' THEN 'T'
WHEN REPORT.RETIRED ='y' THEN 'R'
END

I want the user to check one box , two or 3 . Rightnow it is working fine if any one option is checked. Please let me if there is any other way to do this.

many thanks

i think this would be better if you build the WHERE from your form/gui. you have might have to adjust the returned value of your checkbox. depending on your front-end tool.

something like

queryvar = queryvar + ' WHERE STATUS IN ('
for counter = numbercheckbox
if checkbox is checked
queryvar = queryvar + returnedvalueofcheckbox

queryvar = queryvar + ')'

this is a psedocode. am suggesting the technique, not the syntax. depending on your gui, you're going to have to adjust it accordingly

CASE Statement, works but.....

My CASE Statement works fine like this:

================================================== =======
CASE

WHEN so.PhoneNum LIKE '999%' THEN 'Chester'

END AS Company,(Works!)
================================================== =====

But how can I get it to work checking more than 1 instance of PhoneNum?

This doesn't work:

================================================== ====
CASE

WHEN so.PhoneNum LIKE '999%' THEN 'City1'
WHEN so.PhoneNum LIKE '997%' THEN 'City2 '
WHEN so.PhoneNum LIKE '998%' THEN 'City3'

END AS Company,(Fails!)
================================================== ====

This fails with an out put of "NULL"

ThanksFound the solution - this works!

[Company] =
CASE
WHEN so.PhoneNum LIKE '999%' THEN 'Chester'
WHEN so.PhoneNum LIKE '998%' THEN 'Camden'
WHEN so.PhoneNum LIKE '997%' THEN 'Great Falls'
WHEN so.PhoneNum LIKE '803%' THEN 'Winnsboro'
END,

Case Statement(Help Please)

I am trying to use a case statement to pull bad phone numbers out of my
company table into a readable format. I am using SQL Server 2000 SP4.
The phone numbers, in my inherited database, are in any number of formats,
not complete, etc, and I need to pull a report showing only the good phone
numbers.
My Case statement returns the error below the statement.
-- SELECT SUBSTRING((RTRIM(phone) + ' '+
RTRIM(phone) + ' '), 1, 25) AS Name, phone,
Phone =
CASE
WHEN SUBSTRING(phone, 1, 2) > len(10) THEN 'Phone'
WHEN SUBSTRING(phone, 1, 2) < len(10) THEN 'Phone'
END
FROM company
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '+(' to a column of data type int.
RayRay wrote on Fri, 11 Nov 2005 07:05:07 -0800:

> I am trying to use a case statement to pull bad phone numbers out of my
> company table into a readable format. I am using SQL Server 2000 SP4.
> The phone numbers, in my inherited database, are in any number of formats,
> not complete, etc, and I need to pull a report showing only the good phone
> numbers.
> My Case statement returns the error below the statement.
> -- SELECT SUBSTRING((RTRIM(phone) + ' '+
> RTRIM(phone) + ' '), 1, 25) AS Name, phone,
> Phone =
> CASE
> WHEN SUBSTRING(phone, 1, 2) > len(10) THEN 'Phone'
> WHEN SUBSTRING(phone, 1, 2) < len(10) THEN 'Phone'
> END
> FROM company
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value '+(' to a column of data type
> int.
SUBSTRING(phone, 1, 2) > len(10) is the problem
This takes the first 2 characters of the phone column, and compares it to
the length of the string '10' (implicit conversion of the LEN expression to
a string). The first 2 characters are '(+', and due to implicit conversion
will try to convert that to an integer to compare to the value 2.. What
exactly are you trying to achieve here? It makes no sense.
Dan|||I am trying to pull good phone numbers from a list of bad numbers.
For Example:+ () -
+ () -3681
+ () -8544
+ () ?
+ () ?
+ () 0-+00-00
+ () 0-00
+ () 0-00
+ () 0-00--00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-000
+ () 00-000
+ () 011-1-4756-6900
+ () 011-234-1266-89
+ () 011-2711-320-50
+ () 011-2711-377-38
+ () 011-322-545-252
+ () 011-331-4323-20
+ () 011-358-013360
+ () 011-418-643-307
+ () 011-44-002-7551
+ () 011-45-4468-446
+ () 011-468-719-500
+ () 011-495-254-991
+ () 011-603-707-449
+ () 011-612-4655-24
I inhereted this database from someone who just put stuff in anyway they
wanted. Now I need to pull out the ones formatted corrected so they can be
inputted into a new system.
Ray
"Daniel Crichton" wrote:

> Ray wrote on Fri, 11 Nov 2005 07:05:07 -0800:
>
> SUBSTRING(phone, 1, 2) > len(10) is the problem
> This takes the first 2 characters of the phone column, and compares it to
> the length of the string '10' (implicit conversion of the LEN expression t
o
> a string). The first 2 characters are '(+', and due to implicit conversion
> will try to convert that to an integer to compare to the value 2.. What
> exactly are you trying to achieve here? It makes no sense.
> Dan
>
>|||Ray wrote on Fri, 11 Nov 2005 07:39:16 -0800:

> I am trying to pull good phone numbers from a list of bad numbers.
Sorry, I guess I wasn't being clear enough. From the SELECT statement you
provided I can't figure out what you're trying to do. I can only guess that
you're looking for strings of at least 10 characters after the +( at the
start, but as to what you want returned it makes no sense - why would you
display the first 25 characters of the phone number concatentated to itself
as the name column, and then show the phone number again as phone, and then
appear to just put the string 'Phone' into another column called Phone? A
long list of numbers doesn't explain anything. What is a well formatted
phone number? None of those I would consider well formatted, but then again
I'm a UK resident and we have a different number format here.
Dan|||Daniel Crichton wrote:
> Ray wrote on Fri, 11 Nov 2005 07:39:16 -0800:
>
> Sorry, I guess I wasn't being clear enough. From the SELECT statement
> you provided I can't figure out what you're trying to do. I can only
> guess that you're looking for strings of at least 10 characters after
> the +( at the start, but as to what you want returned it makes no
> sense - why would you display the first 25 characters of the phone
> number concatentated to itself as the name column, and then show the
> phone number again as phone, and then appear to just put the string
> 'Phone' into another column called Phone? A long list of numbers
> doesn't explain anything. What is a well formatted phone number? None
> of those I would consider well formatted, but then again I'm a UK
> resident and we have a different number format here.
Another idea thrown into the mix: write a user defined function that
either maps a given phone number to a valid one or maps phone numbers to
something like "ok", "maybe", "crap". Then you can easily select
your_function(phone) = 'ok'.
Kind regards
robert

Case Statement!

Hi all,
I am trying to return a true / false value via case statement.
The boolean value returned is determined whether a column contains a null
value.
Can someone help with the following query as it is causing an error...
SELECT
q.ColumnID, q.ColumnText,
CASE a.ColumnID
WHEN IsNull THEN 0
WHEN Not IsNull Then 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
Cheers,
Adam
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID IS NULL THEN 0 ELSE 1 END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID|||Try (untested)
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID IsNull THEN 0
WHEN a.ColumnID Is not Null Then 1
END AS colAlias
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in message
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I am trying to return a true / false value via case statement.
> The boolean value returned is determined whether a column contains a null
> value.
> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
> Cheers,
> Adam
>|||SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID
Is Null THEN 0
ELSE 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in message
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I am trying to return a true / false value via case statement.
> The boolean value returned is determined whether a column contains a null
> value.
> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
> Cheers,
> Adam
>|||Try this.
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID Is Null THEN 0 ELSE 1 END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID|||"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl:

> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
I suspect that you're misusing the ISNULL function. AFAICT, the syntax
for ISNULL is: ISNULL ( check_expression , replacement_value )
Try something like:
CASE ISNULL(a.ColumnID, 0)
WHEN 0 THEN 0
ELSE 1
END AS aID
or
CASE a.ColumnID
WHEN NULL THEN 0
ELSE 1
END AS aID
HTH,
Geoff Lane
Cornwall, UK|||Hi Adam,
SELECT
q.ColumnID, q.ColumnText,
CASE a.ColumnID
WHEN NULL THEN 0
ELSE 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
HTH, Jens Suessmeyer.sql

Case Statement Woes

I have the case statement below and when the processing runs (set
transname=(case stmt...) or insert into table (col1,col2...) select col1,case
stmt from anothertable, the processing from within the part of the case
statement that is below the "when isnull(trans_id,0)=1105" runs, it does not
process the statements inside the case within the case. I suspect I have
committed an error but I can't pin it down. Help appreciated.
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
Location' ELSE 'Move To Location' END -- move it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END
WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
'Undefined' END
WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
'Undefined' END
WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND Upper(tloc_id)='OF-OUT'
THEN 'Finished (END of Roll)' ELSE 'Undefined' END
ELSE 'Undefined'
END
Regards,
JamieWell let's indent your CASE "expression" to make it a little easier to
troubleshoot:
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN
CASE
WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
ELSE 'Move To Location'
END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END /* CASE Upper(tloc_id) */
WHEN 20 THEN
CASE
WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) like 'RC%' */
WHEN 25 THEN
CASE
WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
WHEN 26 THEN
CASE STATUS
WHEN 0 THEN 'Available'
ELSE 'Hold'
END /* CASE STATUS */
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN
CASE
WHEN Upper(floc_id)='OF-OUT'
AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
WHEN floc_id IS NULL /* Upper() not necessary */
AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
ELSE 'Undefined-2' /* Used to be Undefined */
END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
ELSE 'Undefined'
END /* CASE ISNULL(trans_id,0) */
Are you sure it's not processing the expressions inside the case expression?
I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
at the expressions in that CASE expression and see if they can be True at
any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
drop through to the ELSE. If those criteria aren't exactly matched, then
you can expect it to drop through to the ELSE.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>I have the case statement below and when the processing runs (set
> transname=(case stmt...) or insert into table (col1,col2...) select
> col1,case
> stmt from anothertable, the processing from within the part of the case
> statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> not
> process the statements inside the case within the case. I suspect I have
> committed an error but I can't pin it down. Help appreciated.
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> Location' ELSE 'Move To Location' END -- move it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END
> WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> 'Undefined' END
> WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> 'Undefined' END
> WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> Upper(tloc_id)='OF-OUT'
> THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> ELSE 'Undefined'
> END
> Regards,
> Jamie|||You're right about it getting through the case statement with no problem.
Looks like I am looking in the wrong place. I appreciate the help. I think
I can forget about it from this end of the problem. The other records with
nulls show up just fine as 'Undefined-2'
Thanks Mike!
--
Regards,
Jamie
"Mike C#" wrote:
> Well let's indent your CASE "expression" to make it a little easier to
> troubleshoot:
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN
> CASE
> WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
> ELSE 'Move To Location'
> END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
> it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END /* CASE Upper(tloc_id) */
> WHEN 20 THEN
> CASE
> WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) like 'RC%' */
> WHEN 25 THEN
> CASE
> WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
> WHEN 26 THEN
> CASE STATUS
> WHEN 0 THEN 'Available'
> ELSE 'Hold'
> END /* CASE STATUS */
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN
> CASE
> WHEN Upper(floc_id)='OF-OUT'
> AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
> WHEN floc_id IS NULL /* Upper() not necessary */
> AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
> ELSE 'Undefined-2' /* Used to be Undefined */
> END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
> ELSE 'Undefined'
> END /* CASE ISNULL(trans_id,0) */
> Are you sure it's not processing the expressions inside the case expression?
> I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
> at the expressions in that CASE expression and see if they can be True at
> any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
> drop through to the ELSE. If those criteria aren't exactly matched, then
> you can expect it to drop through to the ELSE.
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
> >I have the case statement below and when the processing runs (set
> > transname=(case stmt...) or insert into table (col1,col2...) select
> > col1,case
> > stmt from anothertable, the processing from within the part of the case
> > statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> > not
> > process the statements inside the case within the case. I suspect I have
> > committed an error but I can't pin it down. Help appreciated.
> >
> > CASE ISNULL(trans_id,0)
> > WHEN 5 THEN
> > CASE Upper(tloc_id)
> > WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> > WHEN 'FIN-INITG' THEN 'To Burlington'
> > WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> > Location' ELSE 'Move To Location' END -- move it in
> > WHEN 'REWORK' THEN 'Rework'
> > ELSE 'Move To Location'
> > END
> > WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> > 'Undefined' END
> > WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> > 'Undefined' END
> > WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> > WHEN 101 THEN 'Shipped'
> > WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> > 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> > Upper(tloc_id)='OF-OUT'
> > THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> > ELSE 'Undefined'
> > END
> >
> > Regards,
> > Jamie
>
>

Case Statement Woes

I have the case statement below and when the processing runs (set
transname=(case stmt...) or insert into table (col1,col2...) select col1,case
stmt from anothertable, the processing from within the part of the case
statement that is below the "when isnull(trans_id,0)=1105" runs, it does not
process the statements inside the case within the case. I suspect I have
committed an error but I can't pin it down. Help appreciated.
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
Location' ELSE 'Move To Location' END -- move it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END
WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
'Undefined' END
WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
'Undefined' END
WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
WHEN 101THEN 'Shipped'
WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
'Finished (Unbatch)' WHEN Upper(floc_id)IS NULL AND Upper(tloc_id)='OF-OUT'
THEN 'Finished (END of Roll)' ELSE 'Undefined' END
ELSE 'Undefined'
END
Regards,
Jamie
Well let's indent your CASE "expression" to make it a little easier to
troubleshoot:
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN
CASE
WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
ELSE 'Move To Location'
END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END /* CASE Upper(tloc_id) */
WHEN 20 THEN
CASE
WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) like 'RC%' */
WHEN 25 THEN
CASE
WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
WHEN 26 THEN
CASE STATUS
WHEN 0 THEN 'Available'
ELSE 'Hold'
END /* CASE STATUS */
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN
CASE
WHEN Upper(floc_id)='OF-OUT'
AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
WHEN floc_id IS NULL /* Upper() not necessary */
AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
ELSE 'Undefined-2' /* Used to be Undefined */
END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
ELSE 'Undefined'
END /* CASE ISNULL(trans_id,0) */
Are you sure it's not processing the expressions inside the case expression?
I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
at the expressions in that CASE expression and see if they can be True at
any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
drop through to the ELSE. If those criteria aren't exactly matched, then
you can expect it to drop through to the ELSE.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>I have the case statement below and when the processing runs (set
> transname=(case stmt...) or insert into table (col1,col2...) select
> col1,case
> stmt from anothertable, the processing from within the part of the case
> statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> not
> process the statements inside the case within the case. I suspect I have
> committed an error but I can't pin it down. Help appreciated.
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> Location' ELSE 'Move To Location' END -- move it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END
> WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> 'Undefined' END
> WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> 'Undefined' END
> WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> Upper(tloc_id)='OF-OUT'
> THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> ELSE 'Undefined'
> END
> Regards,
> Jamie
|||You're right about it getting through the case statement with no problem.
Looks like I am looking in the wrong place. I appreciate the help. I think
I can forget about it from this end of the problem. The other records with
nulls show up just fine as 'Undefined-2'
Thanks Mike!
Regards,
Jamie
"Mike C#" wrote:

> Well let's indent your CASE "expression" to make it a little easier to
> troubleshoot:
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN
> CASE
> WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
> ELSE 'Move To Location'
> END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
> it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END /* CASE Upper(tloc_id) */
> WHEN 20 THEN
> CASE
> WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) like 'RC%' */
> WHEN 25 THEN
> CASE
> WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
> WHEN 26 THEN
> CASE STATUS
> WHEN 0 THEN 'Available'
> ELSE 'Hold'
> END /* CASE STATUS */
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN
> CASE
> WHEN Upper(floc_id)='OF-OUT'
> AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
> WHEN floc_id IS NULL /* Upper() not necessary */
> AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
> ELSE 'Undefined-2' /* Used to be Undefined */
> END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
> ELSE 'Undefined'
> END /* CASE ISNULL(trans_id,0) */
> Are you sure it's not processing the expressions inside the case expression?
> I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
> at the expressions in that CASE expression and see if they can be True at
> any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
> drop through to the ELSE. If those criteria aren't exactly matched, then
> you can expect it to drop through to the ELSE.
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>
>

Case Statement Woes

I have the case statement below and when the processing runs (set
transname=(case stmt...) or insert into table (col1,col2...) select col1,cas
e
stmt from anothertable, the processing from within the part of the case
statement that is below the "when isnull(trans_id,0)=1105" runs, it does not
process the statements inside the case within the case. I suspect I have
committed an error but I can't pin it down. Help appreciated.
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
Location' ELSE 'Move To Location' END -- move it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END
WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
'Undefined' END
WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
'Undefined' END
WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND Upper(tloc_id)='OF-OUT'
THEN 'Finished (END of Roll)' ELSE 'Undefined' END
ELSE 'Undefined'
END
Regards,
JamieWell let's indent your CASE "expression" to make it a little easier to
troubleshoot:
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN
CASE
WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
ELSE 'Move To Location'
END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END /* CASE Upper(tloc_id) */
WHEN 20 THEN
CASE
WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) like 'RC%' */
WHEN 25 THEN
CASE
WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
WHEN 26 THEN
CASE STATUS
WHEN 0 THEN 'Available'
ELSE 'Hold'
END /* CASE STATUS */
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN
CASE
WHEN Upper(floc_id)='OF-OUT'
AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
WHEN floc_id IS NULL /* Upper() not necessary */
AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
ELSE 'Undefined-2' /* Used to be Undefined */
END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
ELSE 'Undefined'
END /* CASE ISNULL(trans_id,0) */
Are you sure it's not processing the expressions inside the case expression?
I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
at the expressions in that CASE expression and see if they can be True at
any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
drop through to the ELSE. If those criteria aren't exactly matched, then
you can expect it to drop through to the ELSE.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>I have the case statement below and when the processing runs (set
> transname=(case stmt...) or insert into table (col1,col2...) select
> col1,case
> stmt from anothertable, the processing from within the part of the case
> statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> not
> process the statements inside the case within the case. I suspect I have
> committed an error but I can't pin it down. Help appreciated.
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> Location' ELSE 'Move To Location' END -- move it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END
> WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> 'Undefined' END
> WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> 'Undefined' END
> WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> Upper(tloc_id)='OF-OUT'
> THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> ELSE 'Undefined'
> END
> Regards,
> Jamie|||You're right about it getting through the case statement with no problem.
Looks like I am looking in the wrong place. I appreciate the help. I think
I can forget about it from this end of the problem. The other records with
nulls show up just fine as 'Undefined-2'
Thanks Mike!
--
Regards,
Jamie
"Mike C#" wrote:

> Well let's indent your CASE "expression" to make it a little easier to
> troubleshoot:
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN
> CASE
> WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Locatio
n'
> ELSE 'Move To Location'
> END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- mov
e
> it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END /* CASE Upper(tloc_id) */
> WHEN 20 THEN
> CASE
> WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) like 'RC%' */
> WHEN 25 THEN
> CASE
> WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
> WHEN 26 THEN
> CASE STATUS
> WHEN 0 THEN 'Available'
> ELSE 'Hold'
> END /* CASE STATUS */
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN
> CASE
> WHEN Upper(floc_id)='OF-OUT'
> AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
> WHEN floc_id IS NULL /* Upper() not necessary */
> AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
> ELSE 'Undefined-2' /* Used to be Undefined */
> END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
> ELSE 'Undefined'
> END /* CASE ISNULL(trans_id,0) */
> Are you sure it's not processing the expressions inside the case expressio
n?
> I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Lo
ok
> at the expressions in that CASE expression and see if they can be True at
> any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
> drop through to the ELSE. If those criteria aren't exactly matched, then
> you can expect it to drop through to the ELSE.
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>
>