Page 1 of 1

Prefix

Posted: Thu Oct 19, 2006 9:06 am
by onl5923
Hello everyone,

Can someone help me with the formule in SQL on how to determine the different prefixes that I have in my log? For mixed all bands, per band and/or per mode.

Thanks in advance

Peter

Prefix

Posted: Thu Oct 19, 2006 9:06 am
by G0CUZ
Hi Peter.

SELECT * FROM LOG WHERE Callsign like 'VK9*' OR Callsign like 'VK0*'

This SQL will select all VK9's and VK0's, in SQL you can use the AND & OR operators, note the pattern matching symbol "*" is used after the prefix.
Note the ' ' are used for searching on a text based field, for numeric fields e.g. BAND you do not use them but instead use < > = as required, see later examples.

Try this example to see the difference getting only two of the available fields

SELECT Callsign, Date FROM LOG WHERE Callsign like 'VK9*' OR Callsign like 'VK0*'

To fetch Callsigns with band/mode restrictions, you will need something like...

SELECT * FROM LOG WHERE (Callsign LIKE 'VK9*' OR Callsign like 'VK0*') AND BAND >=14 AND Band <15

This will select QSO's on 14MHz, if you have entered only frequency bands in Log e.g. 14 and not 14.123 then you do not need the frequency range search so above would be ... AND BAND =14

same search but for two different bands

SELECT * FROM LOG WHERE (Callsign LIKE 'VK9*' OR Callsign like 'VK0*') AND ((BAND >=14 AND Band <15) OR (BAND >=21 AND Band <22))

Note use of brackets - see later)

Now for more a more restrictive search

SELECT * FROM LOG WHERE (Callsign LIKE 'VK9*' OR Callsign like 'VK0*') AND (BAND >=14 AND Band <15) and (MODE like 'SSB' OR Mode like 'CW' OR MODE LIKE 'RTTY')

Note the placement of the brackets when differing operators are used in same expression, like in a mathmatical expression it will stop ambiguities.

Add
ORDER BY CALLSIGN
to the end of the expressions to sort list by callsign field (or any other field) there are endless possibilities.

SQL is a very powerful language, and there is plenty written about it, these are just simple examples but this should get you started, if you make an error it won't affect your log data as long as you use a "Select" expression e.g. expression starts with "Select....

These expressions can be added directly to the SEARCH LOG window and use the "execute" button to retrieve the result.



73 Colin G0CUZ
Winlog32 Author

Prefix

Posted: Thu Oct 19, 2006 9:06 am
by onl5923
Wow Colin, some studying to do here hi hi . But thanks for the help !!

Peter

Prefix

Posted: Thu Oct 19, 2006 9:06 am
by onl5923
Hi Colin and everyone,

I am getting the hang of this SQL thing and it ssems to have endless possibilities. But I keep getting the SQL error when I try to get a list of all the single different prefixes that are in my log. So a list of every callsign with a DIFFERENT prefix.

For example 1x XE1, 1x G1, 1x G2, 1x G3, 1x KB6, 1x OO3 ........ so only one result callsign for every single prefix that is in my log.

Peter

Prefix

Posted: Thu Oct 19, 2006 9:06 am
by G0CUZ
Hi Peter

Your quest is a difficult one, I'm not really a SQL guru, I think this query will be quite difficult annd requiring some advanced stuff.

You can try this which will fetch some where prefix level is 3 characters e.g. PY1, PY2, PY3:

SELECT DISTINCT left(Callsign,3) FROM LOG

But problem here is that level 2 prefixes e.g. G0, G1, G2 will selected as level 3 e.g. G0A, G0B, G0C and so on
Level 4 prefix (if they count in WPX?) e.g. CE0X, CE0Z will be missing too.

The "left(callsign,3)" is a text handling expression that selects first three characters of the Callsigns entered and the 'DISTINCT' means it will ignore duplicates - just gets one of each of these prefixes - see the result for your self.

This is the best I can come up with at the moment!

73 Colin G0CUZ
Winlog32 Author

Prefix

Posted: Thu Oct 19, 2006 9:06 am
by onl5923
Ok, Colin, tnx, I know it's a difficult one but we'll keep on trying.

Maybe some other user has got the answer.

Have a great day

Peter