SELECT not creating array 6

[mmerlinn]

[ ]
Can anybody tell me what I am missing here? I don't see any reason why the second array is not being created just like the first one was.

[tt][blue]
zdestpath = qBPathDest + zdosdaddy + '_f.TXT'
= G_CATFRM(zdestpath, zhtmdaddy, zwebtit)
[green]
SELECT DISTINCT tm_sname ;
FROM tranmodl ;
WHERE NOT EMPTY(tm_tname) ;
HAVING vfamname = (qFamName) ;
AND DELETED("Tranmodl") <> .T. ;
INTO ARRAY temp ; <==== This creates an array
ORDER BY tm_sname
[/green]
zfamlist = ''
FOR z = 1 TO ALEN(temp, 1)
zfamlist = zfamlist + ', ' + ALLTRIM(temp[z])
ENDFOR

zfamlist = ATXRIGHT(zfamlist, ', ')
[red]
SELECT DISTINCT tm_tkind ;
FROM tranmodl ;
WHERE NOT EMPTY(tm_tkind) ;
HAVING vfamname = (qFamName) ;
AND DELETED("Tranmodl") <> .T. ;
INTO ARRAY a_Temp <==== This does NOT create an array
[/red][red]
*
* Throws 'variable not found' error here
* V
* V [/red]
FOR z = 1 TO ALEN([red]a_Temp[/red], 1)
a_Temp[z] = 'trans' + PICKLIST(ALLTRIM(a_Temp[z]), 'R', 'missions', '4', 'missions', 'T', 'axles', 'A', 'axles', 'X', 'fer cases')
ENDFOR
[red]
*
* Throws 'variable not found' error here
* V
* V [/red]
FOR z = 1 TO ALEN([red]a_Temp[/red], 1)
a_Temp[1] = a_Temp[1] + IIF(a_Temp[z] $ a_Temp[1], '', ' & ' + a_Temp[z])
ENDFOR
[red]
DIMENSION a_Temp[6] <==== creates array instead of redimming it[/red]
a_Temp[1] = a_Temp[1]
a_Temp[2] = zfamlist
a_Temp[3] = zmaker
a_Temp[4] = qFamname
a_Temp[5] = qBPathDest + zdosdaddy + '_m.TXT'
a_Temp[6] = zwebtit

= G_CATSPL(@a_Temp)
[/blue][/tt]


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Stella740pl]

Can anybody tell me what I am missing here? I don't see any reason why the second array is not being created just like the first one was.

In my experience, there is usually only one reason for that: there are no records that match the selection criteria (is that possible that all tm_tkind are empty?).

When you select into table or cursor, even with no matching records, an empty table/cursor is created. It is different with arrays. Array with zero records is not created.

I usually test the results of the query with _TALLY right after the SELECT statement. If you had there something like this, you could avoid an error:

SELECT ... INTO ARRAY a_Temp
IF _TALLY>0
  Do something with a_Temp array
ELSE
   Do something else
ENDIF

By the way, is there any reson, why you put some of the conditions (vfamname = (qFamName) AND DELETED("Tranmodl") <> .T.) in the HAVING clause and not in the WHERE clause?
According to VFP documentation,

The HAVING clause without a GROUP BY clause acts like the WHERE clause. If the HAVING clause contains no aggregate functions, use the WHERE clause for faster performance.

(my underlining).

 

[mmerlinn]

[&nbsp;]
Stella740pl:

In my experience, there is usually only one reason for that: there are no records that match the selection criteria (is that possible that all tm_tkind are empty?).

Already double-checked that. I can change the first SELECT from tm_sname to tm_tkind and an array is created, so it does not make sense that the first would work, but the second, with EXACTLY the same fields would not work.

I have never used _TALLY, probably because I have never had a table that could produce zero records for a particular SELECT. In this particular case, using _TALLY would have only indicated something was wrong, as it is impossible to have zero records with either of these SELECTs.

As to why WHERE vs HAVING clauses? I don't know why I sometimes use one or the other. I have always had a hard time understanding what the difference is between the two. The FoxPro documentation (FP 2.6) I have is extremely unclear about which one does what and when, so I have just used whichever worked for me. As for the speed, in the small tables I deal with (under 20,000 records), I doubt the the speed difference would be noticeable.



mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Stella740pl]

Already double-checked that. I can change the first SELECT from tm_sname to tm_tkind and an array is created, so it does not make sense that the first would work, but the second, with EXACTLY the same fields would not work.

So it would work if you change the order in which you issue your SELECT statements? If you place it first, it would work, but doesn't work if you place it second? Hm.

Did you try to select them both into tables instead of arrays? Would that work? How many records are selected then?

In this particular case, using _TALLY would have only indicated something was wrong, as it is impossible to have zero records with either of these SELECTs.

Of course it would. It would serve as error handling. Say, if it is not created, you get a message box like "Uh-oh. Something is wrong. The array is not created."

As to why WHERE vs HAVING clauses? I don't know why I sometimes use one or the other. I have always had a hard time understanding what the difference is between the two.
...As for the speed, in the small tables I deal with (under 20,000 records), I doubt the the speed difference would be noticeable..

As for speed, you can always test. But the problem is, if you don't understand which one you should be using, you would be likely to misuse them even when the speed difference would matter.

The difference is this. WHERE operates on the source table, at the stage when the resulting data set is not created yet.
HAVING operates on already created resulting data set.

HAVING should be used in two cases. First (and the quote from Help I posted points that out), when SELECT statement has GROUP clause, and the condition in HAVING contains an aggregate function. Example:

SELECT field1, field2 ;
   FROM myTable ;
   WHERE some conditions ;
   GROUP BY 1 ;
   HAVING count(field2)>1

Second, when you use in the condition column names from your resulting data set. Example:

SELECT COMPLEX_EXPRESSION(field1) AS field2 ;
   FROM myTable ;
   WHERE some conditions ;
   HAVING field2>1

 

[mmerlinn]

Did you try to select them both into tables instead of arrays? Would that work? How many records are selected then?

I tried putting into cursors. First cursor has correct records. Second cursor is empty. Does not matter which I put first, the result is the same.

I solved the problem by using

[blue]SELECT DISTINCT tm_sname, tm_tkind ;
FROM tranmodl ;
WHERE NOT EMPTY(tm_tname) ;
AND vfamname = (qFamName) ;
AND DELETED("Tranmodl") <> .T. ;
INTO CURSOR temp ;
ORDER BY tm_sname[/blue]

then extracting the info from that cursor. Fortunately, I can use tm_tname to filter both result sets somewhat, even though the second original SELECT did not use nor need that filter.

The problem with doing it this way is that I get a bloated cursor with a lot of empty records in both fields that I must filter out later instead of when I extract them using the SELECTs. In a large cursor, that has got to be slower, and it definitely involves more complex code to do the job.

[red]And it still does not answer the question as to WHY the second SELECT does not work as expected.[/red]

But the problem is, if you don't understand which one you should be using, you would be likely to misuse them even when the speed difference would matter.

Even though this is not likely to be the case for me, I do not like the fact that I have obviously misused them because of not understanding how they work. Misusing them eventually could bite me regardless of whether speed was an issue.

The difference is this. WHERE operates on the source table, at the stage when the resulting data set is not created yet. HAVING operates on already created resulting data set.

You have cleared up some of the confusion for me and you get a star for that. In the future I will use WHERE whenever possible, which, for me, will probably be always since I don't forsee me ever needing to GROUP the results.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Stella740pl]

I tried putting into cursors. First cursor has correct records. Second cursor is empty. Does not matter which I put first, the result is the same.
...
And it still does not answer the question as to WHY the second SELECT does not work as expected.

Well, it does confirm my initial suggestion that, for whatever the reason is, the second data set is empty. So, the problem is not with the array itself, but with the criteria you use to select into it. Try to go over it again, removing parts of the WHERE clause one by one and adding them back again. You might notice something you didn't see before.

I solved the problem by using

SELECT DISTINCT tm_sname, tm_tkind ;
        FROM tranmodl ;
        WHERE NOT EMPTY(tm_tname) ;
            AND vfamname = (qFamName) ;
            AND DELETED("Tranmodl") <> .T. ;
        INTO CURSOR temp ;
        ORDER BY tm_sname

