Database filter

Posted by Thomas Suijkerbuijk on 22-Sep-2015 04:56

I have a rulesheet in which I liked to use a database filter.

The attribute on which the filter takes place is a "boolean" in Corticon.

In the database this is a "bit" attribute.

When I select the database filter option I get this error: "filter expression cannot be processed by database".

 

The mapping is not giving any errors, and I cannot find a work around.

Has anyone found a solution to this problem?

 

 

Extra:
Same problem in SQL 2012

Corticon - Forum
Posted by mparish on 23-Sep-2015 13:51

 
Instead of your current filter
Corticon does accept this
Since CC.CC_Test is Boolean you don’t actually need to say =T
However when I ran a test I got this error:
 
But that appears to be invalid SQL:
SQL is happy with this syntax
But that’s not what Corticon appears to be creating..
Subsequently I found this in the docs:
So it appears you cannot do what you want to.
Most likely you will need to change the datatype.
Mike
 

All Replies

Posted by James Arsenault on 22-Sep-2015 06:49

What database are you using?
 

Posted by Gertjan Hendriks on 22-Sep-2015 08:43

Hi James,

Thomas is using SQL Server 2014. And yes, it's not officially supported by Corticon EDC 5.5. So the question should perhaps be: Is this typical behaviour for Corticon 5.5 and SQL Server 2014? In other words: Will this filter-issue not occur with SQL Server 2012 (and earlier)?

Thank you,

Gertjan

Posted by bblais on 22-Sep-2015 21:13

Was this a database that was previously using an earlier version of SQL Server? If so we ran into some issues similar to this, the fix was to clear the database metadata and re-import it. I would suggest doing this and seeing if the problem persists.

Posted by Thomas Suijkerbuijk on 23-Sep-2015 02:41

Tried to clear the metadata and re-imported it, sadly no change. Same error on database filter

Posted by Thomas Suijkerbuijk on 23-Sep-2015 04:37

[View:/cfs-file/__key/communityserver-discussions-components-files/14/CC_5F00_Test.zip:50:50]

Uploaded database backup and project zip in one zip file

Posted by mparish on 23-Sep-2015 13:51

 
Instead of your current filter
Corticon does accept this
Since CC.CC_Test is Boolean you don’t actually need to say =T
However when I ran a test I got this error:
 
But that appears to be invalid SQL:
SQL is happy with this syntax
But that’s not what Corticon appears to be creating..
Subsequently I found this in the docs:
So it appears you cannot do what you want to.
Most likely you will need to change the datatype.
Mike
 

Posted by Thomas Suijkerbuijk on 30-Sep-2015 02:40

Mike,

Thanks for answering.

Not really the answer is was hoping for.

Change the datatype in this production enviroment will lead to many problems. so I will have to look for a different solution.

@progress, will this change in the future. Will boolean datatype be supported by query filters?

Posted by Harold-Jan Verlee on 30-Sep-2015 02:57

Would it be possible to implement the field in your vocab as a custom data type whereby the labels are Yes/No (so used in rule modelling) and the value refer to an integer value as in your DB (0/1)?
 
Best regards,
Harold
 +31 654 217 073
 

Posted by Thomas Suijkerbuijk on 30-Sep-2015 08:26

Hello Harold-Jan,

Problem then is that Corticon identifies the datatype from database as a bit and the custom dataype as an integer.

Leading to this warning:  

"Attribute data type integer cannot be mapped to database type bit. Supported database types: {int, smallint, tinyint, bigint, etc}"

This thread is closed