Using table result within a script

  • 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

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

  • 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

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

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

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

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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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