Where clause

Permanently deleted user

13 December 2016 18:37

I'm attempting to modify the SearchParcelsOwner workflow and am having trouble getting the where clause to work properly. The goal is to have it narrow down the results by first name, last name and street name. I get results for first and last names, but not by street name. Here are the 2 queries I've tried:

 

"(UPPER(given1) LIKE UPPER('" & firstname & "%') AND UPPER(surname1) LIKE UPPER('" & lastname & "%') OR UPPER(given2) LIKE UPPER('" & firstname & "%') AND UPPER(surname2) LIKE UPPER('" & lastname & "%')) AND UPPER(street_name1) LIKE UPPER('" & streetname & "%')"

 

"(UPPER(street_name1) LIKE UPPER('" & streetname & "%')) AND (UPPER(given1) LIKE UPPER('" & firstname & "%') AND UPPER(surname1) LIKE UPPER('" & lastname & "%') OR UPPER(given2) LIKE UPPER('" & firstname & "%') AND UPPER(surname2) LIKE UPPER('" & lastname & "%'))"

 

Can anyone point out my error?

0

• 

  Permanently deleted user

  13 December 2016 20:02

  Ok, I just responded, but just as I hit 'Answer' saw an error.  So let's try this again.

   

  I think you want something like:

   

  check - firstname like given1 AND lastname like surname1

   

  or check - firstname like given2 AND lastname like surname2

   

  and when those evaluate true, check for - streetname like street_name

   

  So something like this (formatted for readability):  ( ( UPPER(given1) LIKE UPPER('" & firstname & "%') AND UPPER(surname1) LIKE UPPER('" & lastname & "%') ) OR ( UPPER(given2) LIKE UPPER('" & firstname & "%') AND UPPER(surname2) LIKE UPPER('" & lastname & "%') ) ) AND UPPER(street_name1) LIKE UPPER('" & streetname & "%') Don't know if this is easier to read, but less typeing of the variable names. String.Format("((UPPER(given1) LIKE UPPER('{0}%') AND UPPER(surname1) LIKE UPPER('{1}%')) OR(UPPER(given2) LIKE UPPER('{0}%') AND UPPER(surname2) LIKE UPPER('{1}%'))) AND UPPER(street_name1) LIKE UPPER('{2}%')",firstname,lastname,streetname)

   

  It is untested, so sorry if it doesn't work, but should evaluate or at least get you closer to an answer?

   

  Marshall

   

   

  0
• 

  Permanently deleted user

  13 December 2016 22:15

  It's still ignoring street name for some reason. Some users want to search by last name and street name if they don't know a first name. I just don't understand why it's totally disregarding the street name part of the clause...  

  0
• 

  Permanently deleted user

  14 December 2016 13:20

  OK, the query that I provided will only return a value if both the firstname and lastname are provided and match both the given1 and surname1 or given2 and surname2 respectively.

   

  If you want to return results for anything matching any field, you'll want to do an or on all the name fields:

   

    ( UPPER(given1) LIKE UPPER('" & firstname & "%') OR UPPER(surname1) LIKE UPPER('" & lastname & "%') OR UPPER(given2) LIKE UPPER('" & firstname & "%') OR UPPER(surname2) LIKE UPPER('" & lastname & "%') ) AND UPPER(street_name1) LIKE UPPER('" & streetname & "%')

   

  Just for troubleshooting, Is the query successful when you just search on street_name1? 

   

  UPPER(street_name1) LIKE UPPER('" & streetname & "%')

   

  Do you have a rest endpoint that I might be able to attempt the query on? and a sample search that you are trying to test on?

  0

Du måste logga in om du vill lämna en kommentar.