Recently we have converted oracle to terada sql queries most of the query runs fine except one that have a function
Over simplified sample query looks like this ---
SELECT A, B, C , CASE (SOMETHING) AS D, GROUPING_ID (A,B,C) AS E
1. GROUPING_ID is not even taking as a function in teradata sql assistant tool or teradata studio (it is not showing in blue color i mean)
2. query runs fine if i comment the grouping_id section
3. Tried with GROUPING SETS(), CUBE() etc that i read in some teradata form here
Some pointers to a tantative solution will be highly appriciated
GROUPING_ID is a standard Oracle function, to provide a bit vector on multiple columns, to identify a row.
In Teradata, we typically use these columns directly as PI or SI.
Oracle's GROUPING_ID combines the GROUPINGs into a single base 2 number:
GROUPING_ID (coln, ..., col3,col2,col1)
Power(2,n-1)*Grouping(colN) + ... + Power(2,3-1)*Grouping(col3) + Power(2,2-1)*Grouping(col2) + Power(2,1-1)*Grouping(col1)
GROUPING_ID(A,B,C,D) = 8*Grouping(A) + 4*Grouping(B) + 2*Grouping(C) + Grouping(D)
Thanks for your valuable comments, I have tried most of them. Yet struggling to get a sutiable function of GROUPING_ID in Oracle to SOMETHING_ID in Teradata that will techinically behave the same...
The only way to get the same result is rewriting it using the calculation I showed.
You might try to create multiple SQL UDFs (for different numbers of parameters), but you still need to apply GROUPING before passing a column to that UDF.
I have tried the way you have recommended the column values are comming as "0" , i took 5 column
(16*A + 8*B + 4*C + D) as X
column X is showing "0" for all records
have gone upto 12 columns grouping as mentioend still value is "0"
2048*GROUPING(COL12)+1024*GROUPING(COL11)+512*GROUPING(COL10)+.............+GROUPING(COL1) still get "0"
WHAT THE DUCK