Link to home
Create AccountLog in
Avatar of maximus1974
maximus1974

asked on

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_LEFT
FROM            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_KEY
WHERE        (dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER LIKE 'SHA%') AND (dbo.BA_VIEW_SHIPPING_ORDERS.STATUS NOT IN ('SHIPPED', 'CANCELLED'))

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Vishal Jaiswal
Vishal Jaiswal
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Éric Moreau
you have both Oracle and Microsoft in your tags. One is surely not good!
Avatar of maximus1974
maximus1974

ASKER

Yes, Eric, you are correct. Should have been Oracle.
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:

Select N1.*,(NVL(WO_DAYS_LEFT,0)+NVL(SO_DAYS_LEFT,0)+NVL(RO_DAYS_LEFT,0)+NVL(RW_DAYS_LEFT,0)) Days_Left

...
etc.