2 Replies Latest reply: May 9, 2013 11:12 PM by 1008134 RSS

    Select List from Query with row level Where clause

    1008134
      Hi all,

      I am trying to create a tabular form based on a SQL query, that has a query-based select list with a where clause that references a column in the originating SQL query.

      The situation is, I have a table that stores client_id, source_id and build_id, lets call it client_source. I have a second table, build_source, that contains source_id and build_id, with a one to many relationship between the two (source_id of 1 could have build_id of 1-7).

      Using a tabular form, I want to select the corresponding build_id to be used in client_source, but the select list must only contain the build_id's for that rows particular source_id.

      Here is an example of the SQL source of my tabular form;

      select
      s.ROWID,
      s.CLIENT_ID,
      s.SOURCE_ID,
      APEX_ITEM.SELECT_LIST_FROM_QUERY(1, s.BUILD_ID,
      'select b.build_id display, b.build_id return from
      build_source b where b.source_id = s.SOURCE_ID ') lst
      from client_source s

      ... what I am trying to achieve is that the source_id fields in bold match. When the query is built this way I get an 'invalid identifier' Oracle error on s.SOURCE_ID at runtime.

      Is there some special tags that need to be used to reference the outside column? I must be missing something because this seems like a rather trivial problem.

      I am running on Application Express 4.1.0.00.32, on a Oracle 10g release 10.2.0.4.0 database.


      I look forward to any helpful replies!


      Cheers,
      Jason

      Edited by: 1005131 on May 9, 2013 7:02 PM