12 Replies Latest reply: Nov 28, 2011 1:15 AM by 893978 RSS

    Select List-ALL

    893978
      Hi Experts,

      I’m using apex 4.0 version
      I have 2 regions in my page.
      1.html region which has select list(my_list), button item(Go)

      Select list gets values from LOV(TYPE)
      LOV query is like this…
      Select item d, item r from demo_items
      Union all
      Select 'ALL' d, 'ALL' r from dual

      When I run the page select list has values like this…
      Item1
      Item2
      Item3
      ALL


      2.chart region
      Chart query is like this………

      Select item from demo_items where item= :P1_my_list

      Based on select list value chart will generate.
      If user select’s only 1 value from select list and clicks on GO button above query works fine.


      But my requirement is…………..

      If user select 'ALL' from select list I need to pass 3 values(item1,item2,item3) to above query.
      For multi select I can use APEX_UTIL.STRING_TO_TABLE function(because item1,item2,item3 values are in demo_items table but ALL value is not in demo_items table)
      Please can anyone help me out in this……….how to pass select list value to query if select list’s selected value is 'ALL'

      Thanks in advance..
        • 1. Re: Select List-ALL
          21205
          Change your query to
          Select item from demo_items where (item= :P1_my_list or :P1_MY_LIST = 'ALL')
          • 2. Re: Select List-ALL
            893978
            Hi Alex,
            thanks for ur reply.. :)

            Select item from demo_items where (item= :P1_my_list or :P1_MY_LIST = 'ALL')

            *'ALL'* value is not in the demo_items table.

            'ALL' value is in LOV only from dual..
            i have to pass values(item1,item2,item3..... which are there in demo_items table)to chart query, if user select the 'ALL' as selected value in select list.
            can u help me on this???
            • 3. Re: Select List-ALL
              21205
              mn123 wrote:
              Hi Alex,
              thanks for ur reply.. :)

              Select item from demo_items where (item= :P1_my_list or :P1_MY_LIST = 'ALL')

              *'ALL'* value is not in the demo_items table.

              'ALL' value is in LOV only from dual..
              yes, I understand
              i have to pass values(item1,item2,item3..... which are there in demo_items table)to chart query, if user select the 'ALL' as selected value in select list.
              can u help me on this???
              you didn't try my query, did you?

              As I don't have your application, I will show you an example in SQL*Plus - also with a bind variable (called P31_ITEM)
              SQL> var P31_ITEM varchar2(10)
              SQL>
              SQL>
              SQL> exec :P31_ITEM := 'ALL'
              
              PL/SQL procedure successfully completed.
              
              SQL> select *
                2    from emp
                3   where ename = :P31_ITEM
                4     or :P31_ITEM = 'ALL'
                5  /
              
                   EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
              ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                    7369 SMITH      CLERK           7902 17-DEC-80        800                    20
                    7499 ALLEN      SALESMAN        7698 20-FEB-81       1621        301         10
                    7521 WARD       SALESMAN        7698 22-FEB-81       1250        501         30
                    7566 JONES      MANAGER         7839 02-APR-81       2975                    20
                    7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
                    7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
                    7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                    7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
                    7839 KING       PRESIDENT            17-NOV-81       5000                    10
                    7844 TURNER     SALESMAN        7698 08-SEP-81       1500        123         10
                    7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
                    7900 JAMES      CLERK           7698 03-DEC-81        950                    30
                    7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
                    7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
              • 4. Re: Select List-ALL
                893978
                hi Alex,
                thanks again.. :)
                u were right i didn't try it, I apologize for that..
                query is working fine.....but ..

                along with the select list value, I’m passing date range also for the chart query.
                output data is showing only based on 'ALL', it's not taking data range.
                Can u help me out??

                Thanks in advance for ur guidance.
                • 5. Re: Select List-ALL
                  618203
                  Hi, why don't you simply try this....
                  Create 3 items: PX_LOV_ELEMENTS, PX_FROM_DATE, PX_TO_DATE

                  PX_LOV_ELEMENTS: select list --> Extra values: NO --> Null Values: YES --> Null field: -- All -- --> Return null value: <leave blank>

                  PX_FROM_DATE and PX_TO_DATE : date picker

                  Your queries:
                  select <your select >
                  from <your table >
                  where (element_id = :PX_LOV_ELEMENTS or :PX_LOV_ELEMENTS is null)
                  and (date_element >= :PX_FROM_DATE or :PX_FROM_DATE is null)
                  and (date_element <= :PX_TO_DATE or :PX_TO_DATE is null)

                  Bye.
                  Gaetano.
                  • 6. Re: Select List-ALL
                    893978
                    hi alex,
                    i tried ur query,it seems like this query giving all the details..no filters it's taking


                    in this page
                    if i select supplier 'HP' ,date filter, and item 'ALL' it showing 'HP' as well as 'Dell' products also...

                    but i need only 'HP' products displayed


                    thanks in advance.. :)
                    • 7. Re: Select List-ALL
                      893978
                      Hi All,
                      can any one plzzz help me on this????

                      thanks in advance.. :)
                      • 8. Re: Select List-ALL
                        Joel_C
                        I think I know what the problem is - and it's not specifically to do with APEX.

                        Your query is as follows:
                        Select itemno
                               ,itemname
                               ,supplier
                          From demo_items
                         Where supplier = :p2_supplier
                           And purchase_date Between To_date (Nvl (:p2_frmdt
                                                                      ,To_char (Add_months (Sysdate
                                                                                           ,-6)
                                                                               ,'MM-DD-YY'))
                                                                 ,'MM-DD-YY')
                                                     And To_date (Nvl (:p2_todt
                                                                      ,To_char (Sysdate, 'MM-DD-YY'))
                                                                 ,'MM-DD-YY')
                           And itemname = :p2_listitem
                            Or :p2_listitem = 'ALL';
                        Logically speaking, the where clause of your query is equivalent to:
                           (supplier = :p2_supplier
                                   And purchase_date Between To_date (Nvl (:p2_frmdt
                                                                      ,To_char (Add_months (Sysdate
                                                                                           ,-6)
                                                                               ,'MM-DD-YY'))
                                                                 ,'MM-DD-YY')
                                                     And To_date (Nvl (:p2_todt
                                                                      ,To_char (Sysdate, 'MM-DD-YY'))
                                                                 ,'MM-DD-YY')
                                 And itemname = :p2_listitem)
                          OR (:p2_listitem = 'ALL')
                        or in terms of boolean logic:
                          (A.B.C)+D
                        Can you now see what the problem might be?
                        • 9. Re: Select List-ALL
                          893978
                          Hi joel,
                          thanks for ur reply..
                          it seems 'ALL' keyword can't apply for column level...

                          any one can help me on this??


                          if i select supplier 'HP' ,date filter, and item(single) it shows the output
                          but i need like this...

                          if i select supplier 'HP' ,date filter and item 'ALL'
                          it should show all the items from supplier 'HP' and based on that date filter..

                          thanks in Advance.. :)
                          • 10. Re: Select List-ALL
                            893978
                            Hi friends,

                            can any one help me on this issue??

                            thanks in advance.. :)
                            • 11. Re: Select List-ALL
                              Joel_C
                              mn123 wrote:
                              Hi joel,
                              thanks for ur reply..
                              it seems 'ALL' keyword can't apply for column level...
                              That's not what I said at all - I was hoping that you might see the problem for yourself, but alas...

                              Okay, if we were to express your where clause as it currently is in terms of a boolean logic, it might look something like this:
                               A.B.C+D 
                              where
                              A => supplier = :p2_supplier
                              B => purchase_date Between To_date (Nvl (:p2_frmdt
                                                                            ,To_char (Add_months (Sysdate
                                                                                                 ,-6)
                                                                                     ,'MM-DD-YY'))
                                                                       ,'MM-DD-YY')
                                                           And To_date (Nvl (:p2_todt
                                                                            ,To_char (Sysdate, 'MM-DD-YY'))
                                                                       ,'MM-DD-YY')
                              C => itemname = :p2_listitem
                              D => :p2_listitem = 'ALL'
                              You can see that, in the case of D being true (i.e. p2_listitem = 'ALL') this overrides the other parts of the where clause, because the OR [+] operator has lesser precedence than AND [.] (see http://en.wikipedia.org/wiki/Logical_connective#Order_of_precedence for further details). This is why your are returning all results, even if you supply a value for :p2_Supplier.

                              The solution is to explicitly de-mark predicates with parenthesis:
                               A.B.(C+D)
                              And thus your where clause now becomes:
                              supplier = :p2_supplier
                                         And purchase_date Between To_date (Nvl (:p2_frmdt
                                                                            ,To_char (Add_months (Sysdate
                                                                                                 ,-6)
                                                                                     ,'MM-DD-YY'))
                                                                       ,'MM-DD-YY')
                                                           And To_date (Nvl (:p2_todt
                                                                            ,To_char (Sysdate, 'MM-DD-YY'))
                                                                       ,'MM-DD-YY')
                                       And (itemname = :p2_listitem OR :p2_listitem = 'ALL')
                              Do you see the difference between the two? In fact, if you re-examine Alex's original post and a subsequent post by Gaetano, they explicitly de-mark the relevant predicates which use OR operators within parenthesis.

                              Edited by: Joel_C on 26-Nov-2011 14:56
                              • 12. Re: Select List-ALL
                                893978
                                HI Joel,

                                Thanks u so.. much.. :) :)
                                i was so confused ,i didn't think of so much on the syntax(i mean Parenthesis)
                                it's working now...
                                thank u once again.. :)