Wait a second. It is not the same criteria you used in your separate SELECTs. In the first one you used NOT EMPTY(tm_tname), in the second one it was NOT EMPTY(tm_tkind), but when you combined them you used NOT EMPTY(tm_tname), as in the first one. What happened to NOT EMPTY(tm_tkind)? Could it be part of the reason it is empty? That would also explain what you mentioned before, that you "can change the first SELECT from tm_sname to tm_tkind and an array is created, so it does not make sense that the first would work, but the second, with EXACTLY the same fields would not work."

Another thing to check. Do you post your code exactly as appears in your program, or just some excerpts? I recently help a colleague to find an error in his program. His SELECT didn't create a table. Actually, it didn't run at all. When we ran it from the Command window, it worked; when in the program, it didn't. Strange, right? Then I noticed that the line immediately preceding the SELECT in the program was a comment ending with a semicolon. It means that even though the SELECT didn't have the comment syntax coloring, it was commented out, too. Something to look into.

In the future I will use WHERE whenever possible, which, for me, will probably be always since I don't forsee me ever needing to GROUP the results.

I can suggest one use for GROUP clause you might want to try. It can be used (very often, but not always, of course) instead of DISTINCT keyword - it would make the query faster. Say,

SELECT field1 ;
   FROM myTable ;
   GROUP ON 1

would run faster than

SELECT DISTINCT field1 ;
   FROM myTable

while getting you exactly same results.

