|
|
|
Junior Member
 
Group: Forum Members
Last Login: 3/28/2008 5:02:56 PM
Posts: 13,
Visits: 8
|
|
|
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?
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/1/2008 2:49:57 PM
Posts: 6,
Visits: 44
|
|
|
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?
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 4/17/2008 5:16:02 AM
Posts: 14,
Visits: 18
|
|
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
Jagdish Kumar
|
|
|
|