Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Truth Table

By Yousef Ekhtiari,

Background

As we all know, we use a logical operator in a WHERE clause. It means each of us are consciously or unconsciously familiar with propositional logic. In propositional logic, we only have two values for each variable: True or False (1 or 0), therefore, any logic statement can be analyzed using a table which lists all possible values of the variable: a Truth Table.

Since each variable can take only two values, a statement with "n" variables requires a table with 2^n rows which is difficult to construct when the number of variables are more than 4.

Truth Table

In this article I will show you how SQL can help to construct the Truth Table. In order to construct the Truth Table I wrote a stored procedure:

 
create PROC Usp_BuildTruthTable ( @variables xml,
                                 @expressions xml)
AS
DECLARE @docHandle int,
        @SELECT varchar(8000),
        @FROM  varchar(8000),
	    @SQL nvarchar(4000)
EXEC sp_xml_preparedocument @docHandle OUTPUT, @variables

SELECT @FROM=isnull( @FROM +char(13)
     +' cross join  (select cast(0 as bit)union all 
	  select cast(1 as bit) ) as '+value+'('+value+')'+char(13) 
	  ,  ' (select cast(0 as bit) union all select  cast( 1 as bit) ) as' 
	  +value+'('+value+')')
FROM OPENXML(@docHandle, N'/Variables/var') 
with (value  char(1)    )
EXEC sp_xml_removedocument @docHandle 

--constructing the Select Clause     
EXEC sp_xml_preparedocument @docHandle OUTPUT, @expressions

SELECT       @SELECT=isnull(@SELECT+' ,['+col+']='+col, '['+col+']='+col)
 FROM OPENXML(@docHandle, N'/expressions/exp') 
WITH (col VARCHAR(8000) '@val')  

    SET @SQL='select *,'+@SELECT+ '  from '+char(13) +@FROM
   EXEC( @SQL)
EXEC sp_xml_removedocument @docHandle 

As you can see it accepts two parameters which are declared as XML data type that is new to SQL Server 2005. If you are not familiar with the XML procedure which I used in the Usp_BuildTruthTable, you can refer to BOL documentations.

The main core of stored procedure is the statement "(select cast(0 as bit)union all select cast(1 as bit) )", which assigns all the possible values to the variables and the CROSS JOIN that is used to produce all possible combinations of variables. The @SELECT will evaluate the expression, and the logic behind this is simple: SQL Server has a bitwise operator, so it can evaluate bitwise expressions. This is the reason that I converted zero and one to the bit data type. There are 4 bitwise operators in SQL SERVER:

Operator Meaning
~ NOT
& AND
| INCLUSIVE OR
^ EXCLUSIVE OR

Now I want to show you how to use these bitwise operators in logical connectives:

Logical operator Expression Bitwise
Not P ~P
And P AND Q P & Q
Inclusive or P OR Q P | Q
Exclusive or P XOR Q P ^ Q
Implies P IMP Q ~P | Q
Equivalence P EQU Q ==( P IMP Q) & (Q IMP P) (~P | Q ) & (~Q | P )

You have to pass variables and expressions with the following format:


<Variables>
<var value="VAR1" />
.
.
.
<var value=" VARn" /> 
</Variables>


<expressions>
<exp val="EXP1" />
.
.
.
<exp val="EXPn" />
</expressions>

Note: you have to use "& amp;" (remove th space between the & and amp) instead of & or else you get the following error:

.Net SqlClient Data Provider: Msg 9421, Level 16, State 1, Procedure Usp_BuildTruthTable, 
Line 0
XML parsing: line 3, character 15, illegal name character

To test the stored procedure, run the following snippet:

exec usp_BuildTruthTable '<Variables> 
<var value="a" /> 
<var value="b" /> 
</Variables>'  ,
'<expressions>
<exp val="~a" />
 <exp val="a & amp; b" />
<exp val="(a | b)" />
<exp val="(a ^ b)" />
<exp val="(~a | b)" />
<exp val="(~a | b)& amp; (~b | a)" />
</expressions>   '

Note: Remove the space between the & and amp in the code.

Here is the result:


a     b     ~a    a & b (a | b) (a ^ b) (~a |b) (~a | b)& (~b | a)
----- ----- ----- ----- ------- ------- ------- ------------------
0     0     1     0     0       0       1       1
0     1     1     0     1       1       1       0
1     0     0     0     1       1       0       0
1     1     0     1     1       0       1       1

Let's check a complicated expression:

((P imp Q) and (Q imp R)) imp (P imp R) == ~ ((~P |Q) & (~Q | R)) | (~P | R)
We have three variables and an expression, so we invoke the stored procedure as follows:

exec usp_BuildTruthTable 
'<Variables>
<var value="P" /> 
<var value="Q" /> 
<var value="R" /> 
</Variables>' ,
'<expressions>
<exp val="~((~p |Q) & amp;  (~Q |R)) | (~P|R)" />
</expressions>   '

And here is the result:


P     Q     R     ~((~p |Q) &  (~Q |R)) | (~P|R)
----- ----- ----- ------------------------------
0     0     0     1
0     1     0     1
1     0     0     1
1     1     0     1
0     0     1     1
0     1     1     1
1     0     1     1
1     1     1     1


you can download the code from here
Total article views: 10611 | Views in the last 30 days: 1
 
Related Articles
FORUM

SSIS Variable Expressions

SSIS Variable Expressions

FORUM

Parameterized expression in a variable

ssis : Parameterized expression in a variable

FORUM

Expressions for Variables

Expressions

BLOG

Variables and Expressions with Connections in SSIS

Variables and expressions help make any SSIS package dynamic and flexible. It is always a best pract...

FORUM

Using Variables as column names in select statement

Pass @Variable and use in SELECT statement

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones