Need help concatenating two fields in SQL Server

Need help concatenating these two fields: dbo.BA_VIEW_WO_FAULTS.NOTES, dbo.BA_VIEW_WO_CORRECTIVE_ACTIONS.NOTES in this statement:
SELECT        dbo.WO_OPERATION.COMPANY_REF_NUMBER AS CUSTPONUM, dbo.BA_VIEW_SHIPPING_ORDERS.TRACKING_NUMBER AS AIRWAYB, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER AS DELYNUMBER, dbo.WO_OPERATION.AIRCRAFT_FLAG AS ACTYPE, 
                         dbo.BA_VIEW_REPAIR_ORDERS.RO AS POSUBCONNUM, dbo.WO_OPERATION.NOTES AS [REMOVAL REASONS], dbo.WO_OPERATION.TEAR_DOWN, 
                         dbo.BA_VIEW_WO_QUOTE.WQ_NUMBER AS QUOTE#, dbo.BA_VIEW_WO_QUOTE.APPROVED_DATE AS [CUSTAPPROVE DATE], 
                         dbo.BA_VIEW_WO_FAULTS.NOTES AS FAULT_NOTES, dbo.BA_VIEW_WO_CORRECTIVE_ACTIONS.NOTES AS CORRECTIVE_NOTES, 
                         dbo.MANUFACTURER.MFG_CODE, dbo.BA_VIEW_SHIPPING_ORDERS.SHIP_DATE, dbo.BA_VIEW_SHIPPING_ORDERS.STATUS, 
                         dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER, dbo.BA_VIEW_WO_SUMMARY.DEPT_NAME, dbo.BA_VIEW_WO_SUMMARY.CUSTOMER_NAME, 
                         dbo.BA_VIEW_WO_SUMMARY.PN, dbo.BA_VIEW_WO_SUMMARY.DESCRIPTION, dbo.BA_VIEW_WO_SUMMARY.SERIAL_NUMBER, 
                         dbo.BA_VIEW_WO_SUMMARY.CONTRACT_NUMBER, dbo.BA_VIEW_WO_SUMMARY.WORK_PERFORMED, dbo.BA_VIEW_WO_SUMMARY.RECEIVED_DATE, 
                         dbo.BA_VIEW_WO_SUMMARY.SHIP_DATE AS Expr5, dbo.BA_VIEW_WO_SUMMARY.INVC_DATE, dbo.BA_VIEW_WO_SUMMARY.TAT_START_DATE, 
                         dbo.BA_VIEW_WO_SUMMARY.TAT_STOP_DATE, dbo.BA_VIEW_WO_SUMMARY.SCHEDULED_REMOVAL, dbo.BA_VIEW_WO_SUMMARY.WARRANTY_GRANTED, 
                         dbo.BA_VIEW_WO_SUMMARY.WARRANTY_COMMENTS, dbo.WO_OPERATION.ACT_PARTS_COST, dbo.WO_OPERATION.ACT_LABOR_COST
FROM            dbo.PARTS_MASTER LEFT OUTER JOIN
                         dbo.MANUFACTURER ON dbo.PARTS_MASTER.MFG_AUTO_KEY = dbo.MANUFACTURER.MFG_AUTO_KEY RIGHT OUTER JOIN
                         dbo.BA_VIEW_WO_QUOTE RIGHT OUTER JOIN
                         dbo.BA_VIEW_REPAIR_ORDERS RIGHT OUTER JOIN
                         dbo.BA_VIEW_WO_FAULTS RIGHT OUTER JOIN
                         dbo.BA_VIEW_WO_SUMMARY LEFT OUTER JOIN
                         dbo.BA_VIEW_WO_CORRECTIVE_ACTIONS ON dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER = dbo.BA_VIEW_WO_CORRECTIVE_ACTIONS.WO_NUMBER ON 
                         dbo.BA_VIEW_WO_FAULTS.WO_NUMBER = dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER ON 
                         dbo.BA_VIEW_REPAIR_ORDERS.WO_NUMBER = dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER LEFT OUTER JOIN
                         dbo.BA_VIEW_SHIPPING_ORDERS ON dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER = dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER ON 
                         dbo.BA_VIEW_WO_QUOTE.WO_NUMBER = dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER LEFT OUTER JOIN
                         dbo.WO_OPERATION ON dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER = dbo.WO_OPERATION.SI_NUMBER ON 
                         dbo.PARTS_MASTER.PNM_AUTO_KEY = dbo.WO_OPERATION.PNM_AUTO_KEY

