March 6, 2012 at 12:06 pm
My script builds the temporary table #tblFiscalWk, that will return a single record with the integer field YR_WK. I wish to use this field in the WHERE clause within the main logic. When I execute the script, I get the error "The multi-part identifier "t1.YR_WK" could not be bound". What am I doing wrong?
SELECT TOP 1 YR_WK
FROM #tblFiscalWk t1
ORDER BY YR_WK
SELECT DISTINCT .........
FROM ..........
WHERE (RETAIL_SALES.YR*1000)+(RETAIL_SALES.MO*10)+RETAIL_SALES.WK>=t1.YR_WK
March 6, 2012 at 12:13 pm
Can't see what you are doing. Try posting all the code not just pieces of it.
Plus, your first select statement needs to be part of the query that follows or you need to put the value returned in a variable and us it.
March 6, 2012 at 12:21 pm
Here is the entire script:
create table #tblFiscalWk
( YR int,
MOint,
WKint,
YR_WKint,
sqlidint
)
INSERT INTO
#tblFiscalWk
SELECT TOP 5
RETAIL_SALES.YR as YR
,RETAIL_SALES.MO as MO
,RETAIL_SALES.WK as WK
,CONVERT (INT,(RETAIL_SALES.YR*1000)+(RETAIL_SALES.MO*10)+RETAIL_SALES.WK) as YR_WK
,CONVERT (INT,ROW_NUMBER() OVER(ORDER BY RETAIL_SALES.YR, RETAIL_SALES.MO, RETAIL_SALES.WK ASC)) AS sqlid
FROM
Evy_RH_Objects.dbo.RETAIL_SALES RETAIL_SALES
WHERE
RETAIL_SALES.CUST_NO=@CustNo
AND RETAIL_SALES.YR>=@Year-1
AND (RETAIL_SALES.YR*1000)+(RETAIL_SALES.MO*10)+RETAIL_SALES.WK<=(@Year*1000)+(@Month*10)+@Week
GROUP BY
RETAIL_SALES.YR
,RETAIL_SALES.MO
,RETAIL_SALES.WK
ORDER BY
ROW_NUMBER() OVER(ORDER BY RETAIL_SALES.YR
,RETAIL_SALES.MO, RETAIL_SALES.WK ASC) DESC
SELECT TOP 1 YR,MO,WK,YR_WK
FROM #tblFiscalWk t1
ORDER BY YR_WK
SELECT DISTINCT
ITEMMAST.STYLE as STYLE
,ITEMMAST.COLOR as COLOR
,Max(ITEMMAST.SEASON) as SEASON
,RETAIL_SALES.WK as WK
,MAX(ARCLRFIL.COL_DESC) as COLOR_NAME
,MAX(INVEN_HDR.DESCRIPT) as DESCRIPT
,MAX(INVEN_HDR.SEASON) as SEASON
,MAX(RETAIL_SALES.Status) as STATUS
,MAX(RETAIL_SALES.CUST_NO) as CUST_NO
,MAX(CUSTMAST.CUSTNAME) as CUSTNAME
,(SELECT MAX(IMGPATH) FROM RH2007_EvyLive.dbo.INVEN WHERE STYLE = ITEMMAST.STYLE and COLOR = ITEMMAST.COLOR) IMGPATH
,MAX(ITEMMAST.PRICE) as COST
,MAX((SKUMAST.PRICE/SKUMAST.MINQUANT)) as PRICE
,(CASE WHEN MAX(INVEN_HDR.ATTR6) IS NULL THEN 'OLD' ELSE MAX(INVEN_HDR.ATTR6) END) as MKT_GRP
,MAX(INVEN_HDR.TYPE) as TYPE
,MAX(INVEN_HDR.LINE) as LINE
,RTrim(MAX(INVEN_HDR.ATTR6))
+ replicate('^',15-len(RTrim(MAX(INVEN_HDR.ATTR6))))
+ replicate('0',10-len(convert(varchar(10),SUM(RETAIL_SALES.[Sales $ LW]))))
+ convert(varchar(10),SUM(RETAIL_SALES.[Sales $ LW])) as SORT
,SUM(RETAIL_SALES.[Sales $ LW]) as [Sales $ LW]
,SUM(RETAIL_SALES.[Sales Units LW]) as [Sales Units LW]
,SUM(RETAIL_SALES.[EOW OH Units]) as [EOW OH Units]
,MAX(RETAIL_SALES.SKU) as SKU
,MAX(ITEMMAST.SKU) as SKU2
FROM Evy_RH_Objects.dbo.RETAIL_SALES RETAIL_SALES
LEFT OUTER JOIN RH2007_EvyLive.dbo.CUSTMAST CUSTMAST on CUSTMAST.CUSTNO=RETAIL_SALES.CUST_NO
LEFT OUTER JOIN RH2007_EvyLive.dbo.ITEMMAST ITEMMAST on ITEMMAST.CUSTNO=RETAIL_SALES.CUST_NO AND ITEMMAST.SKU=RETAIL_SALES.SKU
AND ITEMMAST.=(SELECT MAX(IM.) FROM RH2007_EvyLive.dbo.ITEMMAST IM WHERE IM.CUSTNO=RETAIL_SALES.CUST_NO AND IM.SKU=RETAIL_SALES.SKU)
LEFT OUTER JOIN RH2007_EvyLive.dbo.SKUMAST SKUMAST on ITEMMAST.CUSTNO+ITEMMAST.SKU=SKUMAST.CUSTNO+SKUMAST.SKU
LEFT OUTER JOIN RH2007_EvyLive.dbo.INVEN_HDR INVEN_HDR on INVEN_HDR.STYLE=ITEMMAST.STYLE AND INVEN_HDR.SEASON=ITEMMAST.SEASON
LEFT OUTER JOIN RH2007_EvyLive.dbo.ARCLRFIL ARCLRFIL on ARCLRFIL.COL_CODE=ITEMMAST.COLOR
WHERE
RETAIL_SALES.CUST_NO=@CustNo
and RETAIL_SALES.YR=@Year
and RETAIL_SALES.MO=@Month
and INVEN_HDR.ATTR6<>'OLD'
and INVEN_HDR.ATTR10 <> 'NO'
and (RETAIL_SALES.YR*1000)+(RETAIL_SALES.MO*10)+RETAIL_SALES.WK>=t1.YR_WK
GROUP BY ITEMMAST.STYLE, ITEMMAST.COLOR, RETAIL_SALES.WK
DROP TABLE #tblFiscalWk
March 6, 2012 at 12:27 pm
The alias t1 does not exist in that query. If you want a value to persist between queries you have to store it somewhere (variable or a table).
_______________________________________________________________
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/
March 6, 2012 at 12:36 pm
I'd be curious about the definitions of the tables used in each of the queries as well.
Also, what is with select the top 5 from one table into the temporary table then selecting the top 1 from that table? Couldn't you just select the top 1 from the first table?
Oh, sample data for the tables involved would also be helpful.
March 6, 2012 at 2:52 pm
My intent is to retrieve the last 5 records in the table by first sorting the table in descending order:
ORDER BY
ROW_NUMBER() OVER(ORDER BY RETAIL_SALES.YR
,RETAIL_SALES.MO, RETAIL_SALES.WK ASC) DESC
Now the bottom 5 records are DESC sequence. I need to retrieve the last record. Finally, I execute the following which sorts the 5 records in ASC sequence and pick the first record.
SELECT TOP 1 YR,MO,WK,YR_WK
FROM #tblFiscalWk t1
ORDER BY YR_WK
Now, I want to the the result YR_WK within the WHERE clause towards the end of the script.
"and (RETAIL_SALES.YR*1000)+(RETAIL_SALES.MO*10)+RETAIL_SALES.WK>=t1.YR_WK"
I get the message: The multi-part identifier "t1.YR_WK" could not be bound.
I appreciate any assistance.
March 6, 2012 at 8:31 pm
JayWinter (3/6/2012)
My intent is to retrieve the last 5 records in the table by first sorting the table in descending order:ORDER BY
ROW_NUMBER() OVER(ORDER BY RETAIL_SALES.YR
,RETAIL_SALES.MO, RETAIL_SALES.WK ASC) DESC
Now the bottom 5 records are DESC sequence. I need to retrieve the last record. Finally, I execute the following which sorts the 5 records in ASC sequence and pick the first record.
SELECT TOP 1 YR,MO,WK,YR_WK
FROM #tblFiscalWk t1
ORDER BY YR_WK
Now, I want to the the result YR_WK within the WHERE clause towards the end of the script.
"and (RETAIL_SALES.YR*1000)+(RETAIL_SALES.MO*10)+RETAIL_SALES.WK>=t1.YR_WK"
I get the message: The multi-part identifier "t1.YR_WK" could not be bound.
I appreciate any assistance.
So, just to be clear, you want the fifth to last record from the original table?
March 6, 2012 at 8:48 pm
JayWinter (3/6/2012)
My intent is to retrieve the last 5 records in the table by first sorting the table in descending order:ORDER BY
ROW_NUMBER() OVER(ORDER BY RETAIL_SALES.YR
,RETAIL_SALES.MO, RETAIL_SALES.WK ASC) DESC
Now the bottom 5 records are DESC sequence. I need to retrieve the last record. Finally, I execute the following which sorts the 5 records in ASC sequence and pick the first record.
SELECT TOP 1 YR,MO,WK,YR_WK
FROM #tblFiscalWk t1
ORDER BY YR_WK
Now, I want to the the result YR_WK within the WHERE clause towards the end of the script.
"and (RETAIL_SALES.YR*1000)+(RETAIL_SALES.MO*10)+RETAIL_SALES.WK>=t1.YR_WK"
I get the message: The multi-part identifier "t1.YR_WK" could not be bound.
I appreciate any assistance.
It's the wrong way to do it. You've created a non-SARGable WHERE clause that will never be able to do an INDEX SEEK. You're also recalculating the same information over everytime you use the query yet the boundaries of the "week" never change.
Please see the following post for a much faster method of grouping by week and doing ORDER BYs for the same.
http://www.sqlservercentral.com/Forums/FindPost1261947.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply