i have problem with case ststemant in row number statemant. when i try execute there is arror msg :
Executed as Single statement. Failed [3706 : 42000] Syntax error: expected something between '(' and the integer '1'.
CREATE MULTISET VOLATILE TABLE tt_RandNum ,NO LOG AS
case when tt_FirstEmailPOP.AB_Group_Status = 'NULL' then row_number() over(partition by tt_FirstEmailPOP.brand_ID,
case when tt_FirstEmailPOP.AB_Group_Status = 'NULL' then 1 else 0 end
order by randNum) else 0 end randNum
(select tt_FirstEmailPOP.*, randnum (1,100000) as randNum
from tt_FirstEmailPOP ) as dt) WITH DATA
ON COMMIT PRESERVE ROWS;
***i think to create new temp table with all cases but dont know exactly how to write it right.
It's probably the randnum (1,100000), the Teradata function is named RANDOM.
Brw, when you use SQL Assistant better switch to a .NET connection which will show you the (approximate) position where the parser failed.
let me show you the source statement :
select brand_id, MaxRand=MAX(randNum),
group by brand_id
as i know NEWID function is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function in Teradata.
when i use Nested Query without cases inside its work.
but with cases it's give me error message like i missed some syntax between '(' & the digut '1'.