Thanks for the star. (Didn't Mike Yearwood explain it, too?)

 

[mmerlinn]

[&nbsp;]

Do you post your code exactly as appears in your program, or just some excerpts?

The original code is cut and pasted EXACTLY from the program with no excerpts and no deletions. The comments were added after the cut & paste for clarification.

What happened to NOT EMPTY(tm_tkind)?

The NOT EMPTY(tm_tkind) was needed in the original second SELECT to filter out empty tm_tkind records. It was not used in the solution because it would then filter out some of the needed tm_sname records. By definition, every record that has data in tm_tkind also has data in tm_tname (though not vice versa), so using tm_tname as the filter has no effect on tm_tkind. As a result the solution SELECT has some records where tm_tkind is empty while all tm_sname records have data. That is why I stated that I would need complex code to further eliminate those records that have empty fields. Short of using two SELECTs as originally used, I know of no way to filter the combined result into a cursor where BOTH columns have only valid results without losing many of the results that are needed.

I tried putting into cursors.  First cursor has correct records.  Second cursor is empty.  Does not matter which I put first, the result is the same.
...
And it still does not answer the question as to WHY the second SELECT does not work as expected

Obviously I was not clear when I made this statement. I can swap the SELECTs using cut & paste, and the first will ALWAYS work while the second will NEVER work. It seems to me that if a SELECT will create an array in the first position the same exact SELECT should ALSO create an array in the second position, but it does NOT. [red]And that really confuses me, because I don't see how that is possible.[/red]

Here is the cut & pasted full code of the solution:

[blue]SELECT DISTINCT tm_sname, tm_tkind ;
FROM tranmodl ;
WHERE NOT EMPTY(tm_tname) ;
AND vfamname = (qFamName) ;
AND DELETED("Tranmodl") <> .T. ;
INTO CURSOR temp ;
ORDER BY tm_sname

zfamlist = ''
SCAN
zfamlist = zfamlist + ', ' + ALLTRIM(tm_sname)

DIMENSION zz[RECNO()]
IF NOT EMPTY(tm_tkind)
zz[RECNO()] = 'trans' + PICKLIST(ALLTRIM(tm_tkind), 'R', 'missions', '4', 'missions', 'T', 'axles', 'A', 'axles', 'X', 'fer cases')
ELSE
zz[RECNO()] = ''
ENDIF
ENDSCAN

zfamlist = ATXRIGHT(zfamlist, ', ')

FOR z = 1 TO ALEN(zz, 1)
IF NOT EMPTY(zz[z])
zz[1] = zz[1] + IIF(zz[z] $ zz[1], '', ' & ' + zz[z])
ENDIF
ENDFOR

DIMENSION a_Temp[6]
a_Temp[1] = zz[1]
a_Temp[2] = zfamlist
a_Temp[3] = qMaker
a_Temp[4] = qFamname
a_Temp[5] = qDest
a_Temp[6] = qTitle[/blue]

As you can see, not using two SELECTs made this code much more complex.

Thanks for the star. (Didn't Mike Yearwood explain it, too?)

Yes, he did, but I did not understand what he stated until I read your post then went back and reread his. Your explanation was immediately clear to me, while his was not.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[TamarGranor]

I can swap the SELECTs using cut & paste, and the first will ALWAYS work while the second will NEVER work. It seems to me that if a SELECT will create an array in the first position the same exact SELECT should ALSO create an array in the second position, but it does NOT. And that really confuses me, because I don't see how that is possible.

What this says to me is that something is changing in the environment between the two queries. Have you checked right before the second what the value of qFamName is?

Also, one more structural comment. There's no reason to write your test:

DELETED("something") <> .T.

Instead, use:

NOT DELETED("something")

That'll be easier for the next programmer to understand. In general, with logical variables, fields and functions, you never have to compare to .T. or .F.; just check the thing or NOT the thing.

Tamar

 

[Stella740pl]

What this says to me is that something is changing in the environment between the two queries. Have you checked right before the second what the value of qFamName is?

I agree with Tamar. Also, try to put the two queries right next to each other, and not separating them with any other code. Put everything you have between the two after the second one is done. Would that work?

 

[mmerlinn]

[&nbsp;]

Have you checked right before the second what the value of qFamName is?

No, because after the selects qFamName is used in a_Temp[4] = qFamname and it produces the correct results there. Also, by definition, all variables starting with 'q' have their value set in the PARAMETER clause and that value is NEVER changed anywhere else, which of course means that I can rely on that variable always retaining its original value within that PROGRAM, PROCEDURE, or FUNCTION.

I did some testing. I pasted the second SELECT immediately before the first one. It created the array there, but the original first SELECT did not create an array. I deleted everything between the two SELECTs. First works, second does not. I created another program and hand typed them in. Same result. I swapped them, same result. First always works, second never does, regardless of which one actually comes first. Later I am going to use a different table with different fields and different conditions to see what will happen.

DELETED("something") <> .T.

I never used to write SELECTs this way - I used NOT DELETED() instead. But, when I use NOT DELETED() then open the SELECTs in the query wizard, the wizard saves them as above. So, to avoid confusion, I have started writing them all this way to be consistant. This SELECT was not constructed by a wizard - I only use the wizard for long SELECTs, then tweek the SELECTs for constructs the wizard cannot do, things like (qFamName) in my examples here.

Elsewhere, I never compare logical variables to .T. or .F.



mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Stella740pl]

Interesting.
Now, I know that shouldn't change anything, especially when SELECT statements are involved, but for some weird reason, it sometimes does make a difference.
Try moving a cursor in the source table between the two SELECTs. Say, insert
SELECT tranmodl
GO TOP
between the two.

 

[mmerlinn]

[&nbsp;]
I did all of the following between the two SELECTs.

If I add DIMENSION a_Temp[1], the array is created BUT when the SELECT is run, the array is NOT updated with the results of the SELECT.

If I add SELECT any_table, there is no change.

If I add GO TOP, there is no change.

If I add SELECT some_other_table and GO TOP, there is no change.

If I run a SELECT on a different table into another_array, the new array is created and filled, BUT the second SELECT from tranmodl still does not create an array.

But, if I add SELECT tranmodl and then GO TOP, the second SELECT works as it should.

Based on this tiny sample of results, I assume that the following is true.

[RED]Whenever two SELECTs are done on the SAME table, regardless of any intervening SELECTs, the second SELECT will fail unless one does a SELECT on the table in question, then a GO TOP.[/RED]

So, to be safe, I guess I should ALWAYS move the table pointer before I do any SELECT on that table.

No matter what, this seems utterly ridiculous, but based on this testing it seems to be true.

Has anyone tested my code in their environment? I am curious if this behaviour is unique to FP 2.6 or not. My code will not run unless the lines containing the PICKLIST and ATXRIGHT UDFs are commented out.




mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Stella740pl]

Really? Wow.

That's probably was what I meant when I said that "I know that shouldn't change anything, ... but for some weird reason, it sometimes does make a difference."

If I add SELECT any_table, there is no change.

Of course. You just switched into another work area. Why would it change anything at all?

If I add GO TOP, there is no change.

Well. It means that at the moment you issued GO TOP, tranmodl wasn't your current work area. If it was, it would work.

