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