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

UDA
Enthusiast

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

Hi all,

I have an error above when running follow table:

CREATE MULTISET VOLATILE TABLE tt_help,NO LOG AS
(
select distinct CID
from tt_VIPList a
inner join tt_CID_PlayerID_Split b on
(Player_ID =(case when tt_help.v_CIDorPlayerID = 'player_id and Brand' then b.CID_Player_ID else a.Player_ID end)
)) WITH DATA
ON COMMIT PRESERVE ROWS;

 

Please help, what wrong?

12 REPLIES
Junior Contributor

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

What is tt_help.v_CIDorPlayerID supposed to be?

You try to use table tt_help while creating it...

 

 

Enthusiast

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

My mistake, but still has an error...

 

CREATE MULTISET VOLATILE TABLE tt_yyyy,NO LOG AS
(
select distinct CID
from tt_VIPList a
inner join tt_CID_PlayerID_Split b on
(Player_ID =(case when v_CIDorPlayerID = 'player_id and Brand' then b.CID_Player_ID else a.Player_ID end)
)) WITH DATA
ON COMMIT PRESERVE ROWS;

Junior Contributor

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

Player_ID in the join is not qualified, but this seems to be a very strange condition anyway.

 

Can you elaborate what you actually trying to calculate?

Some example data & expected result?

Enthusiast

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

Hi, i'm part of migration team and my task is to convert follow SQL code:

Select distinct CID
INTO tt_yyyy 
from tt_VIPList a
   inner join tt_CID_PlayerID_Split b on
    (  Player_ID = (case when  v_CIDorPlayerID = 'player_id and Brand' then b.CID_Player_ID else a.Player_ID end)

 

In SQL it's work fine... In TD - totally not..
             

Junior Contributor

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

You probably mean Microsoft's dialect T-SQL when you talk about SQL (MS is really successful making people believe that SQL is automatically related to MS SQL Server).

 

As long as there's only one column named Player_ID in both tables this should work (of course resulting in a bad product join), what's the exact error message?

 

And what's the logic behind that query?

Enthusiast

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

The error is:

Executed as Single statement.  Failed [3782 : HY000] Improper column reference in the search condition of a joined table.
Elapsed time = 00:00:00.006
 
STATEMENT 1: Create Table failed.

 

The logic is simple:

switch join condition depend on v_CIDorPlayerID value...

 

I'm lost in this case...

 

Junior Contributor

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

What's the DDL of both source tables?

Enthusiast

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

tt_CID_PlayerID_Split:

CID_Player_ID               ,CV 

 

tt_VIPList:

DataUpdateTime TIMESTAMP(6),

Player_ID INTEGER NOT NULL,

CID INTEGER NOT NULL,

UserName VARCHAR(4000) CHARACTER SET UNICODE NOT CASESPECIFIC,

AccountStatus_ID INTEGER,

Brand_ID INTEGER,

 

Junior Contributor

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

Where is v_CIDorPlayerID coming from?