If I add SELECT some_other_table and GO TOP, there is no change.

Sure, you moved the pointer in some other table that doesn't have anything to do with this part of the program. Not surprisingly, it didn't help.

But, if I add SELECT tranmodl and then GO TOP, the second SELECT works as it should.

That's because you first made sure that the source table is your currently selected table, then moved the pointer - in the source table, not just any table. If it had to work at all, that was the thing to do.

I am curious if this behaviour is unique to FP 2.6 or not.

I am not sure it is unique to FP 2.6, but I think it changed long ago. I use VFP6.0, and I usually can issue as many queries SELECTing from the same table as I need, without moving the pointer. SELECTs now don't move the pointer of the table, unlike many native FoxPro commands. Besides, the position of the table pointer doesn't matter when you issue a SELECT statement, so you can put many of them one after another without having to move the pointer. If you SELECT INTO ARRAY, you also don't change your current work area.

As an explanation of what happened in your case, I can suggest that in older FoxPro versions SELECT used to work just like many native FoxPro commands (SEEK, for example). That's probably what I hoped for when I made the suggestion to move the pointer of the source table.

SELECT here seemed to move pointer along the way, and also needed it to be at the top when you issued the next one (and the source table to be selected). Also, it seems that SELECT INTO ARRAY changed the current work area. Can't check all of this as I don't have FP 2.6, though I might still have FP 2.0 documentation - I will take a look when I have a chance.

I am glad you resolved the issue.

 

[mmerlinn]

[&nbsp;]
Interestingly enough, when I do a second iteration through the procedure, the FIRST SELECT does not create an array the second time through unless I first SELECT the table/cursor and move the record pointer. This is true for the both the original code and for the solution, so in fact the solution did not really solve anything at all.

And this is further support for the theoretical assumption I made in the previous post.

I have reverted to the original code with the addition of SELECTing the table first and then issuing GO TOP before BOTH SELECTs. Not doing so before the first SELECT crashes the program on subsequent iterations through the PROCEDURE even though it is not needed the first time through.

And many thanks for pointing me in the right direction to solve this puzzling issue.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Stella740pl]

Interestingly enough, when I do a second iteration through the procedure, the FIRST SELECT does not create an array the second time through unless I first SELECT the table/cursor and move the record pointer.
...solve this puzzling issue.

Then this issue doesn't puzzle me any more. What you observed doesn't surprise me at all, as it further support my theory that in older FoxPro (2.6 in particular) SELECT-SQL worked just like many native FoxPro commands, which required a source table to be the current work area and the pointer to be on top.

When you ran your program the first time around, this condition was already met due to some previous code. When you did the second iteration, the environment has already changed, and now your first SELECT was in the same position your second SELECT was before that.

It also means that your assumption ([red]"Whenever two SELECTs are done on the SAME table, regardless of any intervening SELECTs, the second SELECT will fail unless one does a SELECT on the table in question, then a GO TOP."[/red]) is a little off.

Apparently, it doesn't really matter if you run one, two, or more SELECTs. It is just that a table should be selected and a pointer to be on top before any SELECT. It became clear only when the first SELECT switched the work area (you can probably test that by issuing ?ALIAS() before and after the first SELECT), and also moved the pointer to the bottom (you can try to check this by issuing ?EOF() right after the first SELECT and SELECT tranmodl command). You can also try to move pointer not to the top, but somewhere in the middle of the table, and try your SELECT then. Will it return all the records needed or only those located below the pointer? If you decide to try these tests, please report the results in this thread, as I am interested to know, too.

What is true is this:”… I should ALWAYS move the table pointer before I do any SELECT on that a table."

What it all means for you? It means that you learned some of the ways FoxPro works, and you don't need "fixes" any more; you should adjust your programming style accordingly - as noted in the statement above.

I also learned something here - the differences between older FoxPro and Visual FoxPro. I didn't work much with older FoxPro, I used to program in dBase III Plus a little (don't even know if it had SELECT-SQL), then several years in Clipper Summer '87 (it didn't have SQL), nearly skipped FoxPro 2.x, but worked some time in VFP 3.0 and 5.0, and now for many years already in VFP 6.0.

So thank you, too, for letting me learn something today. Even though the version is old, the knowledge, apparently, may still prove useful.

 

