Oracle to Teradata SQL Conversion challanges

Database
Enthusiast

Oracle to Teradata SQL Conversion challanges

Hello All,

Recently we have converted oracle to terada sql queries most of the query runs fine except one that have a function

GROUPING_ID

 

Over simplified sample query looks like this ---

SELECT  A, B, C , CASE (SOMETHING) AS D, GROUPING_ID (A,B,C) AS E

FROM TABLE;

 

Observation

 

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

 

Thanks,

 

 

 

 

 


Accepted Solutions
Highlighted
Teradata Employee

Re: Oracle to Teradata SQL Conversion challanges

Did you include GROUP BY ROLLUP / CUBE / GROUPING SETS?

1 ACCEPTED SOLUTION
9 REPLIES
Junior Supporter

Re: Oracle to Teradata SQL Conversion challanges

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.

 

Teradata Frank, Certified Master
Teradata Employee

Re: Oracle to Teradata SQL Conversion challanges

Look for the SELECT statement in the Teradata SQL manual, under the GROUP BY clause, for Cube Grouping, Grouping Sets, Rollup Grouping and Examples.

Junior Contributor

Re: Oracle to Teradata SQL Conversion challanges

Oracle's GROUPING_ID combines the GROUPINGs into a single base 2 number:

GROUPING_ID (coln, ..., col3,col2,col1)

translates to

Power(2,n-1)*Grouping(colN) + ... + Power(2,3-1)*Grouping(col3) + Power(2,2-1)*Grouping(col2) + Power(2,1-1)*Grouping(col1)

e.g.

GROUPING_ID(A,B,C,D) = 8*Grouping(A) + 4*Grouping(B) + 2*Grouping(C) + Grouping(D)

 

 

Enthusiast

Re: Oracle to Teradata SQL Conversion challanges

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

 

 

Junior Contributor

Re: Oracle to Teradata SQL Conversion challanges

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.

Enthusiast

Re: Oracle to Teradata SQL Conversion challanges

 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

Enthusiast

Re: Oracle to Teradata SQL Conversion challanges

I mean Grouping

 

(16*GROUPING(A) + 8*GROUPING(B) + 4*GROUPING(C) + GROUPING(D)) as X

Enthusiast

Re: Oracle to Teradata SQL Conversion challanges

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

Highlighted
Teradata Employee

Re: Oracle to Teradata SQL Conversion challanges

Did you include GROUP BY ROLLUP / CUBE / GROUPING SETS?