3782: Improper column reference in the search condition of a joined table. ERROR

[Dimabo]

CREATE MULTISET VOLATILE TABLE tt_Pop,NO LOG AS

(

select CID, Player_ID,A.Brand_ID

from Bingo_PlayerAnalysis a

inner join tt__PlayerID_Split b on (Player_ID =(case when pp.v_CIDorPlayerID = 'player_id and Brand' then b.CID_Player_ID else a.Player_ID end)

)) WITH DATA

ON COMMIT PRESERVE ROWS;

Hi everybody.

I have a problem with this code- error is on the title.

the issue is the case statement: what's wrong? anynody?

[Dimabo]

Player_ID  is integer

pp.v_CIDorPlayerID  is varchar

CID_Player_ID  is varchar

Player_ID is integer

[David_Roth]

Where is table pp defined?

[Dimabo]

Hi, pp table defined two lines above:

CREATE MULTISET VOLATILE TABLE pp , no log

(v_CIDorPlayerID VARCHAR(100),

v_CID_PlayerID_Split VARCHAR(400),

v_Brand VARCHAR(400),

v_Brand_ID INTEGER,

v_VIP_List INTEGER) ON COMMIT PRESERVE ROWS;

[David_Roth]

In the select statement. Where is pp defined as a join in the select statement?

[Dimabo]

CREATE MULTISET VOLATILE TABLE pp , no log

(v_CIDorPlayerID VARCHAR(100),

v_CID_PlayerID_Split VARCHAR(400),

v_Brand VARCHAR(400),

v_Brand_ID INTEGER,

v_VIP_List INTEGER) ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE tt_Pop,NO LOG AS

(

select CID, Player_ID,A.Brand_ID

from Bingo_PlayerAnalysis a

inner join tt__PlayerID_Split b on (Player_ID =(case when pp.v_CIDorPlayerID = 'player_id and Brand' then b.CID_Player_ID else a.Player_ID end)

)) WITH DATA

ON COMMIT PRESERVE ROWS;

This a code,  pp.v_CIDorPlayerID is a parameter - it's not should be defined on join... Am i right?

[GJColeman]

That's right: pp is not defined as a table name or table alias in this select statement. You say pp.v_CIDorPlayerID
is a parameter?  Is this in a stored procedure?  SP parameters do not contain '.'s.

Also, even if you fix the case statement you will still have problems because it makes no sense to define a join predicate by matching Bingo_PlayerAnalysis.Player_ID to itself.  You can't join A to B on A.x = A.x.

[Dimabo]

Yes, this is a store procedure...

But, how it could be- i just convert this code from SQL - it's run on MS server...

CREATE PROCEDURE Analysis_LastBack

@CIDorPlayerID varchar(100),

@CID_PlayerID_Split varchar(400),

@Brand varchar(400)

AS

BEGIN

　

　

declare @Brand_ID int = (select Brand_ID from tt_Brands where Brand_Desc = @Brand)

select CID_Player_ID =@CID_PlayerID_Split

into #CID_PlayerID

SELECT Split.a.value('.', 'VARCHAR(100)') AS CID_Player_ID

into #CID_PlayerID_Split

FROM (SELECT CAST ('<M>' + REPLACE(CID_Player_ID, ',', '</M><M>') + '</M>' AS XML) AS String

FROM #CID_PlayerID )AS A CROSS APPLY String.nodes ('/M') AS Split(a)

CREATE CLUSTERED INDEX A ON #CID_PlayerID_Split (CID_Player_ID)

　

declare @VIP_List int =

(Select distinct 1

from PlayerInfoAnalysis   a

inner join #CID_PlayerID_Split b on ( Player_ID = (case when @CIDorPlayerID = 'player_id and Brand' then b.CID_Player_ID else a.Player_ID end)))

[dnoeth]

pp.v_CIDorPlayerID is not a parameter, it's a column from a table, only in your original code it's a parameter, @CIDorPlayerID.

Your XML/Cross Apply can be rewritten using STRTOK_SPLIT_TO_TABLE (I doubt you need all those temp tables).

But what is the final SELECT DISTINCT 1 supposed to do do?

Returning 1 or NULL after a join or cross join?

[Dimabo]

You r right.

I need to convert some source code, that was written by someone else and i really try not to deep to mush into the code if it's not neccessary.

In teradata i need to create volatile table for parameter when i use it on store procedure - that's the reason for pp.v_CIDorPlayerID.

BR