query with Incorrect syntax near 'STYLE'.

  • 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

    DIVISIONAS COMPANY,

    SEASONAS SEASON,

    STYLEAS STYLE,

    MONTH_END_DATEas PREV_BAL_DATE,

    CURRENT_COSTAS CRNT_COST,

    BEGINNING_BALANCEAS PREV_BAL

    FROM dbo.INV_VAL_BEG_BALANCE

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

    )S1

    LEFT OUTER JOIN

    (

    /* RECEIPTS */

    SELECT

    dbo.RECVSKU#.RCDIVNAS COMPANY,

    dbo.RECVSKU#.RCSEASAS SEASON,

    dbo.RECVSKU#.RCstylAS style,

    dbo.RECVSKU#.RCSKU#AS SKU,

    dbo.RECVSKU#.RCDATEAS TRANS_DATE,

    Dbo.RECVSKU#.RCQTYAS REC_QTY,

    dbo.MITMAS.MMPUPRAS 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#.SCDIVNAS COMPANY,

    dbo.SHIPSKU#.SCSEASAS SEASON,

    dbo.SHIPSKU#.SCSTYLAS STYLE,

    dbo.SHIPSKU#.SCSKU#AS SKU,

    dbo.SHIPSKU#.SCDATEAS TRANS_DATE,

    dbo.SHIPSKU#.SCQTYAS SHIP_QTY,

    dbo.SHIPSKU#.sccostas INV_COST,

    dbo.MITMAS.MMPUPRAS 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

  • 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!

  • 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/

  • thanks, love your profile picture!!!

  • Mine or Lowell's?

    _______________________________________________________________

    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/

  • lowells, but yours i nice as well. 🙂

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

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