12 Replies Latest reply on Dec 28, 2012 1:19 AM by liltbrockie

    Help with ExecuteSQL query please

    liltbrockie

      Hi can someone help with this SQL query please? I am trying to retrieve a list of matching addresses based on what the user enters:

       

      ExecuteSQL ( "

       

      SELECT UPPER(addressid)

       

      FROM AddressSurname

       

      WHERE

       

      UPPER(surname) LIKE ? and

      UPPER(firstname) LIKE ? and

      UPPER(postcode) LIKE ?

      "

       

      ; "" ; "";

      Upper(Enquiries::Surname&"%");

      Upper(Enquiries::Firstname&"%");

      Upper(Enquiries::Postcode&"%") )

       

      The above code kind of works but it does not account for fields that are NULL

      The problem being if I enter in "Smith" for the surname I ONLY get results if there is date in the firstname field (which there often isnt)

      I have attached a demo file

        • 1. Re: Help with ExecuteSQL query please
          beverly

          Which fields are allowed to be NULL?

          If any of them, use OR instead of AND.

          If surname must match and the others are allowed tone NULL the query can be a combination of AND & OR, testing for NULL if desired.

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          • 2. Re: Help with ExecuteSQL query please
            liltbrockie

            All fields are allowed to be NULL..I have changed the ands to ors but its still not working properly. I've got a feeling this is going to be a very long sql statement if I want it to work on all the fields!

             

            ( I have updated my original post to correct wrongly uploaded file first time round)

            • 3. Re: Help with ExecuteSQL query please
              beverly

              I downloaded your sample database and the query is working for me. It is matching on OR (meaning any of these match). If you want to match all, the query needs to be "AND" not "OR".

               

              what do you mean by "not working properly"? what results do you expect to see?

              Beverly

              • 4. Re: Help with ExecuteSQL query please
                beverly

                Perhaps this is what you meant?

                 

                WHERE

                     ( UPPER(surname) LIKE ? or surname is NULL )

                     AND ( UPPER(firstname) LIKE ? or firstname IS NULL )

                     AND  ( UPPER(postcode) LIKE ? OR postcode IS NULL )

                 

                 

                Beverly

                • 5. Re: Help with ExecuteSQL query please
                  liltbrockie

                  Hi Beverly.. thanks for your help on this by the way!

                   

                  See attached file again

                   

                  On the first record, where surname is "kat" and first name is "a" i would expect number of results to be 2 and the portal just show the 2 entries where surname is kat and first name is "a"

                   

                  At the moment its wrong as its showing 5 results

                  • 6. Re: Help with ExecuteSQL query please
                    beverly

                    OK, then perhaps you want a filtered portal which you can constrain with If and/or Case statements rather than with ExecuteSQL.

                     

                    How many of the fields from the form above the portal do you think you will eventually use? all of them? If any of them are to "constrain", then the filtered portal would work well.

                     

                    You may also be able to use multiple ExecuteSQL() calls to constrain based on which fields have data in the form. Assign each query in a LET statement and use the previous results as part of the next query (with IN for the list of ID numbers). But that does seem like a lot of work!

                     

                    Perhaps others have suggestions?

                    Beverly

                    • 7. Re: Help with ExecuteSQL query please
                      liltbrockie

                      :-(... i am coming from a filtered portal solution... I had to abandon that as it was too slow with 50,000+ addresses to sort through every time! I thought I was ontosomethign with using executeSQL but it seems I'm a little premature in my celebrations!

                       

                       

                      Any others with advice im all ears!

                      • 8. Re: Help with ExecuteSQL query please
                        liltbrockie

                        I suppose I could do a massivelylong case statement that looks to accounts for all the possible combination of fields that are filled in.

                        • 9. Re: Help with ExecuteSQL query please
                          Shauniedarko

                          If you remove the "or surname is NULL" from your statement, you'll find that you come up with the two records as expected for Kat a.  However, that creates a problem when that field actually is null.  Maybe you should set up a case statement with SQL statements embedded.  If the field is NULL, use the NULL statement.   That might not work if you have lots of fields, but if you're only working with the firstname or last name, that wouldn't be a long case statemtn.

                          • 10. Re: Help with ExecuteSQL query please
                            beverly

                            Try something like this (I had to make the addressID a number field to work):

                             

                            Let (
                            
                               [ $query = " 
                                  SELECT A.AddressID
                                  FROM AddressSurname A
                                  WHERE A.AddressID > 0 "
                               ; $s = If ( IsEmpty ( Enquiries::Surname ) ; "" ; " AND UPPER(A.Surname) LIKE '" & Upper(Enquiries::Surname) & "%' " )
                               ; $f = If ( IsEmpty ( Enquiries::Firstname ) ; "" ; " AND UPPER(A.Firstname) LIKE '" & Upper(Enquiries::Firstname) & "%' " )
                               // repeat for each field in your search
                               ; $result = $query & $s & $f 
                               ]; ExecuteSQL ( $result ; "" ; "" )
                            )
                            

                             

                            Build the query to change - add an AND if the query field is not empty. Then execute the built query. The above worked to return the two records that OP wanted, BTW. Yes, you don't have the ? for the parameters, but that would be more difficult.

                             

                            Beverly

                            • 11. Re: Help with ExecuteSQL query please
                              Shauniedarko

                              That's a really nice solution!

                              • 12. Re: Help with ExecuteSQL query please
                                liltbrockie

                                Amazing Beverly. thank you so much X