Need help combining four columns into one for each record
Need to combine WO_DAYS_LEFT, SO_DAYS_LEFT, RO_DAYS_LEFT, RW_DAYS_LEFT columns into one column named DAYS_LEFT for corresponding record.
How would I do this?
SELECT dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, dbo.BA_VIEW_SHIPPING_ORDERS.STATUS AS SM_STATUS, dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_TYPE, dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER, dbo.DEPARTMENT.DEPT_NAME, dbo.BA_VIEW_SHIPPING_ORDERS.PN, dbo.BA_VIEW_SHIPPING_ORDERS.DESCRIPTION, dbo.BA_VIEW_SHIPPING_ORDERS.SHIP_NAME AS CUSTOMER_NAME, dbo.BA_VIEW_SHIPPING_ORDERS.SHIP_VIA_CODE, CASE WHEN BA_VIEW_WO_SUMMARY.CONTRACT_TAT IS NULL THEN 21 ELSE BA_VIEW_WO_SUMMARY.CONTRACT_TAT END - dbo.BA_VIEW_WO_SUMMARY.NET_TAT AS WO_DAYS_LEFT, DATEDIFF(DAY, dbo.BA_VIEW_SHIPPING_ORDERS.DUE_DATE, GETDATE()) AS SO_DAYS_LEFT, DATEDIFF(DAY, GETDATE(), dbo.BA_VIEW_REPAIR_ORDERS.SM_ENTRY) AS RO_DAYS_LEFT, DATEDIFF(DAY, GETDATE(), dbo.BA_VIEW_REWORK_RO.SM_ENTRY) AS RW_DAYS_LEFTFROM dbo.BA_VIEW_SHIPPING_ORDERS LEFT OUTER JOIN dbo.BA_VIEW_REWORK_RO ON dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = dbo.BA_VIEW_REWORK_RO.RO LEFT OUTER JOIN dbo.BA_VIEW_REPAIR_ORDERS ON dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = dbo.BA_VIEW_REPAIR_ORDERS.RO LEFT OUTER JOIN dbo.BA_VIEW_WO_SUMMARY ON dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER LEFT OUTER JOIN dbo.DEPARTMENT ON dbo.BA_VIEW_SHIPPING_ORDERS.DPT_AUTO_KEY = dbo.DEPARTMENT.DPT_AUTO_KEYWHERE (dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER LIKE 'SHA%') AND (dbo.BA_VIEW_SHIPPING_ORDERS.STATUS NOT IN ('SHIPPED', 'CANCELLED'))
you have both Oracle and Microsoft in your tags. One is surely not good!
maximus1974
ASKER
Yes, Eric, you are correct. Should have been Oracle.
Kent Olsen
Oracle? The dbo reference suggests that it's SQL Server.
You're computing all 4 values for Days_Left. Do you want to see all 4 in one column or just add them? Either is pretty trivial with the query that you have.
if you want to add them, use the + operator...if you want to concatenate them, concatenate them (CONCAT)... You may need to ensure that you do not have a null (depending on which platform you're using, in which case you can REPLACENULL / NVL / COALESCE accordingly).
so the example provided:
...
Select N1.*,(WO_DAYS_LEFT||SO_DAYS_LEFT||RO_DAYS_LEFT||RW_DAYS_LEFT) Days_Left
...
concatenates using Oracle SQL shorthand (double pipe)...
If you wanted to add them it would be:
Select N1.*,(WO_DAYS_LEFT+SO_DAYS_LEFT+RO_DAYS_LEFT+RW_DAYS_LEFT) Days_Left
(if the fields were NOT NULL fields)...
Otherwise it might be: