COUNT to Dimension ARRAY ? 2

[David Higgs]

I have the following code in my application which works as required.

Is there a way of COUNTing into my Dimension ARRAY wagi(10) instead of first storing it in wagi_count and then storing it into my Dimension ARRAY wagi(10) ?

PUBLIC ARRAY wagi(10)

SELECT COUNT(*) FROM csrWAGI_wkd where Date_160 > ctod("01-01-2017") INTO ARRAY wagi_count 	
STORE wagi_count TO wagi(1)

SELECT COUNT(*) FROM csrWAGI_wkd where Date_80 > ctod("01-01-2017") INTO ARRAY wagi_count 	
STORE wagi_count TO wagi(2)

SELECT COUNT(*) FROM csrWAGI_wkd where Date_60 > ctod("01-01-2017") INTO ARRAY wagi_count 	
STORE wagi_count TO wagi(3)

SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > ctod("01-01-2017") INTO ARRAY wagi_count 	
STORE wagi_count TO wagi(4)

Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

David,

You could try this:

SELECT COUNT(*) FROM csrWAGI_wkd where Date_160 > ctod("01-01-2017") ; 	
  UNION  SELECT COUNT(*) FROM csrWAGI_wkd where Date_80 > ctod("01-01-2017") ;
  UNION  SELECT COUNT(*) FROM csrWAGI_wkd where Date_60 > ctod("01-01-2017") ;	
  UNION  SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > ctod("01-01-2017") INTO ARRAY wagi_count

but I'm not sure that it is any better than your original code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

A couple of other improvements to your original code:

- Don't use CTOD() when expressing a date literal. Better to do [tt] DATE(2017, 1, 1)[/tt]. That way, you won't get tripped up by SET DATE settings.

- Although [tt]STORE wagi_count TO wagi(1)[/tt] will work as expected, it is a bit more concise to do [tt]wag1(1) = wgi_count[/tt].

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Mike,

Thank you for you reply.

I tried using your code but didn't get the expected result.

If, say, the Count for DATE_160 was 21, DATE_80 was 37, DATE_60 was 0, DATE_40 was 53 I would have expected :-

wagi_count (1) to contain 21 actual = 0
wagi_count (2) to contain 37 actual = 21
wagi_count (3) to contain 0 actual = 37
wagi_count (4) to contain 53 actual = 53

So it looks like it's storing them in Value order not actual order?

