This discussion is archived
4 Replies Latest reply: Feb 7, 2013 1:59 PM by 988139 RSS

Help with this query using APEX_ITEM

988139 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points