SELECT SQL without switching area

[Toman]

Hi,
Is it possible to generate SELECT SQL command without automatic switching to a work area with query results?
I've finished my experiments with

SELECT * FROM table TO SCREEN NOCONSOLE

which is perfect, no switching, just results are neither visible nor accessible.
Thank you, Tom.

 

[Mike Lewis]

Tom,

Are you saying you want to get the query results into a cursor, but you don't want to switch to the cursor's work area?

If so, this should do it:

lnSave = SELECT()
SELECT * FROM Whatever INTO CURSOR SomeCursor
SELECT (lnSave)

That'll work even if there was no work area selected previously.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Toman]

Mike,

Behind my question was exactly my whish to eliminate lnSave = SELECT() / SELECT (lnSave) construct.
If you suggest this, I understand, that there is no other way to force VFP not to switch to work area where new cursor resides.

Thank you, Tom

 

[Mike Lewis]

Tom,

That's right. There's no way of prevent VFP from making the cursor's work area the active one. For example, there's no IN clause for SELECT.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[IlyaRabyy]

Actually, colleagues, there is the way, albeit the result is somewhat cumbersome to process afterwards:

SELECT * FROM MyTable INTO ARRAY aSelected

You stay in the same work area, but you have to watch for RAM usage (the resulting array can be of a huge size), and you'll have to, probably, put this array into some table or cursor afterwards anyway, I guess.

HTH.

Regards,

Ilya

 

[Toman]

Ilya,

not a bad idea in cases I had on mind. Current work area remains the some. Pointers remain the some. Easy to read code, something like

SELECT MAX(MyField) FROM MyTable … INTO ARRAY aMyMaximum

IF _TALLY > 0
   *    all followings are acceptable
   ? aMyMaximum(1,1)
   ? aMyMaximum(1)
   ? aMyMaximum
   *    or with bigger respect to variable types
     nMyMaximum = aMyMaximum
   ? nMyMaximum

ENDIF

Nevertheless I must listen a few days, what my sense for purity in programming says.

Thank you for a tip.
Tom

 

[Olaf Doschke]

If that is the type of SQL you want to do, you can also CALCULATE MAX(...) TO Variable. The usage is very limited though. SQL-SELECT INTO ARRAY offers a much larger variety of course.

Bye, Olaf.

 

[Dan Freeman]

CALCULATE really carries no improvement *except* when you're doing multiple calculations, and calculations that aren't easy to do in a single SQL statement:

CALCULATE Min(), Max(), Avg()

 

[Mike Lewis]

CALCULATE is good for those cases where you only have a single "row" of values to calculate - as opposed to sets of values based on the grouping in a table - and you want to put the results directly into variables.

In all other cases, my choice would be a SELECT into a cursor. But that's just a personal preference.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Surely CALCULATE is no improvement, but it's meant for setting single variables to results. And of course you can also do Sum(), Avg(), Min() and Max() in one single SQL-Select.

It's just an alternative way.

Bye, Olaf.

 

[IlyaRabyy]

Actually, colleagues, I have noticed quite a while ago that having aggregate functions (or at least SUM()) in an SQL-SELECT statement slows down the performance quite noticeably, the more so the larger the number of records in the source table. Yours truly ended up running a number of CALCULATE statements for the performance speed's sake, back then...
But that, I guess, is another topic.

Regards,

Ilya