Page 1 of 2

Help/Advice on using Custom searches

Posted: 05 May 2015, 08:38
by G4DUL
I hope someone can help.

I have never used (custom) SQL searches, so I wonder if anyone can give a simple example that I might use as a basis, or point me to a tutorial web site. I have tried www.w3schools.com/sql/, but can't relate the examples given to Log4OM.

Sorry for my lack of knowledge.

Thanks again

73 Martin, G4DUL

Re: Help/Advice on using Custom searches

Posted: 05 May 2015, 09:46
by G4POP
G4DUL wrote:I hope someone can help.

I have never used (custom) SQL searches, so I wonder if anyone can give a simple example that I might use as a basis, or point me to a tutorial web site. I have tried http://www.w3schools.com/sql/, but can't relate the examples given to Log4OM.

Sorry for my lack of knowledge.

Thanks again

73 Martin, G4DUL
Martin,

If you want to use the "Custom update query" in the "Field update" area of the QSO Archive manager I must warn you that unless you are very familiar with that sort of query it can be very dangerous and has the potential to destroy your logbook!

However I will assume that you want to do a custom query in the "Search Parameters" of Log4OM? and this is quiet harmless as far as your data is concerned so here is a how to for you to experiment with.

EG: I have several QSO's where 'Claude' is part of the name - Like John Claude Pasquale but I also have other QSO's where Claude is the only name - I need to display all QSO's where the name Claude appears somewhere in the 'Name' field.

Type:

Name='Claude' and you will only get those QSO's where Claude is the only name in the field

Equals (=) works for EQUALS (Exact match) only

Type:

Name LIKE '%Claude%' OR Name like '%claude%' OR Name like '%CLAUDE%' - results in all QSO's where 'Claude' is somewhere in the Name text

LIKE is "similar to"

Where % means everything so %Claude matches everything that ends with Claude

Claude% matches everything that starts with Claude

%Claude% matches everything that contains (or start/end) with Claude

Re: Help/Advice on using Custom searches

Posted: 05 May 2015, 10:26
by G4DUL
Thank you Terry, that gives me a basis to start from.

73 Martin, G4DUL

Re: Help/Advice on using Custom searches

Posted: 07 May 2015, 07:33
by hb9cat
All,

I'm also trying to make use of custom MySQL queries, but I'm a bit stuck.
Here's an Example of what I want to do: I want to run a query to find my status for the 2m VUCC Award, it's about working distinct locator Grids (Squares).
I have built a simple MySQL query, running it directly on the Log4OM MySQL database from the MySQL Workbench works fine;

Query:

SELECT
COUNT(DISTINCT LEFT(GridSquare,4)) AS "Squares"
FROM
log
WHERE
Band = '2m'

Result:
Squares
--------
237

--------------------------

Can I integrate this search somehow in Log4OM ? Ideally this should be a new Award definition.

73 de Marco HB9CAT

Re: Help/Advice on using Custom searches

Posted: 07 May 2015, 08:01
by G4POP
To add that as an award you need a CSV file of grid squares related to DXCC countries and references

Re: Help/Advice on using Custom searches

Posted: 07 May 2015, 08:45
by hb9cat
Ok then forget the award, can I just place that as log query ?

HB9CAT

Re: Help/Advice on using Custom searches

Posted: 07 May 2015, 09:22
by G4POP
hb9cat wrote:Ok then forget the award, can I just place that as log query ?

HB9CAT

Yes save it as a search parameter for future use

Re: Help/Advice on using Custom searches

Posted: 07 May 2015, 19:41
by HB9BRJ
As the title above the data entry line says, it accepts SQL update queries only. I have tried things like

Select Count(*) From Log

but the result always shows a figure 0. Update queries however are working ok. Let's assume I did a SOTA activation today and forgot to enter "My Sota Ref":

Update Log Set MySotaRef = 'HB/SH-002' Where QsoDate = 20150507

The same result can be achieved more easily using Log4OM's field update feature.

The primary index of the Log table is called QsoId and contains a time stamp showing when the record (QSO) was added to the database. As soon as you have to access this primary key you need an external tool such as the free Database Browser from http://www.etl-tools.com.

If you stay with the default SQLite database, there is another very nice freeware tool for browsing the database or running queries: DB Browser for SQLite from http://sqlitebrowser.org/.

73, Markus HB9BRJ

Re: Help/Advice on using Custom searches

Posted: 14 May 2015, 16:39
by IW3HMH
When you set the query parameters, available in every grid, you must simply switch the radio button to custom query.

Here you can type, as example:

DXCC <> '291' AND BAND = '20m'
to see QSO not made with USA in 20m band

So:
1) select CUSTOM QUERY in the SEARCH PARAMETERS
2) type your WHERE (without WHERE statement)
3) press the + button in the top right part of the search screen
4) apply with V button

Re: Help/Advice on using Custom searches

Posted: 06 Nov 2015, 10:29
by DF2MC
Why the custom search show me all countrys not only Japan as requested.

My query: Country = 'Japan' AND Mode = 'JT9' OR Mode = 'JT65'

What is wrong with my search query ?

As soon as I combine two modes and the country, search result show me all country from the log not only Japan ?
If I delete one mode I get only Japan and the one requested mode !

Could you give me please a few example of query`s, I could modify my self, mainly I have problems to combine more than two parameter in the query !

Thanks for your Support !

73 Hans DF2MC