I've applied your recommendations to my code. The CTOD() must stem from my early days with VFP. Since I started to re-construct my project I've been applying experience I've gained over the years to simplify and make the code more efficient. One of the major gains is reducing the number of PUBLIC Variables from literally 100's down to zero at the moment (excepting the use of 3 PUBLIC MY ARRAY's). I achieved this by passing PARAMETERS and using PROPERTY on FORMS.



Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

Steve,

I can see what's happening here.

As you say, you have got the correct results but in the wrong order. In general, you can't determine the order in which SQL SELECT returns its results, unless you include an ORDER BY clause. With no ORDER BY the results can - to all intents and purposes - be considered to be in a random order.

In this caes, a possible solution would be to add an extra column to the array to force the order. Something like this:

SELECT 1, COUNT(*) FROM csrWAGI_wkd where Date_160 > ctod("01-01-2017") ; 	
  UNION SELECT 2, COUNT(*) FROM csrWAGI_wkd where Date_80 > ctod("01-01-2017") ;
  UNION SELECT 3, COUNT(*) FROM csrWAGI_wkd where Date_60 > ctod("01-01-2017") ;	
  UNION SELECT 4, COUNT(*) FROM csrWAGI_wkd where Date_40 > ctod("01-01-2017") ;
    ORDER BY 1 INTO ARRAY wagi_count

That would then give you a 2-column array. The first column contains the sequence number (1, 2, ...); the second contains the required data, in the required order.

Given that you want a one-colum array, you could do the above SELECT into a temporary array, then copy the second column into wagi_count.

However, although my solution is still more concise than your original code, I can't help thinking that your code is more readable. I would be inclined to stay with what you already have (after making the CTOD() and STORE changes I mentioned earlier).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

However, although my solution is still more concise than your original code, I can't help thinking that your code is more readable. I would be inclined to stay with what you already have (after making the CTOD() and STORE changes I mentioned earlier).

Thank you for your reply.

I'd more or less decided to stick with what I've got after I applied your recommendations to my code, but out of courtesy I tried your code and reported my findings.

SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > DATE(2017, 1, 1) INTO ARRAY wagi_count 	
wagi(4) = wagi_count	&&	 40 Metres

Thank you for your valued advice.

Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

That's fine, David. You made the right choice. That said, I hope that you (and perhaps others) found my suggestion of some moderate educational value.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

In general, SQL is better with rows, not columns. So when you'd store your dates in a single date column "DateN" and store the number n (40,60,80,160) as extra field "dategroup", you can group by. Mikes last solution then wouldn't need to create a numeric field on the fly to order by it, it wouldn't even need to union 4 queries, it would end up as one simple query.

Select Count(*) From aTable where DateN > DATE(2017, 1, 1) Group by Dategroup Order By Dategroup

Mike's query could be condensed to this, if his 1,2,3,4 was already in the data and a single row with date40,60,80, 160 would be split into 4 groups of rows with (dateN,40), (dateN,60), (dateN,80) and (dateN,160).

I know your data isn't stored that way and you likely have stored your data your way for decades and programmed for its structure, so a data structure change needs a lot of code changes.
But you see that's how SQL is designed to work best with data in rows, not in columns.

Or see it this way: As group by works this way, it's nice to have columns in the data which can be used for group by. So don't create arrays of fields, just because that's better to read in the end (just like wide scree movies - but also note text is still easier to read in quite narrow columns, even in large newspapers).

I guess you'll say then a lot of other fields would repeat 4 time. No, not if you store them separate and let your 4 new records point back to them AKA 1:n relation.

Chriss

 

[David Higgs]

I hope that you (and perhaps others) found my suggestion of some moderate educational value.

Very much so.

Regards,

David.

Recreational Developer / End User of VFP.

 

[David Higgs]

In general, SQL is better with rows, not columns.

Thank you for your input Chris.

I am re-building one of my applications at the moment and don't really want to make data structure changes at this stage, but I will take on board your advice for the next time I'm experimenting with MySQL.

Regards,

David.

Recreational Developer / End User of VFP.

 

[TamarGranor]

UNION sorts the data.

Tamar

 

[Mike Lewis]

UNION sorts the data.

Even if there is no ORDER BY clause?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I think this experiment answers it:

USE SYS(2005) AGAIN ALIAS dummy
SELECT 3 FROM dummy WHERE RECNO()<4 ;
union ;
SELECT 2 FROM dummy WHERE RECNO()<3 ;
union ;
SELECT 1 FROM dummy WHERE RECNO()<2

The result is 1,2,3, not 3,2,1.

The question is, what's when you want to order by second column or how well the code shows that it sorts. I think it's not bad to have the order by even when it's optional, just for sake of seeing what happens. Same story, I guess, group by also sorts.

And on the other side: UNION also eliminates doubles and UNION ALL does not sort.


Chriss

 

[David Higgs]

The following code produced 0,11,12 and then ERROR Subscript outside defined range.

I was expecting 0,11,0,12 as confirmed with my original code.

SELECT COUNT(*) FROM csrWAGI_wkd where Date_160 > ctod("01-01-2017") ; 	
UNION;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_80 > ctod("01-01-2017") ;
UNION;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_60 > ctod("01-01-2017") ;
UNION;  	
SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > ctod("01-01-2017") INTO ARRAY wagi_test 

FOR a = 1 TO 4
	MESSAGEBOX(wagi_test(a))
NEXT

The following code produced the same results & order as the above code.

SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > ctod("01-01-2017") ; 	
UNION;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_60 > ctod("01-01-2017") ;
UNION;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_80 > ctod("01-01-2017") ;
UNION;  	
SELECT COUNT(*) FROM csrWAGI_wkd where Date_160 > ctod("01-01-2017") INTO ARRAY wagi_test 

FOR a = 1 TO 4
	MESSAGEBOX(wagi_test(a))
next

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

Yes, that's because UNION removes doubles AND sorts.

Use UNION ALL instead.

Or, on the othr side, do as Mike suggested to add an index field, in the order you want and to make records unique, so no double is removed. That also ties to the usual group by you could use with data in a "normal" structure.

Chriss

 

[Mike Lewis]

David, it would help us to understand what is going on here if we knew a bit more about the data. In particular, whether the four date fields in csrWAGI_wkd are mutually exclusive. IAnd in a given record in csrWAGI_wkd, can more than one of the four meet the criterion for the date test?

Can you post a mimimum amount of representative data that would allow us to run the code and try to reproduce the promblem?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I'd also be interested in the data before it goes into the csrWAGI_wkd cursor. Because I think you create those Date_N fields and so the counting would be easier on the initial data than it is on the cursor.



Chriss

 

[David Higgs]

Yes, that's because UNION removes doubles AND sorts.

Use UNION ALL instead.

Thank you Chris, changing from UNION to UNION ALL produced the desired result.



Regards,

David.

Recreational Developer / End User of VFP.

 

[David Higgs]

David, it would help us to understand what is going on here if we knew a bit more about the data.

The Data is used to record Amateur Radio Contacts with stations operating from a particular National Grid Reference Number based on a 10kM x 10kM Square. See

http://wab.intermip.net/Introduction.php

for further information.

The Contact could be made using a number of different 'Band of Frequencies" hence the Date_160 denotes that a contact was made on the 160 Metre Band and Date_40 the contact wade made on the 40 Metre Band.

So the Database will contain a list of all the 10kM x 10kM Squares of Great Britain and each square will have a list of 'Band of Frequencies". If I make contact with a Station in Keswick the NGR would be NY22 and say the Band of operation is 80 Metres then an entry will be made under NY22 with a Date_80 entry.

Only one contact needs to be recorded for each NGR / Band combination, although in reality all contacts are recorded in a separate database the Logbook.




Regards,

David.

Recreational Developer / End User of VFP.

 

[David Higgs]

Consider converting the count(*) into a string padded with leading zeros. Should sort properly then.

This is the modified code with no sorting, which is what I was looking for. So wagi(3) will always show the COUNT for Date_60

SELECT COUNT(*) FROM csrWAGI_wkd where Date_160 > DATE(2017, 1, 1) ;
UNION ALL;  	
SELECT COUNT(*) FROM csrWAGI_wkd where Date_80 > DATE(2017, 1, 1) ;
UNION ALL;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_60 > DATE(2017, 1, 1) ;
UNION ALL;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > DATE(2017, 1, 1) ;
INTO ARRAY wagi

Regards,

David.

Recreational Developer / End User of VFP.