Open in new window

maximus1974Asked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
Use table alias names, order your JOIN clauses and for concatenating it depends on nullability of your columns:

SELECT WOO.COMPANY_REF_NUMBER AS CUSTPONUM ,
       SO.TRACKING_NUMBER AS AIRWAYB ,
       SO.SM_NUMBER AS DELYNUMBER ,
       WOO.AIRCRAFT_FLAG AS ACTYPE ,
       RO.RO AS POSUBCONNUM ,
       WOO.NOTES AS [REMOVAL REASONS] ,
       WOO.TEAR_DOWN ,
       WOQ.WQ_NUMBER AS QUOTE# ,
       WOQ.APPROVED_DATE AS [CUSTAPPROVE DATE] ,
       WOF.NOTES AS FAULT_NOTES ,
       CA.NOTES AS CORRECTIVE_NOTES ,
       M.MFG_CODE ,
       SO.SHIP_DATE ,
       SO.STATUS ,
       WOS.WO_NUMBER ,
       WOS.DEPT_NAME ,
       WOS.CUSTOMER_NAME ,
       WOS.PN ,
       WOS.DESCRIPTION ,
       WOS.SERIAL_NUMBER ,
       WOS.CONTRACT_NUMBER ,
       WOS.WORK_PERFORMED ,
       WOS.RECEIVED_DATE ,
       WOS.SHIP_DATE AS Expr5 ,
       WOS.INVC_DATE ,
       WOS.TAT_START_DATE ,
       WOS.TAT_STOP_DATE ,
       WOS.SCHEDULED_REMOVAL ,
       WOS.WARRANTY_GRANTED ,
       WOS.WARRANTY_COMMENTS ,
       WOO.ACT_PARTS_COST ,
       WOO.ACT_LABOR_COST ,
       NULLIF(ISNULL(WOF.NOTES, '') + ISNULL(CA.NOTES, ''), '') AS ConcatNotes
FROM   dbo.PARTS_MASTER PM
       LEFT OUTER JOIN dbo.MANUFACTURER M ON PM.MFG_AUTO_KEY = M.MFG_AUTO_KEY
       LEFT OUTER JOIN dbo.WO_OPERATION WOO ON PM.PNM_AUTO_KEY = WOO.PNM_AUTO_KEY
       RIGHT OUTER JOIN dbo.BA_VIEW_WO_SUMMARY WOS ON WOS.WO_NUMBER = WOO.SI_NUMBER
       LEFT OUTER JOIN dbo.BA_VIEW_WO_CORRECTIVE_ACTIONS CA ON WOS.WO_NUMBER = CA.WO_NUMBER
       RIGHT OUTER JOIN dbo.BA_VIEW_WO_FAULTS WOF ON WOF.WO_NUMBER = WOS.WO_NUMBER
       LEFT OUTER JOIN dbo.BA_VIEW_SHIPPING_ORDERS SO ON WOS.WO_NUMBER = SO.ORDER_NUMBER
       RIGHT OUTER JOIN dbo.BA_VIEW_REPAIR_ORDERS RO ON RO.WO_NUMBER = WOS.WO_NUMBER
       RIGHT OUTER JOIN dbo.BA_VIEW_WO_QUOTE WOQ ON WOQ.WO_NUMBER = WOS.WO_NUMBER;

Open in new window

or

