KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: Multiple ways of Referencing a Boolean in SQL code
PRODUCT: 4D | VERSION: 11 | PLATFORM: Mac & Win
Published On: July 9, 2009
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

When writing SQL code, you may find yourself needing to reference a Boolean field. Here are three different approaches of writing code that all reference both True and False using SQL.

The first approach uses 4D process variables set to True and False; then in SQL code, those process variables are referenced as the true and false values:

  ` using a process variable
C_BOOLEAN(false_b;true_b)
false_b:=False
true_b:=True
Begin SQL
SELECT *
FROM Table_1
WHERE (
      Field_2 = :true_b AND
      Field_3 = :false_b
      )
INTO :LBox1

End SQL


The second approach uses the SQL function CAST to cast a number to a boolean value.
  ` using CAST with a number
Begin SQL
SELECT *
FROM Table_1
WHERE (
      Field_2 = CAST(1 AS BOOLEAN) AND
      Field_3 = CAST(0 AS BOOLEAN)
      )
INTO :LBox2

End SQL

The above snippet of code uses the CAST function in SQL to CAST the number 1 as True and CAST the number 0 as False.

The third approach also uses the SQL function CAST, but this example uses a string instead of a number.
  ` using CAST with a string
Begin SQL
SELECT *
FROM Table_1
WHERE (
      Field_2 = CAST('true' AS BOOLEAN) AND
      Field_3 = CAST('false' AS BOOLEAN)
      )
INTO :LBox3

End SQL

The above snippet of code uses the CAST function in SQL to CAST the string 'true' as True and CAST the string 'false' as False.

Each of these examples shows a different way of returning records which are "True" for Field_2 and "False" for Field_3.

See Also:
Commented by Tim Penner on July 15, 2009 at 4:57 PM
Cast can be a very useful SQL expression. More information on it's uses can be found here: http://www.4d.com/docs/CMU/CMU18438.HTM