“With great power comes great responsibility.” Clark Kent's Father

The CFL functions ReturnSQLNumberValue() and ReturnSQLStringValue allow you to use a SQL query to retrieve a value from the database that might not otherwise be accessible. However, because of potential performance issues, it works differently than other CFL functions. [Steve Gillispie comments: The function has 2 parameters - (1) the string being passed to Sequel and (2) the “error result” if nothing is returned. As shown below, this second parameter is entered with a comma following the Sequel string and then a string value for ReturnStringValue and a number value for ReturnNumberValue.

The trick to making this function work is remember that you are constructing a string which must result in the exact string you would enter in a direct Sequel query; so it must match exactly the syntax Sequel requires. Missing single quotes, commas or separate words without white space between them will cause the function to fail.

You may insert a temporary variable you have declared such as Declare SKUNumber := PartByVariableName(“variable”).Part.SKU but in such a case, if a string value, you will need to surround it with single quotes as Sequel would expect in an actual query –E.g.“Where Part.SKU =” + “ ' ” + SKUNumber +“ ' ”. What is then parsed to Sequel is Where Part.SKU = '40808WMM' or whatever the SKU number is.]

For the syntax, see CFL Ch 3-Formulas and Functions#CFL%20Ch%203-Formulas%20and%20Functions-General%20Functions-SQL%20Functions.

[NOTE THAT THE SYNTAX HAS CHANGED TO RETURNSQLNUMBERVALUEWITHWAIT() AND RETURNSQLSTRINGVALUEWITHWAIT()..added 12/16/2018 by steve gillispie]

When you run a ReturnSQLNumberValue() or ReturnSQLStringValue(), Control knows it may take a while. Instead of locking everything up by waiting, it returns a value (the 2nd parameter) immediately so the computation can continue and other values can be computed. In the background, it then starts us a separate thread (process) to run the query. When a SQL value is finally returned, Control sets the variable value and then calls a refresh on any dependent variables. The SQL Value is then not recomputed until something in it's formula changes.

• You can really hurt your system performance if you use these without thought. A SQL lookup is thousands of times more work than a CFL computation. While Control tries to minimize the number of times the SQL is executed, you should still use the judiciously.
• Because the temporary value (2nd parameter) is returned immediately and the calculation is performed in the background, when you run these function in the formula debugger the first time you will get the 2nd parameter. If you wait a second or two and click the “Execute” button again, you will see the SQL response (or error if applicable) from the original execution.

Here is a simple example … to pull the last order and status for this company. Set the value of the variable to this equation:

ReturnSQLNumberValue( "Select max(OrderNumber) as MaxOrderNumber"
                                   + " from TransHeader"
                                   + " where TransactionType = 1"
                                   + " and ID < " + IF (Order.OrderNumber < 0) THEN "99999999" ELSE Order.IDAsString ENDIF
                                   , 0 )

Note: The IF statement is in the case when this order isn't save. In that case, the ID will be negative.

To pull the last OrderNumber plus the Status without having to run two queries, it might look like …

ReturnSQLStringValue( "Select top 1 'Order: '+Cast(OrderNumber as VarChar(10)) + ' Status: '+StatusText as OrderInfo"
                                   + " from TransHeader"
                                   + " where TransactionType = 1"
                                   + " and ID < " + IF (Order.OrderNumber < 0) THEN "99999999" ELSE Order.IDAsString ENDIF
                                   + " order by ID Desc"
                                   , "Temp Value" )

Retrieve the promotion rate currently assigned to an order by pulling the value from the SQL table. (Note: It may be easier to do this directly in CFL, but this example may help spur other ideas.)

This worked … almost:

   ReturnSQLNumberValue( "Select Promotion.PercentageOff from Promotion where ID = " + TransHeader.Promotion.IDAsString, 0 )

To get the ID of an object use the IDAsString function on the object that you want the ID of (the order's promotion in this case). However, when there is no promotion assigned TransHeader.Promotion.IDAsString will return “Not Assigned”. This will produce a SQL error because you are trying to test if the ID (a number) is equal to “Not Assigned”.

So then I tried this, which worked even when there was no promotion:

  //Embed the SQL function in CFL Code to determine if a promotion level is set (see below)
  IF TransHeader.PromotionName= "(Standard)" THEN  0
  ELSE
     ReturnSQLNumberValue( "Select Promotion.PercentageOff from Promotion where ID = " + TransHeader.Promotion.IDAsString, 0 )
  ENDIF

The ToString() function is also needed or when you DO have a promotion you'll be comparing a number to “Not Assigned” in the IF clause, which will produce an error. Since you can use ToString() with a number or string or date, it's a quick way to ensure that your comparisons are of the correct type.

Happy SQL!

Contributor: Cyrious Software

Date: 8/2011

Version: Control 4.5