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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ste5anSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

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 WillsTopic 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.