SQLTeam.com | Weblogs | Forums

Mis-matched parenthesis

This is the ugly query:

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @currDate Date = GETDATE();
DECLARE @30DaysAgo Date;
Set @30DaysAgo = DateAdd(Day, -30, @currDate)

SET @xml = CAST((SELECT Shipments.ProNumber, Divisions.LookupCode AS Division, FreightBills.InternalNotes, FreightBills.ApproverComments, Orgs.Name AS [Shipper Name], Orgs.City AS [Shipper City], Orgs_1.Name AS [Cons Name], Orgs_1.City AS [Cons City], StateProvinces_1.LookupCode AS [Ship ST], StateProvinces.LookupCode AS [Cons ST], Orgs.Zip AS [Shipper Zip], Orgs_1.Zip AS [Cons Zip], Orgs_2.Name AS [Carrier Name], FreightBills.RecommendedPaymentAmount, Countries.Name AS [Ship Country], Countries_1.Name AS [Cons Country], FreightBills.CarrierNotes, Shipments.BOLNumber AS [HA BOL #], ViewAllShipmentRefs.[INTL BOL #], ViewAllShipmentRefs.[INTL Entry #], Shipments.WeightOriginal, ViewTotalFuel.RecommendedPaymentAmount AS [Carrier FSC], ViewTotalACCBilledandRecommended.AccRecommendedAmount AS CarrierACC, Left([shipments].[LastEditDT],10) AS [Date placedintoCA], Left([shipments].[actualdeliverydt],10) AS [Delv Date], Left([freightbills].[invoicedt],10) AS [Invoice Date by Carrier], Left([shipments].[shipdt],10) AS [Ship Date], ViewAllShipmentRefs.[PO #], ViewAllShipmentRefs.[Client BOL Number], ViewAllShipmentRefs.[Packing List #], FreightBills.LastEditDT
FROM (((((((((((Shipments INNER JOIN FreightBills ON Shipments.Id = FreightBills.ShipmentId) INNER JOIN Divisions ON Shipments.DivisionId = Divisions.Id) INNER JOIN Orgs ON Shipments.ShipperOrgId = Orgs.Id) INNER JOIN Orgs AS Orgs_1 ON Shipments.ConsigneeOrgId = Orgs_1.Id) INNER JOIN StateProvinces AS StateProvinces_1 ON Orgs.StateProvinceId = StateProvinces_1.Id) INNER JOIN StateProvinces ON Orgs_1.StateProvinceId = StateProvinces.Id) INNER JOIN Orgs AS Orgs_2 ON Shipments.CarrierOrgId = Orgs_2.Id) INNER JOIN Countries ON StateProvinces_1.CountryId = Countries.Id) INNER JOIN Countries AS Countries_1 ON StateProvinces.CountryId = Countries_1.Id) LEFT JOIN ViewAllShipmentRefs ON Shipments.Id = ViewAllShipmentRefs.ShipmentId) LEFT JOIN ViewTotalFuel ON FreightBills.Id = ViewTotalFuel.FreightBillId) LEFT JOIN ViewTotalACCBilledandRecommended ON FreightBills.Id = ViewTotalACCBilledandRecommended.FreightBillId))
WHERE (((FreightBills.LastEditDT)<@30DaysAgo) AND ((Shipments.ParentOrgId)=149) AND ((Shipments.Status)=1) AND ((FreightBills.Status)=2))
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));

Msg 1035, Level 15, State 10, Line 7
Incorrect syntax near 'CAST', expected 'AS'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'FreightBills'.

I have removed a few parenthesis, moving around but no help.
Can any give an assist? Thanks

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @currDate Date = GETDATE();
DECLARE @30DaysAgo Date;
Set @30DaysAgo = DateAdd(Day, -30, @currDate)

