asked on

SSRS--The app is wrong--there is no incorrect syntax

The wonderful SQL Reporting Services tool claims that that there is a syntax error near a comma. I defy anyone to find such an error in the code below. When I run this in SQL Studio, it bring, in data 1000% perfectly.

When I run this in SSRS Query Builder, it bring in data 1000% perfectly.

When I try to generate the report, it says incorrect syntax near a comma.

I think a man behind a curtain is messing with me.

Where in the name of Sam Hill is the syntax error that it refers to?

TBSupport

select
WO010032.MANUFACTUREORDER_I,
CAST((year(WO010032.ENDDATE)) AS varchar(4)) + '-' + RIGHT('0' + CAST(datepart(wk,WO010032.ENDDATE) AS varchar(2)), 2) 'YearWeek',
WO010032.DSCRIPTN 'MO Description', COALESCE(SY03900.TXTFIELD, '') 'MO Notes',
WO010032.ITEMNMBR 'BOM', COALESCE(SOP10200.UNITPRCE*WO010032.ENDQTY_I, '0') 'Sell',
WO010032.ROUTINGNAME_I, WO010032.ENDQTY_I, WR010130.WCID_I, WR010130.RTSEQNUM_I 'File No', WR010130.SETUPTIME_I 'SU', WR010130.RUNTIME_I 'Run', WR010130.SETUPTIME_I + WR010130.RUNTIME_I 'Total',
WR010130.LABORTIME_I 'Logged',
CASE WHEN ((WR010130.SETUPTIME_I + WR010130.RUNTIME_I) - WR010130.LABORTIME_I) < 0 THEN 0 ELSE (WR010130.SETUPTIME_I + WR010130.RUNTIME_I) - WR010130.LABORTIME_I END 'EstRem',
WO010032.ITEMNMBR, IV00101.ITEMDESC, COALESCE(PK010033.REQDATE, '') 'Mtl Due',
WR010130.SCHEDULEFINISHDATE_I 'Due Date', WO010032.ENDDATE 'MO Due', COALESCE(SOP10100.ACTLSHIP, '') 'Ship Date',
COALESCE(SOP10100.CUSTNAME, '') 'Customer'
from WR010130
LEFT OUTER JOIN WO010032 ON WR010130.MANUFACTUREORDER_I = WO010032.MANUFACTUREORDER_I
LEFT OUTER JOIN IV00101 ON WO010032.ITEMNMBR = IV00101.ITEMNMBR
LEFT OUTER JOIN IS010001 ON WO010032.MANUFACTUREORDER_I = IS010001.MANUFACTUREORDER_I AND WO010032.ITEMNMBR = IS010001.ITEMNMBR
LEFT OUTER JOIN SOP10100 ON IS010001.SOPNUMBE = SOP10100.SOPNUMBE
LEFT OUTER JOIN SOP10200 ON SOP10100.SOPNUMBE = SOP10200.SOPNUMBE AND SOP10100.SOPTYPE = SOP10200.SOPTYPE
LEFT OUTER JOIN SY03900 ON WO010032.NOTEINDX = SY03900.NOTEINDX
LEFT OUTER JOIN PK010033 ON WO010032.MANUFACTUREORDER_I = PK010033.MANUFACTUREORDER_I and WO010032.ITEMNMBR = PK010033.ITEMNMBR
where WO010032.MANUFACTUREORDERST_I = '3'
and WR010130.MANUFACTUREORDER_I = @MO2
and WR010130.RTSEQNUM_I = @RoutingSequenceNo2

ASKER CERTIFIED SOLUTION

edtechdba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

well I sure as heck can't see a missing comma (one of the reasons why I like "comma first" layout by the way) and the only thing I could suggest woud be to use [Column Alias] instead of 'Column Alias' - but it really should work with either. Could it be something in the parameter values? (also unlikely)

SELECT WO010032.MANUFACTUREORDER_I
     , CAST((year(WO010032.ENDDATE)) AS VARCHAR(4)) + '-' 
         + RIGHT('0' + CAST(datepart(wk, WO010032.ENDDATE) AS VARCHAR(2)), 2)  'YearWeek'
     , WO010032.DSCRIPTN                                              'MO Description'
     , COALESCE(SY03900.TXTFIELD, '')                                 'MO Notes'
     , WO010032.ITEMNMBR                                              'BOM'
     , COALESCE(SOP10200.UNITPRCE * WO010032.ENDQTY_I, '0')           'Sell'
     , WO010032.ROUTINGNAME_I
     , WO010032.ENDQTY_I
     , WR010130.WCID_I
     , WR010130.RTSEQNUM_I                                            'File No'
     , WR010130.SETUPTIME_I                                           'SU'
     , WR010130.RUNTIME_I                                             'Run'
     , WR010130.SETUPTIME_I + WR010130.RUNTIME_I                      'Total'
     , WR010130.LABORTIME_I                                           'Logged'
     , CASE 
          WHEN ((WR010130.SETUPTIME_I + WR010130.RUNTIME_I) - WR010130.LABORTIME_I) < 0
               THEN 0
          ELSE (WR010130.SETUPTIME_I + WR010130.RUNTIME_I) - WR010130.LABORTIME_I
          END                                                         'EstRem'
     , WO010032.ITEMNMBR
     , IV00101.ITEMDESC
     , COALESCE(PK010033.REQDATE, '')                                 'Mtl Due'
     , WR010130.SCHEDULEFINISHDATE_I                                  'Due Date'
     , WO010032.ENDDATE                                               'MO Due'
     , COALESCE(SOP10100.ACTLSHIP, '')                                'Ship Date'
     , COALESCE(SOP10100.CUSTNAME, '')                                'Customer'
FROM WR010130
LEFT JOIN WO010032 ON WR010130.MANUFACTUREORDER_I = WO010032.MANUFACTUREORDER_I
LEFT JOIN IV00101 ON WO010032.ITEMNMBR = IV00101.ITEMNMBR
LEFT JOIN IS010001 ON WO010032.MANUFACTUREORDER_I = IS010001.MANUFACTUREORDER_I
     AND WO010032.ITEMNMBR = IS010001.ITEMNMBR
LEFT JOIN SOP10100 ON IS010001.SOPNUMBE = SOP10100.SOPNUMBE
LEFT JOIN SOP10200 ON SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
     AND SOP10100.SOPTYPE = SOP10200.SOPTYPE
LEFT JOIN SY03900 ON WO010032.NOTEINDX = SY03900.NOTEINDX
LEFT JOIN PK010033 ON WO010032.MANUFACTUREORDER_I = PK010033.MANUFACTUREORDER_I
     AND WO010032.ITEMNMBR = PK010033.ITEMNMBR
WHERE WO010032.MANUFACTUREORDERST_I = '3'
     AND WR010130.MANUFACTUREORDER_I = @MO2
     AND WR010130.RTSEQNUM_I = @RoutingSequenceNo2

Open in new window

by the way the first line of the where clause has the effect of making the left join to WO010032 into an inner join.

Anthony Perkins

first line of the where clause has the effect of making the left join to WO010032 into an inner join.
It looks like you were ignored. A pity as they will learn with time.