10 Replies Latest reply: Feb 13, 2014 6:53 AM by rhisingh RSS

    Creating a where clause in the LOV query box

    rhisingh

      Hi guys is any able to help me with this one

       

      i have a page in my app where there is a LOV drop down now the values for this drop down comes from a table that looks like this

       

      users_for _form_TBL

       

      E_MAILVendor
      user1@1.comoracle
      user@1.comsap
      user@1.comIBM
      user2@2.comOracle
      user2@2.comintergraph

       

       

      So my syntax for the LOV i mentioned looks like this

       

       

      select VENDOR as display_value, VENDOR as return_value

        from form_user_tab where E_MAIL = :APP_USER

      order by 1 ;


      so if User1@1.com logs onto the app and has to make a selection on the LOV it would only display the vendors relative to his E_MAIL like oracle ,sap, IBM then he hits the submit button on the page then the vendor that he selected is inserted into another table .What i did on the insert table was create a trigger that after a vendor is inserted the trigger inserts a 1 into a column called assessed .

       

      What i did after this was add a select in the where clause of the LOV so that it would check in the insert table if that vendor had been chosen before and not display it again to be selected  my syntax the second time around in the SQL query box was

       

       

      select VENDOR as display_value, VENDOR as return_value

        from users_for _form_TBL where E_MAIL = :APP_USER

      and (select 'vendor' , 'ASSESSD' from inserted _tbl_imp is null);

      order by 1 ;

       

      But im getting an error any ideas guys .Thank you

        • 1. Re: Creating a where clause in the LOV query box
          fac586

          rhisingh wrote:

           

            select VENDOR as display_value, VENDOR as return_value

            from users_for _form_TBL where E_MAIL = :APP_USER

          and (select 'vendor' , 'ASSESSD' from inserted _tbl_imp is null);

          order by 1 ;

           

          But im getting an error any ideas guys .Thank you

          What error? Where does it occur?

           

          That is invalid SQL:

           

          select VENDOR as display_value, VENDOR as return_value 
            from users_for _form_TBL where E_MAIL = :APP_USER
          and (select 'vendor' , 'ASSESSD' from inserted _tbl_imp is null);
          order by 1 ;
          

           

          There are spaces in the table names in lines 2 and 3. This probably results in "invalid table name" errors.

           

          The statement is terminated by a semicolon (";") at the end of line 3, but is continued on line 4, which ends with another semicolon. (I don't think a terminating semicolon is necessary in an LOV query, and IIRC it will cause an error even with a valid query (or at least it used to).

          • 2. Re: Creating a where clause in the LOV query box
            rhisingh

            hay man thanks for your reply the syntax in really using is

             

            select VENDOR as display_value, VENDOR as return_value

              from users_for _form_TBL  where E_MAIL = :APP_USER

            and (select  vendor , ASSESSED from nserted _tbl_imp  where 'vendor' , 'ASSESSED' is null)

            order by 1 ;

             

            im still getting and error is the where clause allowed in the second select statement 

            • 3. Re: Creating a where clause in the LOV query box
              fac586

              rhisingh wrote:

               

              hay man thanks for your reply the syntax in really using is

               

              select VENDOR as display_value, VENDOR as return_value

                from users_for _form_TBL  where E_MAIL = :APP_USER

              and (select  vendor , ASSESSED from nserted _tbl_imp  where 'vendor' , 'ASSESSED' is null)

              order by 1 ;

               

              im still getting and error is the where clause allowed in the second select statement

              It still has basic syntax errors. You've posted 4 different "table names" so far, of which only that in the first query is valid. As can't see your database I have no idea what the names of these tables actually is, and to what extent getting these wrong is contributing to your problem.

               

              As far as I can tell you are looking for:

               

              select
                  u.vendor display_value
                , u.vendor return_value
              from
                  users_for_form_tbl u
              where
                  u.e_mail = :app_user
              and not exists (select null from inserted_tbl_imp i where u.vendor = i.vendor)
              order by
                  u.vendor
              

               

              but the table names could well be something else...

              • 4. Re: Creating a where clause in the LOV query box
                rhisingh

                Dude you are a star it works perfectly thank you thank you thank you . is there something that i could do for you send me a mail     rhine.singh@gmail.com       YOU ARE THE MAN !!!!!!!!!!!!!

                • 5. Re: Creating a where clause in the LOV query box
                  rhisingh

                  hay man so sorry to worry you again just that i came across abit of a problem . the code works fine when the first person using the app logs in and as he selects vendors the vendors he selects dont show up for him again which is fine. But when another user logs on and has to assess the same vendor that was previously selected by the first user  it doesn't show up for the current user to assess . I think because it already exists in the insert table  any idea what i could do here .The reason for having more the one person assess a vendor is to get more the one opinion on that vendor  

                  • 6. Re: Creating a where clause in the LOV query box
                    fac586

                    rhisingh wrote:

                     

                    hay man so sorry to worry you again just that i came across abit of a problem . the code works fine when the first person using the app logs in and as he selects vendors the vendors he selects dont show up for him again which is fine. But when another user logs on and has to assess the same vendor that was previously selected by the first user  it doesn't show up for the current user to assess . I think because it already exists in the insert table  any idea what i could do here .The reason for having more the one person assess a vendor is to get more the one opinion on that vendor

                    This requirement was not stated above. See The Tom Kyte Blog: How to ask questions. (This is what you could do for me: ask questions properly.)

                     

                    Have you thought about what you could do here? Have you tried anything yourself? It seems pretty obvious...

                    • 7. Re: Creating a where clause in the LOV query box
                      rhisingh

                      Hay man thanks for that info on the Tom Kyte Blog 


                      --(the code you gave me )

                       

                      select 

                          u.vendor display_value 

                        , u.vendor return_value

                       

                      from 

                          users_for_form_tbl u 

                      where 

                          u.e_mail = :app_user 

                      and not exists (select null from inserted_tbl_imp i where u.vendor = i.vendor) 

                      order by 

                          u.vendor 

                       

                      --(After the error i tried )Then when i got the error i try'd using 'or exists'  like this

                       

                      --(code)

                      where 

                          u.e_mail = :app_user 

                      and not exists (select null from inserted_tbl_imp i where u.vendor = i.vendor) 

                                            or exists (select null from inserted_tbl_imp i where u.vendor = i.vendor)

                      order by 

                          u.vendor 

                       

                      But the all the vendors that where in the  same in the insert table came up in the drop down list  example is oracle was assessed before then only oracle would appear in the drop down list  and if it was assessed 3 times it would come up 3 times

                       

                       

                      --Then i tried to select where 'Assessed' is null   ('Assessed' is a column in the insert table  which i created a trigger on to insert 1 in the 'ASSESSED' column  when a vendor is begin assessed  )  

                       

                      --(code)

                      where 

                          u.e_mail = :app_user 

                      and not exists (select 'ASSESSED' from inserted_tbl_imp i where 'ASSESSED' is null ) 

                                           

                      order by 

                          u.vendor 

                       

                      This try didnt return any vendors at all

                       

                       

                      --Then i tried distinct

                       

                      --(code)

                      where 

                          u.e_mail = :app_user 

                      and not exists (select distinct 'VENDOR',  from inserted_tbl_imp i where u.vendor = i.vendor) 

                                         

                      order by 

                          u.vendor 

                      This gave me a LOV ERROR

                       

                      This is what i have tried so far there where others syntax's i tried last night as well  but most of them came back as errors . Thanks again for you time and help  fac586  

                      • 8. Re: Creating a where clause in the LOV query box
                        rhisingh

                        Ok still trying with only errors give me a little clue is it a if statement  case statement or is it a loop maybe

                        • 9. Re: Creating a where clause in the LOV query box
                          rhisingh

                          Sir did you mean the answer is obvious or that you personally think that im not trying hard enough ?

                          • 10. Re: Creating a where clause in the LOV query box
                            rhisingh

                            Hi Mr fac586 I used e_mail = :app_user  in there

                            .....not exists (select distinct 'VENDOR',  from inserted_tbl_imp i where u.vendor = i.vendor and e_mail = :app_user )

                             

                            And it seems to be working fine . Thank you once again you where a great help