databases recognize “empty strings” which is a string of zero length (dim x as string="")
and NULL which is NOTHING (not to be confused with a zero length string, which is “something”)
what happens if you say this
"SELECT COUNT (DISTINCT Pays) FROM Caritas WHERE (Pays <> '' and Pays IS NOT NULL) AND (Date_Out = '' OR date_out IS NULL)"
No, the line does not makes a difference. As an example, I have 4 entries in the report for Angola: 1 Date_Out is filled / 3 Date_Out are empty (or Null). If I expected an error, I would get 1 or 3, not 4 (1 + 3).
I wanted to use the EXPLAIN command (or EXPLAIN QUERY PLAN), but the explanation is so cryptic ! And my SQL for Dummies (by Allen G. Taylor) does not know about EXPLAIN.
EXPLAIN will not help with this problem… it is used to help tune long or complex queries, and usually you need to be a trained DBA to fully understand what they say… and to make matters worse… differnent database engines will EXPLAIN a query totally differently (in syntax as well as result).
I would suggest getting a database tool to view the data and see if it can tell you what is really in those fields.
visually for a text field, NULL, ‘’, ’ ’ etc will all LOOK the same
What is in the DB ?
I know. I can export it as text, display it in a Listbox / open it from a different application to inspect what is in the .sqlite file (all Tables, sqlite_master)
I also am able to import the text file from the exported text file.
Of course, I have the sql create command line.
I really know what is inside the data base.
What I do not know is how to achieve the goal I want to do.
May be time for me to go to bed. Maybe tomorrow will be a good day.
importing/exporting as text will tell you nothing, as it will still appear as a blank space… .what you do not know is if that blank area is a NULL, an empty string, a single space, or “n” spaces…
Does your SQL Create statement allow NULLS to be used (they are allowed by DEFAULT),
If you really know what is in the database, then you would be able to explain why the statement I provided doesn’t work for you, since it checks for both an empty string and a Null, leaving the only other possiblity that there is 1 or more spaces in the field.
If you want to send me the database (assuming its not too big). I’d be glad to analyze it and tell you what is off.
and yes EXPLAIN is a way to get information, but about a QUERY execution profile, not the data
Curious, why did you use Date_Out in the query, but mention Date_In here? Is it possible you’re using the wrong field in your query? I only ask because I’ve done it myself.
Dave:
I spend time to try to use the Terminal to open, return data from my .sqlite file.
I get two pipes (||) for each empty entry.
I also was able to make working some SELECT * FROM queries.
Now that some days ellapsed, I can start back and watch what error Ive made with the command.
I found that LIKE is nice in:
SELECT * FROM Caritas WHERE Date_Out NOT LIKE ''; // Exclude empty Date_Out (still present)
SELECT * FROM Caritas WHERE Date_Out LIKE '%2016%'; // Keep all Date_Out that were done in 2016
for examples.
I have to read carefully what my code do (how my code achieve what I want to do) and try to make changes.
PS: I left the Terminal when I added a report window with a TextField as a SQLite command text holder (I put SQLite string commands there and watch what the result(s) can be in a Listbox below (for searching porposes only.
Code can have to be refined.
That tells you NOTHING… a NULL shows as ||, “” shows as ||, and I’ll bet even 0x00 shows as ||
Ok… I won’t attempt to give you advice, since you do not wish to listen.
I do not really understand what you said. Listen is a different beast. When I ask advice(s), usually I listen to them.
Oh, you want to see the CREATE Statement ? Here it is:
SQLParm = "CREATE TABLE IF NOT EXISTS Caritas(ID INTEGER NOT NULL, PNom VARCHAR NOT NULL, Nom VARCHAR NOT NULL, " +_
"Wife VARCHAR, Enfant VARCHAR, Sexe BOOLEAN NOT NULL, Date_Birth DATE NOT NULL, " +_
"Pays VARCHAR NOT NULL, SentBy VARCHAR NOT NULL, Date_In DATE NOT NULL, Date_Out DATE NOT NULL, " +_
"Statut VARCHAR NOT NULL, PRIMARY KEY(ID))"
I am able to store Records with empty (nothing) values. I use a bunch of TextFields (in an edit window) to allow New, Modify, Delete (!).
Note: ID INTEGER NOT NULL is set at the end of the “visible” columns in another (not involved here) table.
Also: Fields are called by Name to avoid errors in destination (excepted when displayed in the Listbox where only a column number is allowed: LB.Cell(LocRow, 2) = CaritasRS.Field("Wife") // Store the spouse name).
My code returns 93 [but there are some mispelled countries).
The original code was:
"SELECT COUNT (DISTINCT Pays) FROM Caritas WHERE Pays<>'';"
I think Xojo does not care about the ending semi-colon (I checked). The Terminal cares (do nothing or report an error )
Is it possible to issue two SQLSelect: one against the whole db, the second agains the results of the first SQLSelect ?
(stupid question).
Note: the values may change only because I get earlier today a brand new db file (the 2016 closed file).
Also: to enforce a more correct db contents, at display time Ive introduced a simple scan and display simple errors once the display process is done and report a window witht he list of all found errors. Errors like no country, no birth date (no Date_In), Man / Woman is empty. I do not (yet ?) report a mispelled country name (like Abany for Albany as I saw earlier today).
I skipped this test because I do not wanted to slow down the display process.
But if someone want to read a RecordSet with a mispelled country name it will not be displayed: I have a list of kocher country names that I display in a PopupMenu (for selection and at RecordSet display time). I explain that to them.
I also had to disallow them to type a date (SQLdate): they now have to choose the Day, month and year from PopupMenus (they are french, so the order of appearance).
Its a pity to have to go these ways and hope to have a correct data in my (yes MY) data base ;-:).
Why don’t you try SQLite Studio? It’s free and it lets you see all your data as well as execute selects and instantly see the results. It’s nicer than writing code if your testing or trying something out. You could also try Vantina Studio. It will let you connect to many different databases.
After a long nap, I started back my searches (using a custom made window with a TextField to put SQL code to be executed), I found:
To get the number of countries
SELECT COUNT (*) FROM Caritas WHERE Pays='France' AND (Date_In LIKE '%2016%');
To get the list of countries
SELECT * FROM Caritas WHERE Pays='France' AND (Date_In LIKE '%2016%');
In my original project, I already have code to display the data as I wish (say France 93 and so on for all defined countries) in a loop.
I have a dictionary that I fill with the country names (I get them using UNIQUE / fill the dictionary), so ‘France’ above is replaced by the Dictionary name, the loop index and get the report I need.
The hard part to get was: AND (Date_In LIKE '%2016%' that returns only entries with the passed year (here 2016). For the real application, I have to replace 2016 with the year of the current date (this part of the application returns data for statistics reports).
Nota: I found a description of LIKE @ www.w3schools.com and used their example and Try it yourself button.
Then I used the Terminal to send commands to my .sqlite file. Not very practical. (the first step was to understand how to open my .sqlite file. After each error, I had to open my .sqlite file: really boring.
So, I add a new window into my project, put a TextField (for the SQLite commands), a PushButton to issue the SQL orders and some viewer(s) to display the returned values.
What is also a hard part is to deal with the (Pro Bono) charity association that do not always know what they want. Here, I think we all know that. But I ask them (many times) what kind of statistics they want / feature / etc.
[quote=307284:@Emile Schwarz]SQLParm = "CREATE TABLE IF NOT EXISTS Caritas(ID INTEGER NOT NULL, PNom VARCHAR NOT NULL, Nom VARCHAR NOT NULL, " +_
"Wife VARCHAR, Enfant VARCHAR, Sexe BOOLEAN NOT NULL, Date_Birth DATE NOT NULL, " +_
"Pays VARCHAR NOT NULL, SentBy VARCHAR NOT NULL, Date_In DATE NOT NULL, Date_Out DATE NOT NULL, " +_
“Statut VARCHAR NOT NULL, PRIMARY KEY(ID))”[/quote]
I’m not an SQL expert, but the table definition says each of the date fields must not be null. So all of your DB entries must have something in them. That means you can’t search for NOT NULL. Check the code that you use to populate those fields when adding records. It’s possible that each field contains the date the record was created, if you haven’t specifically put something in them.
