October 21, 2010 at 7:13 am
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
October 21, 2010 at 7:27 am
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
October 21, 2010 at 7:54 am
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/
October 21, 2010 at 7:59 am
thanks, love your profile picture!!!
October 21, 2010 at 8:02 am
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/
October 21, 2010 at 8:14 am
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