query with Incorrect syntax near 'STYLE'.

Question

Post reply

query with Incorrect syntax near 'STYLE'.

sharonmtowler

Default port

Points: 1495
More actions
October 21, 2010 at 7:13 am

#242015

i am in the process of trying to speed up a union query(takes hours to run) i am not sure if this is the way to go, but im giving it a shot.

when i try to run this i get an Incorrect syntax near 'STYLE'. error.

could it be because i dont have the same number of columns in each query?

SELECT DISTINCT

S1.COMPANY,

S1.SEASON,

S1.STYLE,

S1.PREV_BAL_DATE,

S1.CRNT_COST,

S1.PREV_BAL,

S2.COMPANY,

S2.SEASON,

S2.style,

S2.SKU,

S2.TRANS_DATE,

S2.REC_QTY,

S2.CRNT_COST,

S3.COMPANY,

S3.SEASON,

S3.STYLE,

S3.SKU,

S3.TRANS_DATE,

S3.SHIP_QTY,

S3.INV_COST,

S3.CRNT_COST

FROM (/*BEG BALANCE*/

SELECT

DIVISION AS COMPANY,

SEASON AS SEASON,

STYLE AS STYLE,

MONTH_END_DATE as PREV_BAL_DATE,

CURRENT_COST AS CRNT_COST,

BEGINNING_BALANCE AS PREV_BAL

FROM dbo.INV_VAL_BEG_BALANCE

WHERE DIVISION='AAA' AND MONTH_END_DATE ='20100731'

)S1

LEFT OUTER JOIN

(

/* RECEIPTS */

SELECT

dbo.RECVSKU#.RCDIVN AS COMPANY,

dbo.RECVSKU#.RCSEAS AS SEASON,

dbo.RECVSKU#.RCstyl AS style,

dbo.RECVSKU#.RCSKU# AS SKU,

dbo.RECVSKU#.RCDATE AS TRANS_DATE,

Dbo.RECVSKU#.RCQTY AS REC_QTY,

dbo.MITMAS.MMPUPR AS CRNT_COST

FROM

dbo.MITMAS

RIGHT OUTER JOIN dbo.RECVSKU# ON dbo.MITMAS.MMITNO = dbo.RECVSKU#.RCSKU#

WHERE dbo.RECVSKU#.RCDATE>='20100801' AND dbo.RECVSKU#.RCDATE <='20100831'

AND dbo.RECVSKU#.RCDIVN='AAA'

)S2

LEFT OUTER JOIN

(

/* SHIPMENTS */

SELECT

dbo.SHIPSKU#.SCDIVN AS COMPANY,

dbo.SHIPSKU#.SCSEAS AS SEASON,

dbo.SHIPSKU#.SCSTYL AS STYLE,

dbo.SHIPSKU#.SCSKU# AS SKU,

dbo.SHIPSKU#.SCDATE AS TRANS_DATE,

dbo.SHIPSKU#.SCQTY AS SHIP_QTY,

dbo.SHIPSKU#.sccost as INV_COST,

dbo.MITMAS.MMPUPR AS CRNT_COST

FROM dbo.SHIPSKU#

LEFT OUTER JOIN dbo.MITMAS ON dbo.SHIPSKU#.SCSKU# = dbo.MITMAS.MMITNO

WHERE dbo.SHIPSKU#.SCDATE>='20100801' AND dbo.SHIPSKU#.SCDATE <='20100831'

AND dbo.SHIPSKU#.SCDIVN='AAA'

)S3

ON S1.SEASON = S2.SEASON

AND S1.STYLE = S2.STYLE

AND S1.SEASON = S3.SEASON

AND S1.STYLE = S3.STYLE

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

i believe the error is related to the fact that you have TWO left outer joins, but only one [ON]

you meant to left join S2 to S1, and then S3 to S2, but ON criteria is missing;

this is syntactically correct:



