Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

ExecuteSQL not finding value of 0?

Discussion created by BruceHerbach on Jan 29, 2018
Latest reply on Jan 30, 2018 by okramis

Like • Show 0 Likes0
Comment • 13

I'm working on a script that should pull a set of primary keys when there is a match for 3 pieces of information. Here is he basic query:

SELECT a."ID"

FROM "Message" a

WHERE a."ID_Staff" = ? AND a."Mailbox_ID" = ? AND a."viewed" = ?

ID_Staff and Mailbox_ID are text fields and viewed is a number field.

When the values are:

ID_Staff = 10,

Mailbox_ID = 633 and

viewed = 1

it pulls the correct data set. When viewed has a value of 0 no records are returned. Going to the base table and doing a find with the same values finds the correct record set. So the SQL should work.

Is there something that should be different for number fields with a value of 0? Any suggestions on this would be greatly appreciated.

Outcomes

13 Replies

okramis Jan 29, 2018 3:04 PM
Are you sure the value is 0 and not empty NULL? If empty, you’d have to query for: and a.”viewed” IS NULL
Like • Show 0 Likes0
Actions
beverly Jan 29, 2018 3:04 PM
Bruce, carefully check if the field shows zero but is really empty in some way.
Thanks,
Beverly
Sent from miPhone
Like • Show 0 Likes0
Actions
BruceHerbach Jan 29, 2018 3:04 PM
I changed the field type for viewed from number to text and the query now works. So why can't I search for a numeric value of 0.

This may work as a solution. viewed is intended to be a binary value. 0 = not viewed and 1 = viewed.
Like • Show 0 Likes0
Actions
BruceHerbach @ beverly on Jan 29, 2018 3:07 PM
Beverly,

I have been spending more time on this then I should. Earlier I went through and made sure that the field had a value in all records of 1 or 0. Finally changed the field type to text and now the query works. It always worked with a value of 1.

So I'm still perplexed.
Like • Show 0 Likes0
Actions
beverly @ BruceHerbach on Jan 29, 2018 3:13 PM
I guess the next question is to see your calc. Are you using number in the parameters or quoted numbers?

; "" ; "" ; 1 ; "1" ; "0" )

Sent from miPhone
1 person found this helpful
Like • Show 1 Like1
Actions
philmodjunk Jan 29, 2018 3:52 PM
in SQL All of the following are different values:
"o" ; "O"; 0; "0"; and null(empty)

If your field is a text field, you need to use '0' (zero in quotes).
Like • Show 0 Likes0
Actions
BruceHerbach @ philmodjunk on Jan 29, 2018 6:55 PM
I have it working as a text field. With the field set as a number field, 1 would work, but 0 would not. For now it can stay a a text field.
Like • Show 0 Likes0
Actions
beverly @ BruceHerbach on Jan 29, 2018 7:36 PM
OK, but what is/was your full query? Were you passing the field names or actual value(s). Please post what didn't work, so we might explain the "why".
Beverly
Like • Show 0 Likes0
Actions
BruceHerbach @ beverly on Jan 29, 2018 9:48 PM
Beverly and all, I finally figured this out or at least most of it. I have been using/modifying a set of custom functions for the SQL. The short of it is I need to either increase the font or zoom in a bit. When looking for text, the value should be in single quotes. '0' but when looking for a number there should not be a quote.

The modifed custom function still had "' '" in it. Unfortunatly I wasn't seeing the single quote. When I zoomed in there it was. I updated the custom function and now it works correctly.

Thanks very much for the help.
Like • Show 1 Like1
Actions
BruceHerbach @ beverly on Jan 29, 2018 9:58 PM
Beverly,

Below is the original SQL. It started with Seedcode's execute SQL and add a dynamic WHERE clause. If the variables have a value then they are included in the where. If not then that portion is left out.

// Built by SQLExplorer. Compliments of SeedCode… Cheers!
Let ( [
// Define Carriage Return Substitution Character
ReturnSub = "\n" ;
// Enable the second line here if you want the header in your results
header = "";
//header = "a.ID";
// Define Table variables
aMESSAGE = Quote ( GetValue ( Substitute ( GetFieldName ( Message::ID ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

// Define Field Variables
aID = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Message::ID ) ; "::" ; ¶ ) ; 2 ) ) ;
aviewed = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Message::viewed ) ; "::" ; ¶ ) ; 2 ) ) ;
aID_Staff = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Message::ID_Staff ) ; "::" ; ¶ ) ; 2 ) ) ;
aMailbox_ID = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Message::Mailbox_ID ) ; "::" ; ¶ ) ; 2 ) ) ;

// Dynamic WHERE clause here...
$wha = ExeSQL_EQUAL_V_AND ( Message::ID_Staff ; $ID ) &
       ExeSQL_EQUAL_V_AND ( Message::Mailbox_ID ; $mboxID ) &
       ExeSQL_EQUAL_Num_AND ( Message::viewed ; $status );

$whb = ExeSQL_LIKE_V_OR ( Message::subject ; $subject ) &
       ExeSQL_LIKE_WV_AND ( Message::body ; $body );

$whc = ExeSQL_LIKE_WV_AND ( Message::subject ; $subject ) &
       ExeSQL_LIKE_WV_AND ( Message::body ; $body );

$wh = $wha & Case(
                IsEmpty ( $subject ); $whc;
                IsEmpty ( $body ); $whc;
                $whb );

// Build SQL Query
q =
"SELECT " & aID & "
FROM " & aMESSAGE &
If ( IsEmpty ( $wh ); ""; " WHERE " & $wh & " 1 = 1 " );
// WHERE " & aID_Staff & " = ? AND " & aMailBoxName & " = ? AND " & aviewed & " = ? " ;

// Run SQL Query
result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; "10" ; "INBOX.OfficeMail" ; "1" ) ] ;

// Clean up carriage returns
List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) ) )
--------------------------
The culprit was the custom function ExeSQL_EQUAL_Num_AND ( Message::viewed ; $status );
Here is the text
Case (
     IsEmpty( myValue ); "";
      "a." & SQL_F ( myFieldTarget ) & " = "    & myValue    & " AND ¶" )

The problem was two extra characters here.
Case (
     IsEmpty( myValue ); "";
      "a." & SQL_F ( myFieldTarget ) & " = "'    & myValue    & '" AND ¶" )

Which resulted in '0' instead of 0. In the small font on my screen I didn't see the extra ' in the custom function.

Again many thanks for the help. Your post got me to take another look at this and find the problem.

Bruce
1 person found this helpful
Like • Show 0 Likes0
Actions
beverly @ BruceHerbach on Jan 30, 2018 1:30 AM
Awesome! That’s why posting the full calc would help us help you spot the error.
Beverly
Sent from miPhone
Like • Show 0 Likes0
Actions
BruceHerbach @ BruceHerbach on Jan 30, 2018 6:18 AM
One last thing, the original source for the custom function came from a FileMaker Hacks article on dynamic portal filters. Here is the link.
FM/SQL Portal Filter + Dynamic Sort | FileMakerHacks

The custom functions in the example file have been a great help. My thanks to the author of this article Joel Englander and Kevin Frank for publishing it.

Now I just have to look into new glasses
Like • Show 0 Likes0
Actions
okramis Jan 30, 2018 7:51 AM
I built myself a set of custom functions for dynamic query building that Is field type aware, might be of interest. It’s used in my sample solution here: MasterDetailSQL.fmp12.zip

best
Otmar
2 people found this helpful
Like • Show 0 Likes0
Actions

ExecuteSQL not finding value of 0?

Outcomes

Related Content

Recommended Content