asked on

SELECT Top n * union query Teradata : sas access to teradata

my code is running forever. I want to select the top 10 uaw in a union sql using teradata but I am not getting any results nor any errors. Please help. Below is my code

- this is sas access to teradata

Create table agt_lst
(
Select Top 10 a.* from
(
SELECT substring(trim(a.us_at) from 1, 150) as uaw,
count(*) as cnt
from V.trc a
      inner join V.slt b
      on a.seon = b.seon
      where b.type = 38
      and b.chd = 'L'
group by substring(trim(a.us_at) from 1, 150)
union
SELECT substring(trim(a.us_at) from 1, 150) as uaw,
count(*) as cnt
from V.trc a
inner join V.slt b
on a.seon = b.seon
where b.txn_type = 6
and b.chd in ('M','W')

group by substring(trim(a.us_at) from 1, 150)
) a
      order by a.cnt desc

ASKER CERTIFIED SOLUTION

mankowitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dfn48

ASKER

I cannot use merge because I am migrating the code from db2 to teradata. I need to use union

mankowitz

the sql should be the same regardless. But go ahead and use union. If you are not getting any errors, you should show the following:

1. What you are getting
2. What you want to get
3. What you start with

PortletPaul

>> cannot use merge because I am migrating the code from db2 to teradata.
but your sql shows you are seeking data from the same tables

FROM V.trc a
INNER JOIN V.slt b ON a.seon = b.seon

>> I need to use union
If the data is from the same tables how is union relevant? Perhaps you haven't provided all the details, but IF unioning is relevant I'd suggest this.

SELECT TOP 10
       uaw
     , sum(cnt) AS cnt
FROM (
     SELECT substring(trim(a.us_at) FROM 1, 150) as uaw
     FROM V.trc a
     INNER JOIN V.slt b ON a.seon = b.seon
     WHERE ( b.type = 38 AND b.chd = 'L' )
           
     UNION ALL
     
     SELECT substring(trim(a.us_at) FROM 1, 150) as uaw
     FROM V.trc a
     INNER JOIN V.slt b ON a.seon = b.seon
     WHERE ( b.txn_type = 6 AND b.chd IN ('M', 'W') )
     ) a
GROUP BY uaw
ORDER BY a.cnt DESC
;

Open in new window

Note in your current query it is possible you would get an inaccurate count. If the upper and lower subqueries have produce equal rows, only one row is retained

e.g.

upper query
uaw cnt
abcdefg 20

lower query
uaw cnt
abcdefg 20

after union:
abcdefg 20

but the true count should have been 40

i.e. IF unioning is relevant, then you should be using UNION ALL