0 Replies Latest reply: Nov 22, 2012 4:00 AM by 883915 RSS

    setting sql for a column

      Hi, is there a way to set sql query for a column?

      the query should take value from column in the table.

      in my case, it has come down for a need to have a nested select query inside my query:

      select app.application_id,
      (SELECT LISTAGG(typ.verification_name, ', ') WITHIN GROUP (ORDER BY typ.verification_id) FROM
      typ,chk where
      chk.application_id = app.application_id
      GROUP BY app.application_id ) AS verification_type,
      from tablea,tableb where....
      <note that application_id is coming from the bigger query , aka another column from another table.>

      i've tried

      CAST ( EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)', "typ"."verification_name", ', ', "chk "."application_id ") AS CHARACTER ( 50 ))

      but my query need more than few EVALUATE_AGGR resulting in huge query in the log plus it's hard to put more details from other tables when you do this since everything buble down to the group by in the end of the query.

      pls help if there's something even close to this