Tuesday, March 20, 2007

UNION oddity (or maybe it's just me)

I had a situation come up today where I needed to add the contents of one cursor (cursorA) to another (cursorB), without adding any duplicates in the process. Both cursors had the exact same structure. CursorA was going to be closed (a little later), while cursorB would persist (for another few processes). Immediately I turned to the UNION clause. My strategy was to select * from both tables, union them together, and put the results back into cursorB like this:

SELECT * FROM cursorB ;
UNION ;
SELECT * FROM cursorA ;
INTO CURSOR cursorB READWRITE

I was surprised to see the open dialog box pop up, basically asking me to locate cursorB! What happens here is that the INTO clause seems to strike first, essentially closing cursorB. I suppose I was a little surprised because I do stuff like this all the time:

SELECT * FROM cursorB WHERE .T. INTO CURSOR cursorB

I worked around this by modyfing my SQL to the following:

SELECT * FROM (SELECT * FROM cursorB) as tmp ;
UNION ;
SELECT * FROM cursorA ;
INTO CURSOR cursorB READWRITE

I should mention that this only works in VFP9. VFP7 will complain that the syntax is wrong (I presume the same in VFP8 too). The only solution that I can think of in VFP7 would be in two-steps:

SELECT * FROM cursorB ;
UNION ;
SELECT * FROM cursorA ;
INTO CURSOR cursorC

SELECT * FROM cursorC INTO cursorB READWRITE

2 comments:

Jamie said...

Hah! I never knew that you could select from an alias back into the same alias.

Bugger! Would have saved me a few extra selects from time to time!

Tod McKenna said...

What's funny is that I stumbled upon this a long time ago (maybe 6 or 7 years in VFP6 I think) by accident. I managed to overwrite a cursor by issuing it the same name as what I had in the from clause. It was a typo, and took me several hours to figure out how my cursor was getting 'garbage data'.

This lesson uncovered a cool little 'feature' that I've used ever since!