doughemi Posted December 29, 2013 Share Posted December 29, 2013 The following ExecuteSQL statement returns the correct count: ExecuteSQL ( " SELECT count (*) FROM "Class Member Data" WHERE ClassID = ? " ; "" ; ""; 53) But when I add some additional qualifying conditions like ExecuteSQL ( " SELECT count (*) FROM "Class Member Data" WHERE ClassID = ? and Region = ? " ; "" ; ""; 53; 1) it incorrectly returns 0. What is the correct syntax? Link to comment Share on other sites More sharing options...
CreativeDataPro Posted December 29, 2013 Share Posted December 29, 2013 Hi Doug, What type of field (Text, Number, etc.) is "Region". You might need to wrap the 1 in quotes if it's a text field. Link to comment Share on other sites More sharing options...
doughemi Posted December 29, 2013 Author Share Posted December 29, 2013 Region is an unstored calculation field, result number (but I had already tried quoting it without any difference). The calculation is members::region, where members is a properly related table (and that's why the calc is unstored). I also tried using members::region directly in the sql statement, without success. Link to comment Share on other sites More sharing options...
CreativeDataPro Posted December 29, 2013 Share Posted December 29, 2013 I'm not seeing anything wrong with your syntax offhand, and the fact your query result is a zero and not a question mark tells me it's probably not syntax. So, how many members that are in class 53 actually have a region 1 value? That is what you're looking to count in this select statement, right? Link to comment Share on other sites More sharing options...
doughemi Posted December 29, 2013 Author Share Posted December 29, 2013 There are 3 records in which the Region value is 1. (I picked class 53 because it only has 26 records to make it easier to troubleshoot). Link to comment Share on other sites More sharing options...
doughemi Posted December 30, 2013 Author Share Posted December 30, 2013 Solved. The copy I was doing the testing on was old, and didn't have class 53 records. I was looking at the live db's values, but testing on the local copy. D-OH! Link to comment Share on other sites More sharing options...
CreativeDataPro Posted January 1, 2014 Share Posted January 1, 2014 Oh that's great news, man. Glad to hear you got it resolved b/c I was honestly stumped! Link to comment Share on other sites More sharing options...
Fitch Posted January 3, 2014 Share Posted January 3, 2014 I see you've solved it, but I wanted to add that when I'm SQL troubleshooting, one of the first things to check is reserved words. I had tables named Language and Translation that tripped me up, for example. "Region" wouldn't have surprised me as reserved. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3771 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now