Case Statement Woes

3 May 2007 8:24 PM

thejamie

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

4 May 2007 1:21 AM

Mike C#

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.

Show quoteHide quote

"thejamie" <theja***@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

4 May 2007 1:55 PM

thejamie

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

Show quoteHide quote

"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" <theja***@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
>
>
>