2 Replies Latest reply on May 10, 2013 4:12 AM by 1008134

    Select List from Query with row level Where clause

      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 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, on a Oracle 10g release database.

      I look forward to any helpful replies!


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