Multi Part Identifier error

[chrisssyp82]

SQL 2008

I've created a script which returns a list, however when i want to pass a field from my select in @Worktable into a parameter of a function which is linkted to @Worktable i get the error:

"The multi part identifier 'pos.alt_curr_cde' could not be bout"

My script is:

Use [InfoPortal]

DECLARE	@Section				VARCHAR(100)
DECLARE @PortfolioGroup			VARCHAR(20)
DECLARE @StartDateTime			DATETIME
DECLARE @EndDateTime			DATETIME
DECLARE @InquiryBasis			INT
DECLARE @RateCCY				CHAR(3)

DECLARE @Worktable TABLE
	(
			[Portfolio ID]					VARCHAR(12)
		,	[Date]							DATETIME
		,	[All in Market Value Base]		FLOAT
		,	[All in Market Value Converted]	FLOAT
		,	[Portfolio Base Ccy]			CHAR(3)
	)
/*------------------------------------------------------------------------------------------------------
Set default values
------------------------------------------------------------------------------------------------------*/
SET @PortfolioGroup = '$OEICS'
SET @StartDateTime = '2011-09-30 00:00:00.000'
SET @EndDateTime = '2011-12-31 23:59:59.000'
SET @InquiryBasis = 1
SET @RateCCY = 'USD'
/*------------------------------------------------------------------------------------------------------
Worktable
------------------------------------------------------------------------------------------------------*/
SET @Section = 'Worktable' 
BEGIN
	INSERT INTO @Worktable
			SELECT	
				dg.PortfolioID						AS [Portfolio ID]
			,	dg.AsOfDate							AS [Date] 
			,	dg.AcctMarketValue					AS [All in Market Value Base]
			,	dg.AcctMarketValue * rt.Rate		AS [All in Market Value Converted]
			,	pos.alt_curr_cde					AS [Portfolio Base Ccy]
		--	,	CAST('2011-09-30 00:00:00.000' AS DATETIME)	AS [Start Date]
		--	,	CAST('2011-12-31 23:59:59.000' AS DATETIME)	AS [End Date]
		--  ,	rt.Rate
			
			FROM	fn_Generic_DGControl ('','',@PortfolioGroup,@InquiryBasis,@StartDateTime,@EndDateTime,'D','M','POSITION') AS Dg
				INNER JOIN Position_dg AS Pos 
				ON	dg.DataGrpCtlNum = pos.data_grp_ctl_num

				LEFT OUTER JOIN fn_Generic_FXRates (@StartDateTime, @EndDateTime,'M','SPOT',pos.alt_curr_cde,@RateCCY) AS Rt
				ON	Rt.BaseCCY = pos.alt_curr_cde
				AND	CONVERT(VARCHAR(10),Rt.RateDate	,120) = CONVERT(VARCHAR(10),dg.AsOfDate,120)

				GROUP BY dg.PortfolioID
				,		 dg.AsOfDate
				,		 dg.AcctMarketValue
				,		 pos.alt_curr_cde
				,		 rt.Rate
				
				ORDER BY dg.PortfolioID
				,		 dg.AsOfDate ASC
				
END

SELECT * FROM @Worktable

I understand what the error means, i just can't for the life of me think how i can recode this so that it returns my list from @Worktable.

Any pointers in the right direction would be much appreciated.

Thanks

 

[SQLBill]

Instead of using pos use the actual table name (Position_dg).

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[chrisssyp82]

I've tried that and i still get the same error message.

 

[markros]

Instead of LEFT JOIN with the table valued function use OUTER APPLY, e.g.

FROM    fn_Generic_DGControl ('','',@PortfolioGroup,@InquiryBasis,@StartDateTime,@EndDateTime,'D','M','POSITION') AS Dg
                INNER JOIN Position_dg AS Pos 
                ON    dg.DataGrpCtlNum = pos.data_grp_ctl_num

                OUTER APPLY fn_Generic_FXRates (@StartDateTime, @EndDateTime,'M','SPOT',pos.alt_curr_cde,@RateCCY) AS Rt
                WHERE Rt.Pk IS NULL OR (    Rt.BaseCCY = pos.alt_curr_cde
                AND    CONVERT(VARCHAR(10),Rt.RateDate    ,120) = CONVERT(VARCHAR(10),dg.AsOfDate,120))

PluralSight Learning Library

 

[chrisssyp82]

thanks for the response, however i'm confused as to why you have added this piece of code:

WHERE Rt.Pk IS NULL

What is rt.pk?

 

[markros]

By Rt.PK I meant the primary key field returned by the function. If there is no primary key field, use any field that can not be null, so we can use our extra criteria and still keep the original LEFT JOIN intent. (without check for NULL this query will be converted into CROSS APPLY /INNER JOIN)

PluralSight Learning Library

 

[chrisssyp82]

Cool i understand what you're saying now. Thanks for your response, was very helpful.