SELECT DISTINCT

  S1.COMPANY,

  S1.SEASON,

  S1.STYLE,

  S1.PREV_BAL_DATE,

  S1.CRNT_COST,

  S1.PREV_BAL,

  S2.COMPANY,

  S2.SEASON,

  S2.style,

  S2.SKU,

  S2.TRANS_DATE,

  S2.REC_QTY,

  S2.CRNT_COST,

  S3.COMPANY,

  S3.SEASON,

  S3.STYLE,

  S3.SKU,

  S3.TRANS_DATE,

  S3.SHIP_QTY,

  S3.INV_COST,

  S3.CRNT_COST

FROM

  (/*BEG BALANCE*/

    SELECT

      DIVISION AS COMPANY,

      SEASON AS SEASON,

      STYLE AS STYLE,

      MONTH_END_DATE as PREV_BAL_DATE,

      CURRENT_COST AS CRNT_COST,

      BEGINNING_BALANCE AS PREV_BAL

    FROM dbo.INV_VAL_BEG_BALANCE

    WHERE DIVISION='AAA' AND MONTH_END_DATE ='20100731'

  )S1

LEFT OUTER JOIN

  (/* RECEIPTS */

    SELECT

      dbo.RECVSKU#.RCDIVN AS COMPANY,

      dbo.RECVSKU#.RCSEAS AS SEASON,

      dbo.RECVSKU#.RCstyl AS style,

      dbo.RECVSKU#.RCSKU# AS SKU,

      dbo.RECVSKU#.RCDATE AS TRANS_DATE,

      Dbo.RECVSKU#.RCQTY AS REC_QTY,

      dbo.MITMAS.MMPUPR AS CRNT_COST

    FROM dbo.MITMAS

    RIGHT OUTER JOIN dbo.RECVSKU# ON dbo.MITMAS.MMITNO = dbo.RECVSKU#.RCSKU#

    WHERE dbo.RECVSKU#.RCDATE>='20100801' AND dbo.RECVSKU#.RCDATE <='20100831'

    AND dbo.RECVSKU#.RCDIVN='AAA'

  )S2

      --moved by Lowell to show the Criteria for the join

      ON  S1.SEASON = S2.SEASON

      AND S1.STYLE = S2.STYLE

LEFT OUTER JOIN

  (/* SHIPMENTS */

    SELECT

      dbo.SHIPSKU#.SCDIVN AS COMPANY,

      dbo.SHIPSKU#.SCSEAS AS SEASON,

      dbo.SHIPSKU#.SCSTYL AS STYLE,

      dbo.SHIPSKU#.SCSKU# AS SKU,

      dbo.SHIPSKU#.SCDATE AS TRANS_DATE,

      dbo.SHIPSKU#.SCQTY AS SHIP_QTY,

      dbo.SHIPSKU#.sccost as INV_COST,

      dbo.MITMAS.MMPUPR AS CRNT_COST

    FROM dbo.SHIPSKU#

    LEFT OUTER JOIN dbo.MITMAS ON dbo.SHIPSKU#.SCSKU# = dbo.MITMAS.MMITNO

    WHERE dbo.SHIPSKU#.SCDATE>='20100801' AND dbo.SHIPSKU#.SCDATE <='20100831'

    AND dbo.SHIPSKU#.SCDIVN='AAA'

  )S3

--changed slightly by Lowell to fix the Criteria for the join

ON  S1.SEASON = S3.SEASON

AND S1.STYLE = S3.STYLE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286562 More actions · Answer 2

Looks like the "ON" is missing on the first join too. Basically you created an incredibly long alias for you table. 😉 Also, just guessing that once you get this put together you are not going to happy with the performance. I am guessing that this was originally 3 select statements with unions?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sharonmtowler Default port Points: 1495 More actions · Answer 3

sharonmtowler

Default port

Points: 1495

October 21, 2010 at 7:59 am

#1240331

thanks, love your profile picture!!!

Sean Lange SSC Guru Points: 286562 More actions · Answer 4

Mine or Lowell's?