SQL Searching weird behaviour

You may post here if you are having problems with Winlog32 that others may be able to help you with. You may also report bugs so that the author may act upon them.
Post Reply
F4GVO
Bit of a boffin
Posts: 61
Joined: Sat Aug 08, 2015 9:22 am

SQL Searching weird behaviour

Post by F4GVO »

Hi Colin,
Using Winlog32 v7.3.32 on Windows 7.
This evening I was checking which QSL cards via BURO I still haven't sent and I still haven't received first.
It is something I do regularly so for that I use the SEARCH LOG window and SQL searching.
Here is the "code line" I use always successfuly:

SELECT * FROM LOG WHERE Remarks LIKE '*BURO*' AND QSLIn NOT Like '*' AND QSLOut NOT LIKE '*' AND DATE >=#05/01/2019#

The problem ,and it is a very first time for me, is that all contacts after the 31st of May 2019 are not found in the SEARCH result window !!!
And it is not logical as I have 391 QSOs registered after this date. But that's not all, if I use only this "code line" following code line in SEARCH LOG window: SELECT * FROM LOG WHERE DATE >=#06/01/2019#, I then have 391 QSOs found and displayed in the result window....

I hope my explanation are ok and I really hope you'll find why this behaviour.
Thank you in advance
73 de Thierry, F4GVO
G0CUZ
Site Admin
Posts: 461
Joined: Wed Jan 02, 2002 7:12 am

Re: SQL Searching weird behaviour

Post by G0CUZ »

hi Thierry

AND QSLIn NOT Like '*' AND QSLOut NOT LIKE '*'

Try swapping the NOT operator:

….. AND NOT QSLin LIKE '*' etc

I'm not sure if this is the problem but I run the NOT operator before the queried field, I think this is important if also using with the AND operator.

73 Colin
G0CUZ
F4GVO
Bit of a boffin
Posts: 61
Joined: Sat Aug 08, 2015 9:22 am

Re: SQL Searching weird behaviour

Post by F4GVO »

Thank you Colin, your advise made the trick !

Anyway it is surely strange that "my way" works well but only for QSO before 1st of july.
As I wrote a lot of different SQL queries to use with Winlog32, I have try another one, swapping the NOT as you suggested but no difference at all, both way works well... anyway, I now will take in mind this NOT to put before.

73 de Thierry,F4GVO
G0CUZ
Site Admin
Posts: 461
Joined: Wed Jan 02, 2002 7:12 am

Re: SQL Searching weird behaviour

Post by G0CUZ »

Yes, Thierry there re a lot of strange things with Access SQL, may be the problem you had is will NULL values, these have different behaviour to empty strings in Access database and the reason for having to use the NOT operator, also when mixing multiple operators AND/OR/NOT sometimes it is necessary to enclose parts of the query in brackets so that one operator will work before the other.
There are many dimensions with SQL if you study all possibilities.
I am not an expert with SQL but Winlog32 does use it extensively for querying the log and databases for many of the functions, some SQL statements have to be quite complex to make them work as expected.
All very interesting though.....
73 Colin, G0CUZ
F4GVO
Bit of a boffin
Posts: 61
Joined: Sat Aug 08, 2015 9:22 am

Re: SQL Searching weird behaviour

Post by F4GVO »

I am also not an expert in SQL but it is sure very powerful and so useful to look for specific information through the log.
I really like these possibilities with your logger, one of lot of them that makes Winlog32 a very good logger, at least for my way of work ;)

Have a great day, here in north east France, we are expecting a new wave of hot temperature...

73 de Thierry, F4GVO
G0CUZ
Site Admin
Posts: 461
Joined: Wed Jan 02, 2002 7:12 am

Re: SQL Searching weird behaviour

Post by G0CUZ »

Thanks Thierry

Another thing, for others that may read, it will NOT damage the database when using the SELECT statement, so it is real fun experimenting like you do to find what is possible.

Please some of you WX up here in middle England ! 73
F4GVO
Bit of a boffin
Posts: 61
Joined: Sat Aug 08, 2015 9:22 am

Re: SQL Searching weird behaviour

Post by F4GVO »

I found that SQL searching stuff a few years ago when looking through the forum. I can't now remember who but another user shared how he was doing.
I then started to try my own and found it so interesting and powerful.

As I am a QSL card lover, each QSO I made, I use the REMARKS field in Winlog32 to put, after looking on QRZ.com or similar, if the operator worked is BURO EQSL LOTW OQRS etc.... Filling these infos help me after because, let say each 3 months I checked all contacts I haven't send my QSL card, using this simple "script"

SELECT * FROM LOG WHERE Remarks LIKE '*BURO*' AND QSLOut NOT LIKE '*' AND QSLIn NOT LIKE '*'

As I am a LOTW user, I do it after checking new validated contacts from LOTW database, so all these contacts are marked with a " V " in Winlog32.
So at the end, I get just the one who are still not been confirmed by LOTW and I haven't still received and sent QSL card. A lot of time it show me some very important QSO to confirm because for my DXCC quest ;)
And if I want to check for a specif period, I use this one:

SELECT * FROM LOG AND WHERE QSLout NOT LIKE '*' AND Date between #01/01/19# and #12/31/19#

All of this to tell that as you wrote, it is something to try without any danger for our precious databases ! So why not try it ! It is very helpful !

Out of topic: for WX, if I could, I will send it to you as we are excepting a new wave of hot temperatures.. these week-end

Best regards,

Thierry, F4GVO
G0CUZ
Site Admin
Posts: 461
Joined: Wed Jan 02, 2002 7:12 am

Re: SQL Searching weird behaviour

Post by G0CUZ »

Thierry
You are making great use of SQL!!

I wonder if you have tried using the menu/Search/Search-Custom] window, it's only meant for the advanced user, but think this is you !

You can write and save and retrieve your own queries here, specific to your own requirements, it is not as simple as pasting in a query to the search window, so you would need to look at the help page on this although there are some examples included (called 'plug-ins')

That function took me weeks to code up, I am not sure if anyone uses it at all though?

The Search-Custom window also allows these custom searches to be applied to ANY database in Winlog32, it is also possible to use SQL commands other than SELECT (if I remember correctly).

I don't use it myself as I am not into data gathering these days but always interested in any feedback if anyone finds it useful and how.

73 Colin, G0CUZ
F4GVO
Bit of a boffin
Posts: 61
Joined: Sat Aug 08, 2015 9:22 am

Re: SQL Searching weird behaviour

Post by F4GVO »

I must confess that I had never mind about the Search-Custom window. :oops:
I just checked the help file and.. waouu, for sure, it is more complex that my simple little scripts !
But it seems so powerful and the way to save them and so use it again and again is very interesting.
I will check these way and try to build some useful but right now with the hot temperature, my brain are just soap..... :lol:
Post Reply