I have an ExecuteSQL statement that looks like this:

 

ExecuteSQL ( 

"SELECT DISTINCT( " & GFN(WT::Version) &")

FROM " & GTN(WT::Version) &"

WHERE "

 & GFN(WT::Version) &" <> 2

or "

 & GFN(WT::Version) &" <> 2"

;

""; "")

 

The output of which is:

1

3

4

5

 

If I remove the or statement it stops working and I get the ?.

 

Anyone have any ideas why that is?

 

 

Not Working Version:

 

ExecuteSQL ( 

"SELECT DISTINCT( " & GFN(WT::Version) &")

FROM " & GTN(WT::Version) &"

WHERE "

 & GFN(WT::Version) &" <> 2 "

;

""; "")

 

is "version" a text field or a number?  I'm guessing a text field in which case the 2 should be '2'

You can avoid this by passing in the 2 as a parameter to the Execute SQL like this:

 

ExecuteSQL ( 

"SELECT DISTINCT( " & GFN(WT::Version) &")

FROM " & GTN(WT::Version) &"

WHERE "

 & GFN(WT::Version) &" <> ? "

;

""; "" ; 2 )

Version is a number field.

 

I created a basic table with a number field and tried the same logic on that and it does work like it should and as you say. 

 

Do you know why the first part of the where is false, but the second would be true?

I've also tried passing it as a parameter with no luck. 

break out the SQL syntax into its own variable so that you can inspect it before firing the ExecuteSQL.

 

Not familiar with your GTN and GFN implementation but you may have to feed it something like 

GetFieldName( WT::version ) instead of the value of the WT::version field.

 

 

Thanks for the debug tip. I'll tinker with that for awhile to see if I can discover what's going on. 

 

GFN function is as follows and GTN is similar:

Let ( [ 

a = GetFieldName ( field ) ; 

b = Substitute ( a ; "::" ; ¶ ) 

] ; 

   Quote ( GetValue ( b ; 2 ) )

)  

I've done more testing and narrowed it down to one table that doesn't calculate SQL correctly.

 

Test 1: Works

     SELECT ID

     FROM TroubleTable

 

     Result: 1,2,3,4,5 

 

Test 2: Doesn't work

    SELECT COUNT(ID)

    FROM TroubleTable

 

    Result: ?

 

I then Copied the table and filled my copy with test data.

 

Test 1b: Works

     SELECT ID

     FROM TroubleTableCopy

 

     Result: 1,2,3,4,5 

 

Test 2b: Works

    SELECT COUNT(ID)

    FROM TroubleTableCopy

 

    Result: 5

 

So my question would be, what can cause a table to behave this way, but a copy of the table works as expected? Is it an indexing issue? Is it a possible sign of corruption? 

I copied one of the backups of the file from a month ago to see if anything has changed.. The SQL works as expected in the back up file. I double checked that the field definitions are the same as well. My final conclusion is that the table is corrupt. 

I made a clone of the file and put in a few test records and it works like it should!

File has been completely rebuilt and put back in place. Everything is back in working order. Only took me 30 minutes. Got to love that data separation model.