SET @xml = CAST(
           (
               SELECT Shipments.ProNumber,
                      Divisions.LookupCode AS Division,
                      FreightBills.InternalNotes,
                      FreightBills.ApproverComments,
                      Orgs.Name AS [Shipper Name],
                      Orgs.City AS [Shipper City],
                      Orgs_1.Name AS [Cons Name],
                      Orgs_1.City AS [Cons City],
                      StateProvinces_1.LookupCode AS [Ship ST],
                      StateProvinces.LookupCode AS [Cons ST],
                      Orgs.Zip AS [Shipper Zip],
                      Orgs_1.Zip AS [Cons Zip],
                      Orgs_2.Name AS [Carrier Name],
                      FreightBills.RecommendedPaymentAmount,
                      Countries.Name AS [Ship Country],
                      Countries_1.Name AS [Cons Country],
                      FreightBills.CarrierNotes,
                      Shipments.BOLNumber AS [HA BOL #],
                      ViewAllShipmentRefs.[INTL BOL #],
                      ViewAllShipmentRefs.[INTL Entry #],
                      Shipments.WeightOriginal,
                      ViewTotalFuel.RecommendedPaymentAmount AS [Carrier FSC],
                      ViewTotalACCBilledandRecommended.AccRecommendedAmount AS CarrierACC,
                      Left([shipments].[LastEditDT], 10) AS [Date placedintoCA],
                      Left([shipments].[actualdeliverydt], 10) AS [Delv Date],
                      Left([freightbills].[invoicedt], 10) AS [Invoice Date by Carrier],
                      Left([shipments].[shipdt], 10) AS [Ship Date],
                      ViewAllShipmentRefs.[PO #],
                      ViewAllShipmentRefs.[Client BOL Number],
                      ViewAllShipmentRefs.[Packing List #],
                      FreightBills.LastEditDT
               FROM Shipments
                   INNER JOIN FreightBills
                       ON Shipments.Id = FreightBills.ShipmentId
                   INNER JOIN Divisions
                       ON Shipments.DivisionId = Divisions.Id
                   INNER JOIN Orgs
                       ON Shipments.ShipperOrgId = Orgs.Id
                   INNER JOIN Orgs AS Orgs_1
                       ON Shipments.ConsigneeOrgId = Orgs_1.Id
                   INNER JOIN StateProvinces AS StateProvinces_1
                       ON Orgs.StateProvinceId = StateProvinces_1.Id
                   INNER JOIN StateProvinces
                       ON Orgs_1.StateProvinceId = StateProvinces.Id
                   INNER JOIN Orgs AS Orgs_2
                       ON Shipments.CarrierOrgId = Orgs_2.Id
                   INNER JOIN Countries
                       ON StateProvinces_1.CountryId = Countries.Id
                   INNER JOIN Countries AS Countries_1
                       ON StateProvinces.CountryId = Countries_1.Id
                   LEFT JOIN ViewAllShipmentRefs
                       ON Shipments.Id = ViewAllShipmentRefs.ShipmentId
                   LEFT JOIN ViewTotalFuel
                       ON FreightBills.Id = ViewTotalFuel.FreightBillId
                   LEFT JOIN ViewTotalACCBilledandRecommended
                       ON FreightBills.Id = ViewTotalACCBilledandRecommended.FreightBillId
               WHERE FreightBills.LastEditDT < @30DaysAgo
                     AND Shipments.ParentOrgId = 149
                     AND Shipments.Status = 1
                     AND FreightBills.Status = 2
               FOR XML PATH('tr'), ELEMENTS
           ) AS NVARCHAR(MAX));
1 Like

Thanks @robert_volk !
That's a hundred times more readable!
I had to remove the AS Division etc
Now, ViewAllShipmentRefs.[INTL BOL #] gives error Column name contains an invalid XML identifier as required by FOR XML
It's only on the columns that have embedded spaces

Do you have a workaround for those? Thanks very much.

I just used this to get around the error:
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @currDate Date = GETDATE();
DECLARE @30DaysAgo Date;
Set @30DaysAgo = DateAdd(Day, -30, @currDate)

SET @xml = CAST(
(
SELECT Shipments.ProNumber,
Divisions.LookupCode AS Division,
FreightBills.InternalNotes,
FreightBills.ApproverComments,
Orgs.Name,
Orgs.City,
Orgs_1.Name,
Orgs_1.City,
StateProvinces_1.LookupCode,
StateProvinces.LookupCode,
Orgs.Zip,
Orgs_1.Zip,
Orgs_2.Name,
FreightBills.RecommendedPaymentAmount,
Countries.Name,
Countries_1.Name,
FreightBills.CarrierNotes,
Shipments.BOLNumber,
ViewAllShipmentRefs.[INTL BOL #] as IntlBOL,
ViewAllShipmentRefs.[INTL Entry #] as IntlEntry,
Shipments.WeightOriginal,
ViewTotalFuel.RecommendedPaymentAmount,
ViewTotalACCBilledandRecommended.AccRecommendedAmount,
Left([shipments].[LastEditDT], 10),
Left([shipments].[actualdeliverydt], 10),
Left([freightbills].[invoicedt], 10),
Left([shipments].[shipdt], 10),
ViewAllShipmentRefs.[PO #] asPONbr,
ViewAllShipmentRefs.[Client BOL Number] as ClientBOLNbr,
ViewAllShipmentRefs.[Packing List #] as PackingListNbr,
FreightBills.LastEditDT
FROM Shipments
INNER JOIN FreightBills
ON Shipments.Id = FreightBills.ShipmentId
INNER JOIN Divisions
ON Shipments.DivisionId = Divisions.Id
INNER JOIN Orgs
ON Shipments.ShipperOrgId = Orgs.Id
INNER JOIN Orgs AS Orgs_1
ON Shipments.ConsigneeOrgId = Orgs_1.Id
INNER JOIN StateProvinces AS StateProvinces_1
ON Orgs.StateProvinceId = StateProvinces_1.Id
INNER JOIN StateProvinces
ON Orgs_1.StateProvinceId = StateProvinces.Id
INNER JOIN Orgs AS Orgs_2
ON Shipments.CarrierOrgId = Orgs_2.Id
INNER JOIN Countries
ON StateProvinces_1.CountryId = Countries.Id
INNER JOIN Countries AS Countries_1
ON StateProvinces.CountryId = Countries_1.Id
LEFT JOIN ViewAllShipmentRefs
ON Shipments.Id = ViewAllShipmentRefs.ShipmentId
LEFT JOIN ViewTotalFuel
ON FreightBills.Id = ViewTotalFuel.FreightBillId
LEFT JOIN ViewTotalACCBilledandRecommended
ON FreightBills.Id = ViewTotalACCBilledandRecommended.FreightBillId
WHERE FreightBills.LastEditDT < @30DaysAgo
AND Shipments.ParentOrgId = 149
AND Shipments.Status = 1
AND FreightBills.Status = 2
FOR XML PATH('tr'), ELEMENTS
) AS NVARCHAR(MAX));

Any non-alphanumeric character in the names, like spaces, punctuation, "#", and @ signs will get HTML/XML encoded when using FOR XML. Aliasing them with alphnumeric and underscore only should resolve it.

1 Like

I hit another issue, @robert_volk. I'll need to generate an HTML report so I need to use
ViewAllShipmentRefs.[INTL BOL #] as IntlBOL AS 'td', '',

When I add AS 'td', '' I get error Incorrect syntax near the keyword 'AS'.

Any thoughts? Thanks

You're using AS twice, that's not allowed. If you're trying to generate an HTML table with TD elements via FOR XML, all column need to be aliased as TD.

1 Like

Yep ViewAllShipmentRefs.[INTL BOL #] AS 'td', '' works.I thought I still had to also specify As IntlBOL.

We're done here. Thanks a lot.