[mmerlinn]

When you ran your program the first time around, this condition was already met due to some previous code.

The first time around the table tranmodl was NOT open in ANY work area. The SELECT opened and left it open.

Will check for EOF() etc later.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Stella740pl]

The SELECT opened and left it open.
SELECT can do that. But while it's just been open, it is always points to the first record, and never EOF().

So maybe it automatically at the same time makes it the selected work area for the duration of running SELECT statement.

Or maybe it is not that important for the table to be in the current work area - just not at EOF().

 

[TamarGranor]

Whenever two SELECTs are done on the SAME table, regardless of any intervening SELECTs, the second SELECT will fail unless one does a SELECT on the table in question, then a GO TOP.

To the best of my knowledge, this has never been true in any version of FoxPro, nor is Stella's more general statement below true:

in older FoxPro (2.6 in particular) SELECT-SQL worked just like many native FoxPro commands, which required a source table to be the current work area and the pointer to be on top.

Just to be sure, I just tested in FoxPro 2.6/Win. Whether I send the results to a cursor or an array, sequential queries against the same table work just fine without any need to SELECT or GO TOP.

However, the fact that SELECT followed by GO TOP solves the problem is another clue. It sounds like something about one of the queries is causing trouble.

What happens if you change the second query to be a simple:

SELECT * FROM TranModl INTO CURSOR junk

Tamar

 

[mmerlinn]

[&nbsp;]

I think we have finally found the problem!

What happens if you change the second query to be a simple:

SELECT * FROM TranModl INTO CURSOR junk

This works as expected. From that I built the query up to this:

SELECT DISTINCT tm_tkind FROM TranModl WHERE NOT EMPTY(tm_tkind) AND vfamname = (qFamName) INTO CURSOR junk

This still works correctly. But when I did either of the following, it stops working:

SELECT DISTINCT tm_tkind FROM TranModl WHERE NOT EMPTY(tm_tkind) AND vfamname = (qFamName) [red]AND NOT DELETED("Tranmodl")[/red] INTO CURSOR junk

SELECT DISTINCT tm_tkind FROM TranModl WHERE NOT EMPTY(tm_tkind) AND vfamname = (qFamName) [red]AND DELETED("Tranmodl") <> .T.[/red] INTO CURSOR junk

This was completely unexpected, so, I tried the following:

SELECT DISTINCT tm_tkind FROM TranModl WHERE NOT EMPTY(tm_tkind) AND vfamname = (qFamName) [red]AND DELETED("Tranmodl")[/red] INTO CURSOR junk

SELECT DISTINCT tm_tkind FROM TranModl WHERE NOT EMPTY(tm_tkind) AND vfamname = (qFamName) [red]AND DELETED("Tranmodl") <> .F.[/red] INTO CURSOR junk

These both work correctly, BUT [red]NONE OF THE RECORDS IN "TranModl" WERE DELETED![/red]

What is really odd is that the first SELECT was working correctly with NOT DELETED() while the second SELECT was working correctly with DELETED() on the SAME table with NO deleted records in the table. And when the program looped through the PROCEDURE a second time, neither of the SELECTs were working correctly. Of course, with DELETED working backwards in the second and subsequent SELECTs, there was no way to get correct results.

Next step was to PACK the table, even though there was NO indication that any of the records were deleted. I ran the last four again. Now they ALL work correctly. Not only that, but the original SELECTs both work correctly without moving the record pointer.

So, now, does anyone know why, in this case, DELETED() worked correctly after the GO TOP, but incorrectly before the GO TOP? Also, why would DELETED() select UNdeleted records and vice versa depending on where the record pointer was? Is there ANOTHER method of marking deleted records that DELETED() uses instead of the method visible to us lowly humans?

Regardless, I would never have expected DELETED() to work this way. Now, to prevent potential problems with my other SELECTs, I am going to PACK all of my tables more often.

All of the code originally posted works correctly now exactly as posted. There was nothing wrong with any of it at any time, so I am now going to use it as is.

And a star to you Tamar.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[mmerlinn]

[&nbsp;]
Oops, not quite exactly as posted.

Have moved the HAVING clauses to WHERE.


mmerlinn

"Political correctness is the BADGE of a COWARD!"