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?
Player_ID is integer
pp.v_CIDorPlayerID is varchar
CID_Player_ID is varchar
Player_ID is integer
Where is table pp defined?
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;
In the select statement. Where is pp defined as a join in the select statement?
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?
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.
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)))
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?
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