Results from ExecuteSQL differ from direct DB query

[eljefejb]

Hello,

 

I am wondering if anyone can explain why I get different results for the same query string between using the ExecuteSQL function in FM versus querying the database through a database browser (I'm using DBVisualizer).

 

Specifically, if I run

SELECT COUNT(DISTINCT IMV_ItemID) FROM IMV

in DBVis, I get 2802.  In FileMaker, if I evaluate the expression

ExecuteSQL ( "SELECT COUNT(DISTINCT IMV_ItemID) FROM IMV"; ""; "")

then I get 2898.  This makes me distrust the ExecuteSQL function.  Inside of FM, the IMV table is an ODBC shadow, connected to the central MSSQL database.  In DBVis, the application connects via JDBC.  However, I don't think that should make any difference.

 

Any ideas why I get a different count for each method?

 

 

[Wim Decorte]

No idea, but I would get the output of the DISTINCT without the count and do a diff on both lists then inspect the differences (doing searches for those that are in FM's DISTINCT list but not in the other)

[eljefejb]

Wim, you're a genius.  I wouldn't have thought to use the command-line diff tool.  It would appear that FileMaker preserves leading and trailing whitespace, and counts "ABCD" and "ABCD  " as two separate values, whereas DBVisualizer counts both of those as one distinct value.  So, changing the query to

ExecuteSQL ( "SELECT COUNT(DISTINCT(LTRIM(RTRIM(IMV_ItemID)))) FROM IMV"; ""; "")

now yields the correct count in both applications.  Nice!

[mr_vodka]

Did TRIM not work for you?

 

SELECT COUNT(DISTINCT(TRIM(IMV_ItemID))) FROM IMV

[eljefejb]

Did TRIM not work for you?

 

SELECT COUNT(DISTINCT(TRIM(IMV_ItemID))) FROM IMV

 

When I looked to see if SQL Server had a TRIM() function, I read that it doesn't exist.  Using LTRIM() and RTRIM() was the listed workaround.

[Rick Whitelaw]

I believe John was referring to the FM Trim function. To be nested within the Execute SQL function.

[mr_vodka]

Actually I did mean the SQL TRIM function.

 

eljefejb, did you try it?

[eljefejb]

No, the database is an older flavor of MS SQL.  It doesn't have a TRIM() function.  I found a reference to TRIM() in a SQL Server 2012 document from Microsoft, but even at the bottom of their own document, it says that TRIM() is not supported.  Go figure. 

[mr_vodka]

Ah my mistake... Didnt realize until re reading your OP that you were using an ESS table. On that note, I did want to add something else. My understanding of it, using ExecuteSQL on ESS tables is not ideal as it would be translating it twice. SQL call from the ESS table which we do not control and then the SQL from ExecuteSQL.

[eljefejb]

What would be the proper way to do it?  I don't seem to be able to use the ExecuteSQL function unless there is an occurrence of the external table in the relationships graph.  Maybe I'm not fully understanding how it works, because it sounds like I should be able to use the function against FileMaker local tables, but it only gives me a "?" value when I try.

 

Thanks in advance, for helping me expand my FM knowledge.