4 Replies Latest reply: Feb 7, 2013 3:59 PM by 988139 RSS

    Help with this query using APEX_ITEM

    988139
      Hi I already updated my handle but I guess it takes some time to the change to be reflected.

      I having trouble using the APEX_ITEM api, here is my query I'm trying to use it in an HTML Region with PL/SQL Anonymous Block. The objective is to dinamically generate a series of control that later will work as filters for a report.

      DECLARE
      CURSOR c_filter IS
      SELECT filter_name, filter_type FROM SEG_FILTER_TEST@dbexterna;
      BEGIN
      FOR a IN c_filter LOOP
      IF a.filter_type = 'LIST' THEN
      APEX_ITEM.SELECT_LIST_FROM_QUERY(3,ID_FILTER,'SELECT DISTINCT ID_FILTER FROM seg_filter_test@dbexterna');
      END IF;
      END LOOP;
      END;

      I don't understand how to pass an ID so I can reference it, but I cannot even create the select list it is showing ID_FILTER must be declared

      Thanks in advance
        • 1. Re: Help with this query using APEX_ITEM
          jariola
          Hi,

          Have you check APEX_ITEM.SELECT_LIST_FROM_QUERY document ?
          http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_item.htm#autoId15

          You have not declare variable ID_FILTER. Or should value come from cursor?

          Also you have mistake in LOV query you use for APEX_ITEM.SELECT_LIST_FROM_QUERY. LOV query need have two columns.

          Regards,
          Jari
          -----
          My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
          Twitter: http://www.twitter.com/jariolai
          • 2. Re: Help with this query using APEX_ITEM
            663055
            DECLARE
            CURSOR c_filter IS
            SELECT filter_name, filter_type FROM SEG_FILTER_TEST@dbexterna;
            BEGIN
            FOR a IN c_filter LOOP
            IF a.filter_type = 'LIST' THEN
            APEX_ITEM.SELECT_LIST_FROM_QUERY(3,a.filter_type,'SELECT DISTINCT ID_FILTER FROM seg_filter_test@dbexterna');
            END IF;
            END LOOP;
            END;

            The second parameter, is the default value you want your select list to have.

            Edited by: Raafje on Feb 7, 2013 2:00 PM

            Edited by: Raafje on Feb 7, 2013 2:00 PM

            Edited by: Raafje on Feb 7, 2013 2:01 PM
            • 3. Re: Help with this query using APEX_ITEM
              fac586
              MrCapuchino wrote:
              Hi I already updated my handle but I guess it takes some time to the change to be reflected.

              I having trouble using the APEX_ITEM api, here is my query I'm trying to use it in an HTML Region with PL/SQL Anonymous Block. The objective is to dinamically generate a series of control that later will work as filters for a report.
              It's not possible to create an HTML Region with an anoymous PL/SQL block. To create HTML content using PL/SQL, create a PL/SQL Dynamic Content region. (And note that whilst it's possible to use an anonymous block as the source for this region, it's much better practice to locate all PL/SQL in packages and only include calls to the package methods in the APEX region source.)

              Have you completed the <i>2 Day + Developer's Guide</i> to become familiar with the basic fundamentals of APEX?

              Always use <tt>\
              ...\
              </tt> tags as described in the FAQ when posting code.
              DECLARE
              CURSOR c_filter IS
              SELECT filter_name, filter_type FROM SEG_FILTER_TEST@dbexterna;
              BEGIN
              FOR a IN c_filter LOOP
              IF a.filter_type = 'LIST' THEN
              APEX_ITEM.SELECT_LIST_FROM_QUERY(3,ID_FILTER,'SELECT DISTINCT ID_FILTER FROM seg_filter_test@dbexterna');
              END IF;
              END LOOP;
              END;
              <tt>apex_item.select_list_from_query</tt> (and the rest of the <tt>apex_item</tt> methods) is a function returning a VARCHAR2 value. It cannot be used imperatively in this way: it must be used as an expression in a query, variable assignment, as part of a larger expression, or somewhere else a VARCHAR2 value can be used. Typically <tt>apex_item</tt> functions are used in report SQL queries or output as HTML in PL/SQL programs using the web toolkit <tt>htp.p</tt> method.
              I don't understand how to pass an ID so I can reference it, but I cannot even create the select list it is showing ID_FILTER must be declared
              What ID? Reference it how? What is "ID_FILTER"? The second parameter to <tt>apex_item.select_list_from_query</tt> is the current value assigned to the generated control.

              The SQL projection in APEX LOV queries must consist of 2 VARCHAR2 columns.
              • 4. Re: Help with this query using APEX_ITEM
                988139
                Hello,

                Thanks for your help.

                I solve it putting the code in a different way like this:
                DECLARE
                 CURSOR c_filter IS
                 SELECT id_filter, filter_name, filter_query, filter_type FROM SEG_FILTER_TEST@dbexterna ORDER BY 1;
                BEGIN
                 htp.p('<table width="300">');
                 FOR a IN c_filter LOOP
                 htp.p('<tr><td style="padding="10px">' || a.filter_name || '</td>');
                  IF a.filter_type = 'LIST' THEN
                    htp.p('<td style="padding="10px">' ||
                    apex_item.select_list_from_query(
                        2
                      , '%'
                      , a.filter_query
                       , 'style=""'
                       , 'YES'
                       , '%'
                       , ' '
                       , null
                       , 'label')
                       || '</td></tr>'    
                    ); 
                  ELSIF a.filter_type = 'DATEPICKER' THEN
                    htp.p('<td style="padding="10px">' ||
                    apex_item.date_popup2(
                      2,
                      null,
                      'DD-MON-RR',
                      12,
                      12,
                      NULL,
                      a.id_filter
                   )
                      || '</td></tr>' 
                   ); 
                  END IF;
                 END LOOP;
                END;
                {code}
                
                What I mean with the Id is how to access these dynamic controls later. I mean how can I use the value of the select list. I want this to use the created select list and make them cascading select lists and also to load the selected values and generate a report.
                
                Basically I need to generate dynamic cascading selects lists then use their values to generate a report.
                
                Thanks in advance