TBSupport
asked on
T-SQL Syntax Errors
Hello:
Below is my T-SQL query. Please let me know why SQL gives me the following errors:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'ORDER'.
Not only that, I'm getting duplicate data. There are supposed to be six line items and each tracking number field is supposed to have three concatenated tracking numbers. Instead of giving me six records, it's giving me 54. Somehow, it's multiplying 6*9 to get that 54, I guess.
select DISINTCT
DTZ..SOP30300.ITEMNMBR AS [Item],
DTZ..IV00101.ITEMDESC as [ItemDesc],
DTZ..SOP30300.LOCNCODE AS [Site], 0 as [QtyAdjusted], 0 as [StockCountVariance], 0 as [QtyTransferred], 0 as [QtyRecd], 0 as [QtyReturned],
vShip..SHP_ContainerItem.Q tyPacked * -1 as [QtySold],
DTZ..SOP30300.FUFILDAT as [Date],
stuff( (SELECT ','+Tracking_Number
FROM DTZ..SOP10107 p2
WHERE p2.SOPNUMBE = p1.SOPNUMBE
ORDER BY Tracking_Number
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,1,'')
as [TrackingNo],
vShip..SHP_Shipment.BOLNum ber as [BOL],
DTZ..SOP30200.CSTPONBR as [VPAorDocNo],
DTZ..IV00102.QTYONHND AS [QtyOnHand],
cast(DTZ..SY03900.TXTFIELD as varchar(max)) AS [Comments]
--, --'' as [TransferFromReason], --'' as [TransferToReason],
--COALESCE (DTZ..SOP10106.CMMTTEXT, '') as [SalesComment]
from DTZ..SOP30300
INNER JOIN DTZ..SOP30200 on DTZ..SOP30300.SOPTYPE = DTZ..SOP30200.SOPTYPE and DTZ..SOP30300.SOPNUMBE = DTZ..SOP30200.SOPNUMBE
INNER JOIN DTZ..SOP10107 p1 ON DTZ..SOP30200.SOPNUMBE = p1.SOPNUMBE AND DTZ..SOP30200.SOPTYPE = p1.SOPTYPE
INNER JOIN DTZ..IV00101 ON DTZ..SOP30300.ITEMNMBR = DTZ..IV00101.ITEMNMBR
INNER JOIN DTZ..IV00102 ON DTZ..IV00101.ITEMNMBR = DTZ..IV00102.ITEMNMBR
LEFT OUTER JOIN DTZ..SOP10106 on DTZ..SOP30200.SOPTYPE = DTZ..SOP10106.SOPTYPE and DTZ..SOP30200.SOPNUMBE = DTZ..SOP10106.SOPNUMBE
LEFT OUTER JOIN DTZ..SY03900 ON DTZ..SOP30200.NOTEINDX = SY03900.NOTEINDX
INNER JOIN vShip..SHP_ContainerItem on
DTZ..SOP30300.ITEMNMBR = vShip..SHP_ContainerItem.C oreItem and DTZ..SOP30300.SOPNUMBE = vShip..SHP_ContainerItem.D ocumentNum ber
INNER JOIN vShip..SHP_Container on vShip..SHP_ContainerItem.S HP_Contain er_ID = vShip..SHP_Container.SHP_C ontainer_I D
INNER JOIN vShip..SHP_Shipment on
vShip..SHP_Container.SHP_S hipment_ID = vShip..SHP_Shipment.Shipme nt_ID and
vShip..SHP_Container.Docum entNumber = vShip..SHP_Shipment.Freigh tAssignedt oDocumentN umber
WHERE (DTZ..SOP30300.SOPTYPE = '2' AND
DTZ..SOP30300.QTYPRINV > 0 AND
DTZ..SOP30200.CSTPONBR LIKE 'VPA%' and
DTZ..IV00101.ITMGEDSC LIKE '%OCE%' AND
DTZ..IV00102.LOCNCODE = '' and
DTZ..SOP30200.CSTPONBR = 'VPA59702')
TBSupport
Below is my T-SQL query. Please let me know why SQL gives me the following errors:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'ORDER'.
Not only that, I'm getting duplicate data. There are supposed to be six line items and each tracking number field is supposed to have three concatenated tracking numbers. Instead of giving me six records, it's giving me 54. Somehow, it's multiplying 6*9 to get that 54, I guess.
select DISINTCT
DTZ..SOP30300.ITEMNMBR AS [Item],
DTZ..IV00101.ITEMDESC as [ItemDesc],
DTZ..SOP30300.LOCNCODE AS [Site], 0 as [QtyAdjusted], 0 as [StockCountVariance], 0 as [QtyTransferred], 0 as [QtyRecd], 0 as [QtyReturned],
vShip..SHP_ContainerItem.Q
DTZ..SOP30300.FUFILDAT as [Date],
stuff( (SELECT ','+Tracking_Number
FROM DTZ..SOP10107 p2
WHERE p2.SOPNUMBE = p1.SOPNUMBE
ORDER BY Tracking_Number
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,1,'')
as [TrackingNo],
vShip..SHP_Shipment.BOLNum
DTZ..SOP30200.CSTPONBR as [VPAorDocNo],
DTZ..IV00102.QTYONHND AS [QtyOnHand],
cast(DTZ..SY03900.TXTFIELD
--, --'' as [TransferFromReason], --'' as [TransferToReason],
--COALESCE (DTZ..SOP10106.CMMTTEXT, '') as [SalesComment]
from DTZ..SOP30300
INNER JOIN DTZ..SOP30200 on DTZ..SOP30300.SOPTYPE = DTZ..SOP30200.SOPTYPE and DTZ..SOP30300.SOPNUMBE = DTZ..SOP30200.SOPNUMBE
INNER JOIN DTZ..SOP10107 p1 ON DTZ..SOP30200.SOPNUMBE = p1.SOPNUMBE AND DTZ..SOP30200.SOPTYPE = p1.SOPTYPE
INNER JOIN DTZ..IV00101 ON DTZ..SOP30300.ITEMNMBR = DTZ..IV00101.ITEMNMBR
INNER JOIN DTZ..IV00102 ON DTZ..IV00101.ITEMNMBR = DTZ..IV00102.ITEMNMBR
LEFT OUTER JOIN DTZ..SOP10106 on DTZ..SOP30200.SOPTYPE = DTZ..SOP10106.SOPTYPE and DTZ..SOP30200.SOPNUMBE = DTZ..SOP10106.SOPNUMBE
LEFT OUTER JOIN DTZ..SY03900 ON DTZ..SOP30200.NOTEINDX = SY03900.NOTEINDX
INNER JOIN vShip..SHP_ContainerItem on
DTZ..SOP30300.ITEMNMBR = vShip..SHP_ContainerItem.C
INNER JOIN vShip..SHP_Container on vShip..SHP_ContainerItem.S
INNER JOIN vShip..SHP_Shipment on
vShip..SHP_Container.SHP_S
vShip..SHP_Container.Docum
WHERE (DTZ..SOP30300.SOPTYPE = '2' AND
DTZ..SOP30300.QTYPRINV > 0 AND
DTZ..SOP30200.CSTPONBR LIKE 'VPA%' and
DTZ..IV00101.ITMGEDSC LIKE '%OCE%' AND
DTZ..IV00102.LOCNCODE = '' and
DTZ..SOP30200.CSTPONBR = 'VPA59702')
TBSupport
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:) .. I cannot get sleect right >80% of the time
>>I'm getting duplicate data. ... Somehow, it's multiplying 6*9 to get that 54
This indicates there's a join problem, which you suppress by 'distinct' - but do not solve
It's probably related to the introduction of stuff(...) and it may be better to perform that calculation within the from clause as a subquery using 'group by'. Before going to that however in the existing query I think you may now have a redundant join, indicated below
>>I'm getting duplicate data. ... Somehow, it's multiplying 6*9 to get that 54
This indicates there's a join problem, which you suppress by 'distinct' - but do not solve
It's probably related to the introduction of stuff(...) and it may be better to perform that calculation within the from clause as a subquery using 'group by'. Before going to that however in the existing query I think you may now have a redundant join, indicated below
SELECT DISTINCT -- wish that distinct wasn't required
DTZ..SOP30300.ITEMNMBR AS [Item]
, DTZ..IV00101.ITEMDESC AS [ItemDesc]
, DTZ..SOP30300.LOCNCODE AS [Site]
, 0 AS [QtyAdjusted]
, 0 AS [StockCountVariance]
, 0 AS [QtyTransferred]
, 0 AS [QtyRecd]
, 0 AS [QtyReturned]
, vShip..SHP_ContainerItem.QtyPacked * - 1 AS [QtySold]
, DTZ..SOP30300.FUFILDAT AS [Date]
, stuff((
SELECT ',' + Tracking_Number
FROM DTZ..SOP10107 p2
WHERE p2.SOPNUMBE = DTZ..SOP30300.SOPNUMBE -- p2.SOPNUMBE = p1.SOPNUMBE
ORDER BY Tracking_Number
FOR XML PATH('')
, TYPE
).value('.', 'varchar(max)'), 1, 1, '') AS [TrackingNo]
, vShip..SHP_Shipment.BOLNumber AS [BOL]
, DTZ..SOP30200.CSTPONBR AS [VPAorDocNo]
, DTZ..IV00102.QTYONHND AS [QtyOnHand]
, cast(DTZ..SY03900.TXTFIELD AS VARCHAR(max)) AS [Comments]
--, --'' as [TransferFromReason], --'' as [TransferToReason],
--COALESCE (DTZ..SOP10106.CMMTTEXT, '') as [SalesComment]
FROM DTZ..SOP30300
INNER JOIN DTZ..SOP30200 ON DTZ..SOP30300.SOPTYPE = DTZ..SOP30200.SOPTYPE
AND DTZ..SOP30300.SOPNUMBE = DTZ..SOP30200.SOPNUMBE
--INNER JOIN DTZ..SOP10107 p1 ON DTZ..SOP30200.SOPNUMBE = p1.SOPNUMBE
-- AND DTZ..SOP30200.SOPTYPE = p1.SOPTYPE
INNER JOIN DTZ..IV00101 ON DTZ..SOP30300.ITEMNMBR = DTZ..IV00101.ITEMNMBR
INNER JOIN DTZ..IV00102 ON DTZ..IV00101.ITEMNMBR = DTZ..IV00102.ITEMNMBR
LEFT JOIN DTZ..SOP10106 ON DTZ..SOP30200.SOPTYPE = DTZ..SOP10106.SOPTYPE
AND DTZ..SOP30200.SOPNUMBE = DTZ..SOP10106.SOPNUMBE
LEFT JOIN DTZ..SY03900 ON DTZ..SOP30200.NOTEINDX = SY03900.NOTEINDX
INNER JOIN vShip..SHP_ContainerItem ON DTZ..SOP30300.ITEMNMBR = vShip..SHP_ContainerItem.CoreItem
AND DTZ..SOP30300.SOPNUMBE = vShip..SHP_ContainerItem.DocumentNumber
INNER JOIN vShip..SHP_Container ON vShip..SHP_ContainerItem.SHP_Container_ID = vShip..SHP_Container.SHP_Container_ID
INNER JOIN vShip..SHP_Shipment ON vShip..SHP_Container.SHP_Shipment_ID = vShip..SHP_Shipment.Shipment_ID
AND vShip..SHP_Container.DocumentNumber = vShip..SHP_Shipment.FreightAssignedtoDocumentNumber
WHERE (
DTZ..SOP30300.SOPTYPE = '2'
AND DTZ..SOP30300.QTYPRINV > 0
AND DTZ..SOP30200.CSTPONBR LIKE 'VPA%'
AND DTZ..IV00101.ITMGEDSC LIKE '%OCE%'
AND DTZ..IV00102.LOCNCODE = ''
AND DTZ..SOP30200.CSTPONBR = 'VPA59702'
)
ASKER
TBSupport