0

Hi,

I have a requirement in Oracle using Decode function with Rownum. I need to convert this into Teradata. I need help in this. I tried using Top function and Rank qualify but unable to do the same. Please help me in this.

sample query in Oracle is:

SELECT DECODE(ROWNUM, 1, party, 2, party, 3, party, 4, party, 5, party,6, party, 7,party,'Others') party,
mnth,SUM(qty) FROM
(
SELECT NVL(trim(b.Party_name),'Others') party, c.MONTH_ID mnth, SUM(a.qty) qty 
FROM tablea a,tableb b,tablec c
WHERE a.col1 = b.col1 
AND a.col2 = c.col1
AND a.col3='Y'
GROUP BY party,mnth
ORDER BY SUM(a.qty) DESC
)
GROUP BY DECODE(ROWNUM, 1, party, 2, party, 3, party, 4, party, 5, party, 6, party, 7, party, 'Others'),mnth

Please help me the conversion of outer query in Teradata. Thanks in Advance.

Thanks and Regards, TDHelp.

flag

1 Answer

2

This should give you the same answer set

SELECT
  CASE WHEN row_num <= 6 THEN party ELSE 'Others' END party,
  mnth,SUM(qty) 
FROM
 (
  SELECT
    ROW_NUMBER() OVER (ORDER BY SUM(a.qty) DESC) AS row_num,
    COALESCE(TRIM(b.Party_name),'Others') party, c.MONTH_ID mnth, SUM(a.qty) qty 
  FROM tablea a,tableb b,tablec c
  WHERE a.col1 = b.col1 
  AND a.col2 = c.col1
  AND a.col3='Y'
  GROUP BY party,mnth
) dt
GROUP BY 1,mnth

Dieter

link|flag
Hi Thanks for the answer, I did the same way but sometimes my resultset matches with Oracle and in some runs my resultset doesnot match with Oracle. I have the same above query repeated for atleast 12 times with different date range using UNION. When I run each sql separately the resultset matches most of the time not all the times When I combine them with Union it is not getting the correctdata. Any inputs would help me. Thanks and Regards, TDHelp – TDHelp Feb 2 at 21:31
Can you share the SQL that you are using? Are you using UNION or UNION ALL? – Rob Paller♦ Feb 11 at 15:21

Your Answer

Get an OpenID
or

Not the answer you're looking for? Browse other questions tagged or ask your own question.