4 Replies Latest reply: Mar 25, 2014 6:18 AM by vballeda RSS

    How to user like operator  with bind variable in Apex LOV

    vballeda

      Hi Friends ,

       

      In my apex page I have two lovs . the first lov return value have to pass to the second lov and there i have a query like

       

       

      Select name disp , id ret  from emp where emp like '%:p1_first_lov%'

       

      this query is not working properly can some please help me about this

       

      I am using apex 4.1

       

       

      Thanks & Regards

       

      Vballeda

        • 1. Re: How to user like operator  with bind variable in Apex LOV
          fac586

          vballeda wrote:

           

          In my apex page I have two lovs . the first lov return value have to pass to the second lov and there i have a query like

           

           

          Select name disp , id ret  from emp where emp like '%:p1_first_lov%'

           

          this query is not working properly can some please help me about this

           

           

          Using the :p1_first_lov syntax makes this a bind variable, not a substitution string. It therefore will not be replaced if it occurs in a string literal. The substitution string syntax &P1_FIRST_LOV. could be used, but this is not recommended in SQL queries because of the danger of SQL injection. The wildcards should be concatenated to the bind variable:

           

          select name disp, id ret from emp where emp like '%' || :p1_first_lov || '%'

           

          Note that leading-edge wildcard predicates are not efficient when used with large datasets as they eliminate the possibility of using any available indexes. Why are you doing this? How large is your real table?

          • 2. Re: How to user like operator  with bind variable in Apex LOV
            vballeda

            Hi Friend ,

             

             

            Thank you so much for your kind response. Actual my requirement is I am getting so many files from different regions . so if I select region from the first LOV the corresponding files list I has to get in second lov.. the file name will contain the Region name so  now my query is

             

            select filename from file region_files where filename like '%P1_region_name%';

             

            and the data in this table max 5 lak records

             

            is there any other alternative this friend

             

             

            Thanks & Regards

             

            Vballeda

            • 3. Re: How to user like operator  with bind variable in Apex LOV
              fac586

              vballeda wrote:

               

              Thank you so much for your kind response. Actual my requirement is I am getting so many files from different regions . so if I select region from the first LOV the corresponding files list I has to get in second lov.. the file name will contain the Region name so  now my query is

               

              select filename from file region_files where filename like '%P1_region_name%';

              That's not a valid LOV query. There must be 2 columns in the projection. For it to work at all, the query must be like:


              select filename disp, filename ret from files where filename like '%' || :p1_region_name || '%'

               

              :p1_region_name cannot occur within a string literal.

              and the data in this table max 5 lak records

              This is an international forum. Please use internationally recognisable measurements and units. That's too many rows to be frequently performing a full table scan on. I'd question the data model. It looks like the files table should be properly normalized with a FK to the regions table, and the region key determined by parsing the filename when the file is added to the table. An index on the region FK column would then provide efficient access to the required rows.

              • 4. Re: How to user like operator  with bind variable in Apex LOV
                vballeda

                Hi Friend ,

                 

                Thank you so much .....

                 

                It is working fine now ...

                 

                 

                Thanks & Regards

                 

                Vballeda