SELECT WOO.COMPANY_REF_NUMBER AS CUSTPONUM ,
       SO.TRACKING_NUMBER AS AIRWAYB ,
       SO.SM_NUMBER AS DELYNUMBER ,
       WOO.AIRCRAFT_FLAG AS ACTYPE ,
       RO.RO AS POSUBCONNUM ,
       WOO.NOTES AS [REMOVAL REASONS] ,
       WOO.TEAR_DOWN ,
       WOQ.WQ_NUMBER AS QUOTE# ,
       WOQ.APPROVED_DATE AS [CUSTAPPROVE DATE] ,
       WOF.NOTES AS FAULT_NOTES ,
       CA.NOTES AS CORRECTIVE_NOTES ,
       M.MFG_CODE ,
       SO.SHIP_DATE ,
       SO.STATUS ,
       WOS.WO_NUMBER ,
       WOS.DEPT_NAME ,
       WOS.CUSTOMER_NAME ,
       WOS.PN ,
       WOS.DESCRIPTION ,
       WOS.SERIAL_NUMBER ,
       WOS.CONTRACT_NUMBER ,
       WOS.WORK_PERFORMED ,
       WOS.RECEIVED_DATE ,
       WOS.SHIP_DATE AS Expr5 ,
       WOS.INVC_DATE ,
       WOS.TAT_START_DATE ,
       WOS.TAT_STOP_DATE ,
       WOS.SCHEDULED_REMOVAL ,
       WOS.WARRANTY_GRANTED ,
       WOS.WARRANTY_COMMENTS ,
       WOO.ACT_PARTS_COST ,
       WOO.ACT_LABOR_COST ,
       NULLIF(CONCAT(WOF.NOTES, CA.NOTES, '') AS ConcatNotes
FROM   dbo.PARTS_MASTER PM
       LEFT OUTER JOIN dbo.MANUFACTURER M ON PM.MFG_AUTO_KEY = M.MFG_AUTO_KEY
       LEFT OUTER JOIN dbo.WO_OPERATION WOO ON PM.PNM_AUTO_KEY = WOO.PNM_AUTO_KEY
       RIGHT OUTER JOIN dbo.BA_VIEW_WO_SUMMARY WOS ON WOS.WO_NUMBER = WOO.SI_NUMBER
       LEFT OUTER JOIN dbo.BA_VIEW_WO_CORRECTIVE_ACTIONS CA ON WOS.WO_NUMBER = CA.WO_NUMBER
       RIGHT OUTER JOIN dbo.BA_VIEW_WO_FAULTS WOF ON WOF.WO_NUMBER = WOS.WO_NUMBER
       LEFT OUTER JOIN dbo.BA_VIEW_SHIPPING_ORDERS SO ON WOS.WO_NUMBER = SO.ORDER_NUMBER
       RIGHT OUTER JOIN dbo.BA_VIEW_REPAIR_ORDERS RO ON RO.WO_NUMBER = WOS.WO_NUMBER
       RIGHT OUTER JOIN dbo.BA_VIEW_WO_QUOTE WOQ ON WOQ.WO_NUMBER = WOS.WO_NUMBER;

Open in new window

0
 
OMC2000Commented:
SELECT        dbo.WO_OPERATION.COMPANY_REF_NUMBER AS CUSTPONUM, dbo.BA_VIEW_SHIPPING_ORDERS.TRACKING_NUMBER AS AIRWAYB, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER AS DELYNUMBER, dbo.WO_OPERATION.AIRCRAFT_FLAG AS ACTYPE, 
                         dbo.BA_VIEW_REPAIR_ORDERS.RO AS POSUBCONNUM, dbo.WO_OPERATION.NOTES AS [REMOVAL REASONS], dbo.WO_OPERATION.TEAR_DOWN, 
                         dbo.BA_VIEW_WO_QUOTE.WQ_NUMBER AS QUOTE#, dbo.BA_VIEW_WO_QUOTE.APPROVED_DATE AS [CUSTAPPROVE DATE], 
                         dbo.BA_VIEW_WO_FAULTS.NOTES AS FAULT_NOTES + dbo.BA_VIEW_WO_CORRECTIVE_ACTIONS.NOTES AS CORRECTIVE_NOTES, 
                         dbo.MANUFACTURER.MFG_CODE, dbo.BA_VIEW_SHIPPING_ORDERS.SHIP_DATE, dbo.BA_VIEW_SHIPPING_ORDERS.STATUS, 
                         dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER, dbo.BA_VIEW_WO_SUMMARY.DEPT_NAME, dbo.BA_VIEW_WO_SUMMARY.CUSTOMER_NAME, 
                         dbo.BA_VIEW_WO_SUMMARY.PN, dbo.BA_VIEW_WO_SUMMARY.DESCRIPTION, dbo.BA_VIEW_WO_SUMMARY.SERIAL_NUMBER, 
                         dbo.BA_VIEW_WO_SUMMARY.CONTRACT_NUMBER, dbo.BA_VIEW_WO_SUMMARY.WORK_PERFORMED, dbo.BA_VIEW_WO_SUMMARY.RECEIVED_DATE, 
                         dbo.BA_VIEW_WO_SUMMARY.SHIP_DATE AS Expr5, dbo.BA_VIEW_WO_SUMMARY.INVC_DATE, dbo.BA_VIEW_WO_SUMMARY.TAT_START_DATE, 
                         dbo.BA_VIEW_WO_SUMMARY.TAT_STOP_DATE, dbo.BA_VIEW_WO_SUMMARY.SCHEDULED_REMOVAL, dbo.BA_VIEW_WO_SUMMARY.WARRANTY_GRANTED, 
                         dbo.BA_VIEW_WO_SUMMARY.WARRANTY_COMMENTS, dbo.WO_OPERATION.ACT_PARTS_COST, dbo.WO_OPERATION.ACT_LABOR_COST
FROM            dbo.PARTS_MASTER LEFT OUTER JOIN
                         dbo.MANUFACTURER ON dbo.PARTS_MASTER.MFG_AUTO_KEY = dbo.MANUFACTURER.MFG_AUTO_KEY RIGHT OUTER JOIN
                         dbo.BA_VIEW_WO_QUOTE RIGHT OUTER JOIN
                         dbo.BA_VIEW_REPAIR_ORDERS RIGHT OUTER JOIN
                         dbo.BA_VIEW_WO_FAULTS RIGHT OUTER JOIN
                         dbo.BA_VIEW_WO_SUMMARY LEFT OUTER JOIN
                         dbo.BA_VIEW_WO_CORRECTIVE_ACTIONS ON dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER = dbo.BA_VIEW_WO_CORRECTIVE_ACTIONS.WO_NUMBER ON 
                         dbo.BA_VIEW_WO_FAULTS.WO_NUMBER = dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER ON 
                         dbo.BA_VIEW_REPAIR_ORDERS.WO_NUMBER = dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER LEFT OUTER JOIN
                         dbo.BA_VIEW_SHIPPING_ORDERS ON dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER = dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER ON 
                         dbo.BA_VIEW_WO_QUOTE.WO_NUMBER = dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER LEFT OUTER JOIN
                         dbo.WO_OPERATION ON dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER = dbo.WO_OPERATION.SI_NUMBER ON 
                         dbo.PARTS_MASTER.PNM_AUTO_KEY = dbo.WO_OPERATION.PNM_AUTO_KEY

Open in new window

0
 
maximus1974Author Commented:
Thank you OMC2000. I am now receiving this error: Msg 8117, Level 16, State 1, Line 5
Operand data type ntext is invalid for add operator.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
ste5anSenior DeveloperCommented:
What SQL Server version? NTEXT is deprecated. Use NVARCHAR(MAX) instead.
0
 
OMC2000Commented:
for NTEXT you could use

CAST( (CAST(dbo.BA_VIEW_WO_FAULTS.NOTES AS FAULT_NOTES AS NVARCHAR(MAX)) + CAST(dbo.BA_VIEW_WO_CORRECTIVE_ACTIONS.NOTES AS NVARCHAR(MAX))) AS NTEXT) as CORRECTIVE_NOTES
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
If you are on SQL2012 you can use CONCAT()
CREATE TABLE #emp (emp_name nvarchar(200) , emp_Note1 ntext, emp_Note2 ntext)
GO
  
INSERT INTO #emp VALUES( 'Name', 'Note1', 'Note2' )
GO

SELECT emp_name, CONCAT( emp_Note1, ' ',emp_Note2 ) AS emp_notes  
FROM #emp 

Open in new window

If prior to 2012, then
SELECT emp_name, cast(emp_Note1 as nvarchar(max)) + ' ' + cast(emp_Note2 as nvarchar(max)) AS emp_notes  
FROM #emp  

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.