Recursive sql with Left outer join in Teradata

Question

I have a query that is using Table1 and Table2 with left outer join on 'usage'. Now i have to join that query with the Table3 with (I guess recursive sql) to generate the 'Resulting table'.

I saw lot of examples on recursive sql, but didnt find any thing that is using left outer join. Now my existing query is like this

     select PIN,  startDt,  StartTm,  usage,  Min 
     from Table1 t1 left Outer join Table2 t2 on t1.usage= t2.usage;

How can i do the Table3 with this query, so that ratGrp will be in comma separated way? Please help!!

     Table1
     PIN  startDt  StartTm  usage  Min
     -----------------------------------------
     123 08/03/2014 12:12:00 500  4567
     234 08/04/2014 12:12:00 200  4568
     .....
     Table2
     1stCol  2ndCol  usage
      ------------------------
     abc        234      500

    Table3
    PIN   ratGrp
    -----------------
    123   3300
    123  100
    123  103
    234  3300
    234  550

    Resulting table
    PIN startDt  StartTm  usage  Min  ratGrp
    -----------------------------------------------
    123 08/03/2014 12:12:00 500  4567   3300,100,103
    234 08/04/2014 12:12:00 200  4568   3300,550

Read http://stackoverflow.com/questions/19595182/sql-concatenate-multiple-rows — Sathish, Aug 08 '14 at 04:44
@Sathish, Thanks for your replay. I might be wrong but i think volatile table is active till the session is active. we can't change that with in a span of session after it is created. the query that i am working on is a web page query. The query changes depending on the search criteria entered by the users. and there will be different search criteries in one perticular session, i might need different types of volatile table, which doesnt sound me the perfect solution. Please guide me if i am wrong. — user2436956, Aug 11 '14 at 14:41

Recursive sql with Left outer join in Teradata

0 Answers0