AnsweredAssumed Answered

SQL Like function using a variable

Question asked by user28897 on Nov 9, 2018
Latest reply on Nov 15, 2018 by user19752

Howdy folks,

 

I need a little help with trying to create a boolean function with SQL.

 

I have a table (BoA_Transactions) of bank deposits that I have populated with matching reservation codes in a field called Transactions.  This is a field that can contain 1 or more confirmation codes separated by commas for reservations that were deposited into the bank.  An example confirmation code would be HMMCWNBD84.  In another table called NightlyBookings I have all the bookings and their confirmation codes.   What I would like to do is search that table for bookings that have already been deposited into the bank.   So my thoughts were to create a query that returns the count of the confirmation code in BoA_Transactions by setting the confirmation code as a variable.

 

I can get my result if I do manually put in the code but can't quite figure out how to use the ? expression to pass the variable into it.  I know it has something to do with the tick around the code I imagine.   Here is my statement.

 

Let (

   [

code = "%" & NightlyBookings::ConformationCode & "%";

query = "Select Count (Transactions) from BoA_Transactions Where Transactions Like  ?  ";

result = ExecuteSQL ( $query; code ;"" ; "" )

 

   ];   $result

)

Outcomes