|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Case Statement Woes
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 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 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! -- Show quoteHide quoteRegards, 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" <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 > > >  
Other interesting topics
SQL Server 2005 Linked Server IDENTITY_INSERT
how can I tell if a query is running SQL Server high utilization Difference between Index & Statistics SQL Server 2005 Sgent will not start - Service Time out error CPU usage and troubleshoot (sp_who2, profiling) How to read SQL file ? Outer Join Problem - hardest query ever? Could not allocate space for object 'xxx' in database 'abc' becaus Is there a way to view SP usage stats? |
|||||||||||||||||||||||
