Has anyone else had difficulties with BOOLEAN type in MS SQL Server Databases?

We have a Boolean column that has True and False as values when you look at the data.

We use the field to filter the data.

Accessing the table externally results in a large number of the records not filtering correctly according to the field contents.

Importing the table results in the filter working correctly.

We are using Tibco Spotfire 7.6.

2 Comments

Thanks for the tip:

It is due to the External Query automatically limiting the results to the Top 10,000 results in the background.

Imported Table

CAST(CASE WHEN "dbo#InspectionFaultCategory"."Archived" > 0 THEN 1 ELSE 0 END AS BIT) AS "Archived", "dbo#InspectionFaultCategory"."ParentInspectionFaultCategoryID" AS "ParentInspectionFaultCategoryID" FROM "dbo#InspectionFaultCategory" AS "dbo#InspectionFaultCategory"'

External Table

SqlServer query: SELECT  TOP(10000) CAST(CASE WHEN [InspectionFaultCategory].[Archived] > 0 THEN 1 ELSE 0 END AS BIT) 

Shame, it is much faster using the external link because it does not load the data when the analytic is loaded.

a.murdock - Oct 06, 2017 - 3:26am ::

Hello,

Please enable debug logging in the Spotfire client and look for any difference between the queries fired for external vs in-db connections-

eg: When data table is external a query similar to this would be logged.It shows the query ,the parameter values as well as the type of parameters-

 

 

2017-10-04 23:19:00,071 [TF LowPriorityThreadPoolIdentifier] DEBUG Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection [(null)] - Running SqlServer query: SELECT  TOP(10000) [q1].[DISPLAY_NAME] AS [Column_1_1], [q1].[LAST_LOGIN] AS [Column_1_2], [q1].[LAST_MODIFIED_MEMBERSHIP] AS [Column_1_3], [q1].[LOCKED_UNTIL] AS [Column_1_4], [q1].[PASSWORD] AS [Column_1_5], [q1].[PRIMARY_GROUP_ID] AS [Column_1_6], [q1].[USER_ID] AS [Column_1_7], [q1].[USER_NAME] AS [Column_1_8], CAST(0 AS BIT) AS [Column_1_9], [q1].[DOMAIN_NAME] AS [Column_1_10], [q1].[EMAIL] AS [Column_1_11], [q1].[ENABLED] AS [Column_1_12], [q1].[EXTERNAL_ID] AS [Column_1_13] FROM (select * from users where enabled=@p1) AS [q1]
2017-10-04 23:19:00,071 [TF LowPriorityThreadPoolIdentifier] DEBUG Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection [(null)] - Parameters:
2017-10-04 23:19:00,071 [TF LowPriorityThreadPoolIdentifier] DEBUG Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection [(null)] - Name = 'p1'; Value = '0'
2017-10-04 23:19:00,071 [TF LowPriorityThreadPoolIdentifier] DEBUG Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection [(null)] - Added prepared statement parameter 'p1' of type 'Int'

Sayali Patil - Oct 04, 2017 - 11:22pm ::
+ Add a Comment

(1) Answer

Login