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

Database
Enthusiast

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

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?

 

 

 

 

10 REPLIES
Enthusiast

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

Player_ID  is integer

pp.v_CIDorPlayerID  is varchar

CID_Player_ID  is varchar

Player_ID is integer

 

 

Teradata Employee

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

Where is table pp defined?

Enthusiast

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

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;

Teradata Employee

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

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

Enthusiast

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

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?

Teradata Employee

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

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.

Enthusiast

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

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)))

Junior Contributor

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

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?

Enthusiast

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

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