Mis-matched parenthesis

SQL Server Administration
1

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

2 
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
3

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.

4

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));

5

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
6

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

7

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
8

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

We're done here. Thanks a lot.