Row Number statement with case statement

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Row Number statement with case statement

Hi,

 

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

(

select tt_FirstEmailPOP.*,

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

 

from

(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.

4 REPLIES
Junior Contributor

Re: Row Number statement with case statement

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.

Teradata Employee

Re: Row Number statement with case statement

Also:

  • outer query should use the derived table alias "dt" to qualify the column references, not the table/view name tt_FirstEmailPOP
  • is the status you are checking really the string value 'NULL'? If it is a "null value" then you need to use IS NULL to test
  • why include the CASE within the PARTITION BY and perhaps skew the STAT function step more? Maybe just use AB_Group_Status
  • is "random" really a requirement? If you just need "arbitrary" ordering then consider some deterministic function like HASHROW applied to one or more columns for the ORDER BY
Enthusiast

Re: Row Number statement with case statement

let me show you the source statement :

 

select brand_id, MaxRand=MAX(randNum),
     EndRange_GroupA=round((MAX(randNum)*@A_Size),0),
     EndRange_GroupB=round(MAX(randNum)-(MAX(randNum)*@ControlPer),0)-- round((MAX(randNum)*@B_Size),0),
into #MaxSize
from #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'.

 

 

 

 

Junior Contributor

Re: Row Number statement with case statement

There's neither NEWID nor ROW_NUMBER in this source.

 

What are you trying do do with those CASEs?