0

I would like to do this in teradata SQL/ MACRO or PROCEDURE :

CREATE MACRO insertloop ( val1 VARCHAR( 1000)) AS
(

 sublist_i  = ' SELECT sublist from table3  '

 FOR sublist_i in sublist :
   INSERT INTO table5 
      SELECT t.id, t.address, sum(t.amount)
      FROM table2 AS t
      WHERE 
              t.id in sublist_i
         AND  t.address = :val1
      GROUP BY t.id t.address
);

Explanation:

table3 contains list of id (by block of 1000 id)
   (12, 546, 999)
   (45,789)
   (970, 990, 123)

Main reason :

table2 is very huge (1 billion record).

A full join requires too much memory, we need to create a table table3 containing disjoint list of id and iterate on this list.

But, am not sure how to correct this MACRO to be make correct.

tensor
  • 3,088
  • 8
  • 37
  • 71
  • 1
    Can you share your actual query? I doubt that running an aggregate for each value is usefull (unless it's a high number of values and then your DBAs won't like looping). You could do a loop in a Stored Procedure, but you probably don't have rights to create one. Talk to your DBAs to get more spool space. – dnoeth Aug 30 '18 at 08:20
  • Join is not possible, so looping is best. – tensor Sep 02 '18 at 08:46
  • You can write a loop/cursor in Teradata Store Procedure. https://stackoverflow.com/questions/43587919/while-loop-in-teradata-procedure – Hassam Sep 22 '18 at 18:36

0 Answers0