question

technette avatar image
technette asked

Case Statement

I have one column in a table that stores values that I need to separate. In this case Sales Orders and Work Orders. How do I correctly write this case statement for this column? DECLARE @PartNum nvarchar(255); DECLARE @temp2 as TABLE(salesOrder nvarchar(70), WorkOrder nvarchar(70), PurchaseOrder nvarchar(70)) INSERT INTO @temp2(salesOrder ) ( select W.BASE_ID as salesOrder from WORK_ORDER W where W.[TYPE] = 'W' and (NOT(LEFT(W.BASE_ID, 1)='R' and (NOT(LEFT(W.BASE_ID,1)='W' and W.PART_ID = @PartNum ) INSERT INTO @temp2( WorkOrder ) ( select W.BASE_ID as WorkOrder from WORK_ORDER W where W.[TYPE] = 'W' and (LEFT(W.BASE_ID, 1)='W' and W.PART_ID = @PartNum ) INSERT INTO @temp2(PurchaseOrder) ( select Purc_Order_ID from Service_Disp_Line where Service_Part_ID = @PartNum ) SELECT * FROM @temp2
case-statement
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Venkataraman avatar image
Venkataraman answered
YOU can apply CASE statement as case WHEN W.[TYPE] = 'W' and (NOT(LEFT(W.BASE_ID, 1)='R' and (NOT(LEFT(W.BASE_ID,1)='W' and W.PART_ID = @PartNum THEN W.BASE_ID WHEN W.[TYPE] = 'W' and (LEFT(W.BASE_ID, 1)='W' and W.PART_ID = @PartNum THEN W.BASE_ID END FROM WORK_ORDER W
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered
It isn't really clear what you're doing. I assume there is a table called WORK_ORDER that has a BASE_ID column where there is a prefix that can be 'W', 'R' or something else we don't know about. ('P' and 'S'?) So, for a particular part number, you are extracting all BASE_IDs with a prefix that is neither W nor R but whose TYPE is 'W' as salesOrders. Then, you are extracting all those with that particular part number with a prefix of W whose TYPE is 'W' The purchase orders are coming from another table A case statement wouldn't improve things and give the same result This would. DECLARE @PartNum nvarchar(255); Select @Partnum=N'WHOKNOWS?' DECLARE @temp2 as TABLE(salesOrder nvarchar(70), WorkOrder nvarchar(70), PurchaseOrder nvarchar(70)) INSERT INTO @temp2(salesOrder ) ( select W.BASE_ID as salesOrder from WORK_ORDER W where W.[TYPE] = 'W' and W.BASE_ID like '[^WR]%' and W.PART_ID = @PartNum ) INSERT INTO @temp2( WorkOrder ) ( select W.BASE_ID as WorkOrder from WORK_ORDER W where W.[TYPE] = 'W' and W.BASE_ID like 'W%' and W.PART_ID = @PartNum ) However, I'm not sure if this is actually what you want to achieve.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.