How the group by works in this instance?

Analytics
Enthusiast

How the group by works in this instance?

Hi,

Can any one explain me how the following query works?The following query is wokring fine in production but it failed in test after recent upgrade of teradata:

INSERT INTO DATABSE.SCGR01TABLE

 (ROW_NUM, country,code,number,account)

 SELECT

  ROW_NUMBER() OVER (ORDER BY ABD.account) AS ROW_NUM

 ,'Z'  AS country

 ,10  AS code

 ,ABD.number

 ,ABD.account

 FROM database.sometable  ABD

 WHERE NOT EXISTS

 (SEL *

  FROM masterdb.mastertable zzz

 WHERE abd.account = zzz.account

 AND zzz.code = 'z'

 AND zzz.code = 10

 GROUP BY abd.number)                    /*when you execute this query alone, it fails in prodn. but as a whole its works fine*/

 GROUP BY abd.nu,ber,abd.account     ;

Is it because the GROUP BY clause refers to the column of table which is not a part of the inner query? Not sure.

Any thoughts?

2 REPLIES
Enthusiast

Re: How the group by works in this instance?

I don't think you need a GROUP BY at all, certainly not in the WHERE EXISTS subquery. Your error is probably caused by selecting columns that are not listed in the outer GROUP BY. What was the exact error message you received?
Enthusiast

Re: How the group by works in this instance?

Hi gururaj,

The query means that select an account from ABD where respective account entry is not found in mastertable zzz.
I dont know how it got executed in earlier version as the query written is syntactically wrong
because sel * followed by group by will quit an error saying "3504: Selected non-aggregate values must be part of the associated group."

Just remove group by used in the where not exists clause

INSERT INTO DATABSE.SCGR01TABLE
(ROW_NUM, country,code,number,account)
SELECT
ROW_NUMBER() OVER (ORDER BY ABD.account) AS ROW_NUM
,'Z' AS country
,10 AS code
,ABD.number
,ABD.account
FROM database.sometable ABD
WHERE NOT EXISTS
(SEL *
FROM masterdb.mastertable zzz
WHERE abd.account = zzz.account
AND zzz.code = 'z'
AND zzz.code = 10)
GROUP BY abd.number,abd.account ;

Other way of writting the above query is:

INSERT INTO DATABSE.SCGR01TABLE
(ROW_NUM, country,code,number,account)
SELECT
ROW_NUMBER() OVER (ORDER BY ABD.account) AS ROW_NUM
,'Z' AS country
,10 AS code
,ABD.number
,ABD.account
FROM database.sometable ABD
WHERE abd.account NOT IN
(SEL zzz.account
FROM masterdb.mastertable zzz
where zzz.code = 'z'
AND zzz.code = 10)
GROUP BY abd.number,abd.account ;

thanks