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
dfn48Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mankowitzCommented:
First, why are you using a union when you can just merge your queries? Second, why are you separating out the TOP 10 from the main query. How does this work?

SELECT Select Top 10 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') OR (b.txn_type = 6 and b.chd  in ('M','W'))
  group by    substring(trim(a.us_at) from 1, 150)
  order by b.type desc, cnt desc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dfn48Author Commented:
I cannot use merge because I am migrating the code from db2 to teradata. I need to use union
0
mankowitzCommented:
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
0
PortletPaulfreelancerCommented:
>> 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Enterprise Software

From novice to tech pro — start learning today.