Jump to content

ExecuteSQL with conditional Arguments


This topic is 3236 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Is there a way for ExecuteSQL to only search provided arguments instead of searching for empty arguments?  

ExecuteSQL ( "SELECT id_contact FROM contacts WHERE contactFirstName = ? AND contactLastName = ? and contactDOB = ? and contactSSN = ? ; "" ; "" ; searchNameFirst ; searchNameLast ; searchDOB ; searchSSN )

I'm trying to write an ExecuteSQL statement where a user can provide a first name, last name, date of birth, and social security number that returns a list of contact ID's that match the provided arguments.  However sometimes DOB and SSN will not be available.  How would I write an ExecuteSQL statement that omits empty search criteria instead of searching for an empty field as in the query above?

Link to comment
Share on other sites

By creating a script that will loop through a set of passed parameters and construct a matching ExecuteSQL() call in a string.  Then use the Evaluate() function on the string...

If you were at Devcon last year you'll find an approach for this in my demo file.

 

Link to comment
Share on other sites

searchDOB would be the global field that the user is typing their search criteria into (same with all of the fields named searchXXX).  ccontactXXX is the field that contains the actual data being searched.

Link to comment
Share on other sites

The global field may be left empty if the user is unable to provide a SSN or DOB to search for.  However, not all Contacts will have a SSN or DOB available to be searched.

 

So either/or.

Edited by James Gill
Link to comment
Share on other sites

I would do it this way:

 

/*for example: SELECT id_contact FROM contacts WHERE contactFirstName='Tom' AND contactLastName='Ad' AND ( contactSSN='111-11-1111' OR contactSSN IS NULL )
*/
Let([
select = "SELECT id_contact FROM contacts" ;

where.name = "contactFirstName='" & contacts::searchNameFirst & "' AND contactLastName='" & contacts::searchNameLast & "'";

where.dob = "( contactDOB='" & contacts::searchDOB & "' OR contactDOB IS NULL )"  ;
where.dob = If ( not IsEmpty ( contacts::searchDOB ) ; " AND " & where.dob ) ;
 

where.ssn = "( contactSSN='" & contacts::searchSSN & "' OR contactSSN IS NULL )"  ;
where.ssn = If ( not IsEmpty ( contacts::searchSSN ) ; " AND " & where.ssn ) ;
 

sql = select & " WHERE " & where.name  & where.dob & where.ssn ;

result = ExecuteSQL ( sql ; "" ; "" ) 
];

result

)

Link to comment
Share on other sites

This topic is 3236 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.