Cross Join

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

Cross Join

Hi,

 

i'm try understand the reason for that error massege:

'Selected non-aggregate values must be part of the associated group'

 

CREATE VOLATILE MULTISET TABLE SWT_QuickConversion105 as(select 0.5 as v_A_Size,0.0 as v_ControlPer) WITH DATA
ON COMMIT PRESERVE ROWS;

 

 

CREATE VOLATILE MULTISET TABLE tt_MaxSize AS
(
select tt_RandNum.FinishBonus,MAX(tt_RandNum.randNum) MaxRand,
round((MAX(tt_RandNum.randNum)*v_A_Size),0) AS EndRange_GroupA,
round(MAX(tt_RandNum.randNum) -(MAX(tt_RandNum.randNum)*v_ControlPer),0) AS EndRange_GroupB

from tt_RandNum cross join SWT_QuickConversion105
group by tt_RandNum.FinishBonus) WITH DATA
ON COMMIT PRESERVE ROWS;

 

this is part os store prosedure code, whene i create the temp table the compiler give me error msg:

'Selected non-aggregate values must be part of the associated group' i put instsd the value:v_ControlPer,v_A_Size numbers: 0.0,0.5, and i success create the SP.

But when i try call that SP i got the same error.

 

someone know why?

 

 

Tx!

 

1 REPLY
Junior Contributor

Re: Cross Join

You know that there's only a single row in that table, the optmizer doesn't.

 

Why do you want to create a single row Volatile Table instead of simply declaring parameters?

That's what they made for and this will make porting the source code from SQL Server much easier.