Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2011
    Posts
    30

    Unanswered: sql query help ++

    hi all,

    my query from runs flawless. now i am trying to add some more fields to it that are just basic.. for example like alternatecode.


    WITH Ranked ( Itemcode, rnk, ItemDesc )
    AS ( SELECT Itemcode,
    ROW_NUMBER() OVER( PARTITION BY Itemcode ORDER BY Itemcode ),
    CAST( rssqldb.dbo.RSITEMDESCRIPTION.ItemDesc AS VARCHAR(8000) )
    FROM rssqldb.dbo.RSITEM INNER JOIN
    rssqldb.dbo.RSITEMDESCRIPTION ON rssqldb.dbo.RSITEM.ItemID = rssqldb.dbo.RSITEMDESCRIPTION.ItemID INNER JOIN
    rssqldb.dbo.RSACCOUNT ON rssqldb.dbo.RSITEM.AccountID = rssqldb.dbo.RSACCOUNT.AccountID
    Where rssqldb.dbo.RSACCOUNT.AccountID=63
    And rssqldb.dbo.RSITEM.ObjectID=3),
    AnchorRanked ( Itemcode, rnk, ItemDesc )
    AS ( SELECT Itemcode, rnk, ItemDesc
    FROM Ranked
    WHERE rnk = 1 ),
    RecurRanked ( Itemcode, rnk, ItemDesc )
    AS ( SELECT Itemcode, rnk, ItemDesc
    FROM AnchorRanked
    UNION ALL
    SELECT Ranked.Itemcode, Ranked.rnk,
    RecurRanked.ItemDesc + ', ' + Ranked.ItemDesc
    FROM Ranked
    INNER JOIN RecurRanked
    ON Ranked.Itemcode = RecurRanked.Itemcode
    AND Ranked.rnk = RecurRanked.rnk + 1 )
    SELECT Itemcode,RSITEM.UserField1, RSITEM.AlternateCode, SUBSTRING(MAX( ItemDesc ), CHARINDEX('DOCUMENT',MAX( ItemDesc COLLATE Latin1_General_CS_AS)), CHARINDEX(' PROJECT',MAX( ItemDesc COLLATE Latin1_General_CS_AS)))
    FROM RecurRanked
    GROUP BY Itemcode;

    i get the following errors:
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "RSITEM.UserField1" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "RSITEM.AlternateCode" could not be bound.

    Now both userfield1 and alternatecode are in the RSITEM table..

    any suggestions ?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    drdre, at the point you reference RSITEM.USERFIELD1 and RSITEM.ALTERNATECODE, they do not exist. You are selecting from the Common Table Expression (CTE) RECURRANKED and the only columns in that 'virtual table' are Itemcode, rnk, and ItemDesc. If you want USERFIELD1 and ALTERNATECODE, you either need to select them in the CTE that creates RECURRANKED (acutually in the CTE for RANKED) or join table RSITEM with you query.

  3. #3
    Join Date
    Feb 2011
    Posts
    30
    Stealth :

    I have tried many different options of adding the Alternate code to CTE you suggested and still no luck... i am pulling my hairs out at this point

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Since I don't know you data or how it relates, I can only make a guess. Try:
    Code:
    WITH Ranked ( Itemcode, USERFIELD1, ALTERNATECODE, rnk, ItemDesc )
      AS ( SELECT Itemcode
                , USERFIELD1
                , ALTERNATECODE
                , ROW_NUMBER() OVER( PARTITION BY Itemcode ORDER BY Itemcode )
                , CAST( rssqldb.dbo.RSITEMDESCRIPTION.ItemDesc AS VARCHAR(8000))
           FROM rssqldb.dbo.RSITEM 
                  INNER JOIN
                rssqldb.dbo.RSITEMDESCRIPTION 
                  ON rssqldb.dbo.RSITEM.ItemID = rssqldb.dbo.RSITEMDESCRIPTION.ItemID 
                  INNER JOIN
                rssqldb.dbo.RSACCOUNT 
                  ON rssqldb.dbo.RSITEM.AccountID = rssqldb.dbo.RSACCOUNT.AccountID
           Where rssqldb.dbo.RSACCOUNT.AccountID=63
             And rssqldb.dbo.RSITEM.ObjectID=3
         )
    
       , AnchorRanked ( Itemcode, rnk, ItemDesc )
      AS ( SELECT Itemcode
                , USERFIELD1
                , ALTERNATECODE
                , rnk
                , ItemDesc
           FROM Ranked
           WHERE rnk = 1 
         )
    
       , RecurRanked ( Itemcode, rnk, ItemDesc )
      AS ( SELECT Itemcode
                , USERFIELD1
                , ALTERNATECODE
                , rnk
                , ItemDesc
           FROM AnchorRanked
           UNION ALL
           SELECT Ranked.Itemcode
                , RANKED.USERFIELD1
                , RANKED.ALTERNATECODE
                , Ranked.rnk
                , RecurRanked.ItemDesc + ', ' + Ranked.ItemDesc
           FROM Ranked
                  INNER JOIN 
                RecurRanked
                  ON     Ranked.Itemcode = RecurRanked.Itemcode
                     AND Ranked.rnk      = RecurRanked.rnk + 1 
         )
    SELECT Itemcode
         , UserField1
         , AlternateCode
         , SUBSTRING(MAX( ItemDesc )
                    , CHARINDEX('DOCUMENT',MAX( ItemDesc COLLATE Latin1_General_CS_AS))
                    , CHARINDEX(' PROJECT',MAX( ItemDesc COLLATE Latin1_General_CS_AS))
                    )
    FROM RecurRanked
    GROUP BY Itemcode;
    If you have some key field that can be used to join back to RSITEM, you may be able to do something like:
    Code:
    SELECT RECURRANKED.Itemcode
         , RSITEM.UserField1
         , RSITEM.AlternateCode
         , SUBSTRING(MAX( ItemDesc )
                    , CHARINDEX('DOCUMENT',MAX( ItemDesc COLLATE Latin1_General_CS_AS))
                    , CHARINDEX(' PROJECT',MAX( ItemDesc COLLATE Latin1_General_CS_AS))
                    )
    FROM RecurRanked
           INNER JOIN
         RSSQLDB.DBO.RSITEM
           ON RECURRANKED.ITEMCODE = RSITEM ITEMCODE 
    GROUP BY RECURRANKED.Itemcode;

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You will have to replace the ?'s with the right CTEs, but this is what I think you are after:
    Code:
    WITH Ranked ( Itemcode, rnk, ItemDesc, UserField1, AlternateCode)
    AS ( SELECT Itemcode,
    	ROW_NUMBER() OVER( PARTITION BY Itemcode ORDER BY Itemcode ),
    	CAST( rssqldb.dbo.RSITEMDESCRIPTION.ItemDesc AS VARCHAR(8000) ),
    	RSITEM.UserField1, 
    	RSITEM.AlternateCode 
    FROM rssqldb.dbo.RSITEM INNER JOIN
    	rssqldb.dbo.RSITEMDESCRIPTION ON rssqldb.dbo.RSITEM.ItemID = rssqldb.dbo.RSITEMDESCRIPTION.ItemID INNER JOIN
    	rssqldb.dbo.RSACCOUNT ON rssqldb.dbo.RSITEM.AccountID = rssqldb.dbo.RSACCOUNT.AccountID
    Where rssqldb.dbo.RSACCOUNT.AccountID=63
      And rssqldb.dbo.RSITEM.ObjectID=3),
    
    AnchorRanked ( Itemcode, rnk, ItemDesc, UserField1, AlternateCode)
    AS ( SELECT Itemcode, 
    	rnk, 
    	ItemDesc,
    	RSITEM.UserField1, 
    	RSITEM.AlternateCode 
    FROM Ranked
    WHERE rnk = 1 ),
    
    RecurRanked ( Itemcode, rnk, ItemDesc, UserField1, AlternateCode)
    AS ( SELECT Itemcode, 
    	rnk, 
    	ItemDesc,
    	UserField1, 
    	AlternateCode 
    FROM AnchorRanked
    UNION ALL
    SELECT Ranked.Itemcode, 
    	Ranked.rnk,
    	RecurRanked.ItemDesc + ', ' + Ranked.ItemDesc,
    	?.UserField1, 
    	?.AlternateCode 
    FROM Ranked INNER JOIN 
    	RecurRanked ON Ranked.Itemcode = RecurRanked.Itemcode
    		AND Ranked.rnk = RecurRanked.rnk + 1 )
    
    SELECT Itemcode,
    	UserField1, 
    	AlternateCode, 
    	SUBSTRING(MAX( ItemDesc ), CHARINDEX('DOCUMENT', MAX( ItemDesc COLLATE Latin1_General_CS_AS)), CHARINDEX(' PROJECT',MAX( ItemDesc COLLATE Latin1_General_CS_AS)))
    FROM RecurRanked
    GROUP BY Itemcode, UserField1, AlternateCode;

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sniped, but I have a complete GROUP BY clause in my final query ;-).

  7. #7
    Join Date
    Feb 2011
    Posts
    30
    Wow thanks guys.

    I did some thinkinger with both your stuff.. and this is the end result that is running(would take a while to run crossing my fingers that it runs through right).

    WITH Ranked ( Itemcode, USERFIELD1, ALTERNATECODE, rnk, ItemDesc )
    AS ( SELECT Itemcode
    , USERFIELD1
    , ALTERNATECODE
    , ROW_NUMBER() OVER( PARTITION BY Itemcode ORDER BY Itemcode )
    , CAST( rssqldb.dbo.RSITEMDESCRIPTION.ItemDesc AS VARCHAR(8000))
    FROM rssqldb.dbo.RSITEM
    INNER JOIN
    rssqldb.dbo.RSITEMDESCRIPTION
    ON rssqldb.dbo.RSITEM.ItemID = rssqldb.dbo.RSITEMDESCRIPTION.ItemID
    INNER JOIN
    rssqldb.dbo.RSACCOUNT
    ON rssqldb.dbo.RSITEM.AccountID = rssqldb.dbo.RSACCOUNT.AccountID
    Where rssqldb.dbo.RSACCOUNT.AccountID=63
    And rssqldb.dbo.RSITEM.ObjectID=3
    )

    , AnchorRanked ( Itemcode,USERFIELD1,ALTERNATECODE, rnk, ItemDesc )
    AS ( SELECT Itemcode
    , USERFIELD1
    , ALTERNATECODE
    , rnk
    , ItemDesc
    FROM Ranked
    WHERE rnk = 1
    )

    , RecurRanked ( Itemcode,Userfield1,ALTERNATECODE, rnk, ItemDesc )
    AS ( SELECT Itemcode
    , USERFIELD1
    , ALTERNATECODE
    , rnk
    , ItemDesc
    FROM AnchorRanked
    UNION ALL
    SELECT Ranked.Itemcode
    , RANKED.USERFIELD1
    , RANKED.ALTERNATECODE
    , Ranked.rnk
    , RecurRanked.ItemDesc + ', ' + Ranked.ItemDesc
    FROM Ranked
    INNER JOIN
    RecurRanked
    ON Ranked.Itemcode = RecurRanked.Itemcode
    AND Ranked.rnk = RecurRanked.rnk + 1
    )
    SELECT Itemcode
    , UserField1
    , AlternateCode
    , SUBSTRING(MAX( ItemDesc )
    , CHARINDEX('DOCUMENT',MAX( ItemDesc COLLATE Latin1_General_CS_AS))
    , CHARINDEX(' PROJECT',MAX( ItemDesc COLLATE Latin1_General_CS_AS))
    )
    FROM RecurRanked
    GROUP BY Itemcode,UserField1, AlternateCode;

    Thanks again for all the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •