Solved

T-SQL Syntax Errors

Posted on 2013-06-13
3
466 Views
Last Modified: 2013-06-13
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.QtyPacked * -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.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 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.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')

TBSupport
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Accepted Solution

by:
Simone B earned 500 total points
ID: 39245643
You have a typo

Change DISINTCT to DISTINCT
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39245648
That was it!  Thanks, Buttercup1!!!!!!!!!!!

TBSupport
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39246468
:) .. 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
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'
        )

Open in new window

0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help in debugging a UDF results 7 72
point in time restore in SQL server 26 54
SQL Select Query help 1 51
I am new to using JSON in SQL Server 2 58
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question