Victor BA


How can i obtain all the records in a specific column like:

select field1 from table

but instead of the name "field1" use a column number i'm guessing :

select 2 from table // The number 2 being the third column (starting at 0)...

is this the right way

Thanks.




Re: Get column name in SQL query

Alex Feldstein


Why would you want to do that It sounds as bad design.

A data access method (in your data acess layer) should know the name of the column. If you get a column by position you are relying on the table(s) always having your desired column in the proper position.

That said, this is how you would do it. Assume here column 3 (column numbers in VFP start with 1 not 0)

Code Snippet

* open table

select 0

use MyTable

* get name of 3rd column at runtime

x = Field(3)

* do query on third column

Select evaluate(x) as Col3 from MyTable into cursor MyResult

HTH






Re: Get column name in SQL query

Naomi Nosonovsky

Alex,

Don't use evaluate here, use macro, since macro will perform faster in this context.

I've just responded on a different forum with this test:

CREATE CURSOR curTest (cField C(10))
FOR i = 1 TO 100000
INSERT INTO curTest VALUES ('AAAAAAA')
next
lcField = 'cField'
lcNewField = 'NewField'
lnSec = SECONDS()
SELECT evaluate(m.lcField) as (m.lcNewField) FROM curTest INTO CURSOR ('curTest1') nofilter
=MESSAGEBOX(SECONDS() - m.lnSec)
lnSec = SECONDS()
SELECT &lcField as (m.lcNewField) FROM curTest INTO CURSOR ('curTest2') nofilter
=MESSAGEBOX(SECONDS() - m.lnSec)
 
UPDATE. Apologize for using the word 'wrong' in the heat of the discussion.





Re: Get column name in SQL query

Alex Feldstein

Naomi,

No need to shout (big font). I have not seen the thread you mentioned. The answer was written on the fly with minimal testing (i.e. a very small table). Showing a more optimal solution is always good. I see you used macro instead of eval(). That's fine. Just no need to shout.





Re: Get column name in SQL query

Tamar E. Granor

In fact, in this case, you don't need EVAL() or a macro, just name substitution:

SELECT (field(3)) ...

****
I wanted to reply to myself, but can't find the Reply button. So, I'm wrong here. You can't use name expressions in the field list.

Tamar




Re: Get column name in SQL query

Naomi Nosonovsky

I didn't actually meant to shout, but somehow my initial response was in so unreadable font, that I have to change it.

I repeat, the only correct way here would be to write it as

select &lcFiled as (m.lcNewField) ...

My simple test should be enough to confirm it.





Re: Get column name in SQL query

CetinBasoz

Hi Naomi,

I just can't see why it's wrong. Evaluate() would work just fine. How does your sample show that it doesn't Name expression wouldn't work for fieldlist even if it's a single field, agreed, but evaluate() and & both work. & would only be faster since it'd be expanded once at start.





Re: Get column name in SQL query

Naomi Nosonovsky

Cetin,

evaluate would be executed every time, while macro is performed only once. It is known, that in select-SQL constructs as well as in

scan for &lcFor for example you should not use evaluate for performance reason though both would work.

My example shows it plainly.

UPDATE. Just want to give a link to another forum where this problem is discussed in more details (the forum requires a registration, though):

http://www.universalthread.com/wconnect/wc.dll 2,15,1241954





Re: Get column name in SQL query

dni

"I didn't actually meant to shout"
...that explanation insults my intelligence...

"Alex,
The answer is wrong! Don't use evaluate here, use macro!"
"But now Tamar is repeating the same error and in a minute I would shout"

Your atitude is like you are the owner of the forum , main host or super main moderator OR a
Vfp super guru. If so just let us know because until now I noticed only excellent internet seeker
skills ...






Re: Get column name in SQL query

Phil Brammer

Naomi Nosonovsky wrote:

Alex,

The answer is wrong! Don't use evaluate here, use macro!

I've just responded on a different forum with this test:

CREATE CURSOR curTest (cField C(10))
FOR i = 1 TO 100000
INSERT INTO curTest VALUES ('AAAAAAA')
next
lcField = 'cField'
lcNewField = 'NewField'
lnSec = SECONDS()
SELECT evaluate(m.lcField) as (m.lcNewField) FROM curTest INTO CURSOR ('curTest1') nofilter
=MESSAGEBOX(SECONDS() - m.lnSec)
lnSec = SECONDS()
SELECT &lcField as (m.lcNewField) FROM curTest INTO CURSOR ('curTest2') nofilter
=MESSAGEBOX(SECONDS() - m.lnSec)


Naomi,
Understanding your font issues, to declare Alex's post as "wrong" is incorrect and in the future you should refrain from making such claims. Does Alex's solution work, albeit in perhaps a not-so-efficient manner Maybe. That doesn't make it wrong -- just different.

Phil Brammer
Forums Moderator





Re: Get column name in SQL query

Naomi Nosonovsky

Hi Phil,

If the solution is unefficient, it's wrong in my book. May be the word 'wrong' is too strong then, I apologize, but I wanted to correct a post so the thread originator would not use this syntax in the future.

That Alex knows it and it was just a quick answer on a question - I have no doubt.

When I'm wrong I have no problems admitting it.

As for the font issue - that was a case very similar to the post by Tamar in another thread. Thus I had to correct the problem and I choose larger from the font size menu.





Re: Get column name in SQL query

Naomi Nosonovsky

BTW, there is another thread in this forum, where the answer is marked as a solution, but actually the asnwer is incorrect from the technical point of the view and I was bitten by it just recently.

When we're dealing with the computer code / programs - I'm thinking from the technical point of view first. I never want to offend anyone with my remarks, but I want to get the technical point and so the solutions provided should be correct or at least have a comment, that they were not tested, so may not work as intended.





Re: Get column name in SQL query

Vladimir Zografski

Phil Brammer wrote:
Naomi Nosonovsky wrote:

Alex,

The answer is wrong! Don't use evaluate here, use macro!

I've just responded on a different forum with this test:

CREATE CURSOR curTest (cField C(10))
FOR i = 1 TO 100000
INSERT INTO curTest VALUES ('AAAAAAA')
next
lcField = 'cField'
lcNewField = 'NewField'
lnSec = SECONDS()
SELECT evaluate(m.lcField) as (m.lcNewField) FROM curTest INTO CURSOR ('curTest1') nofilter
=MESSAGEBOX(SECONDS() - m.lnSec)
lnSec = SECONDS()
SELECT &lcField as (m.lcNewField) FROM curTest INTO CURSOR ('curTest2') nofilter
=MESSAGEBOX(SECONDS() - m.lnSec)


Naomi,
Understanding your font issues, to declare Alex's post as "wrong" is incorrect and in the future you should refrain from making such claims. Does Alex's solution work, albeit in perhaps a not-so-efficient manner Maybe. That doesn't make it wrong -- just different.

Phil Brammer
Forums Moderator


Hi Phil,
I totally agree with your opinion!!!





Re: Get column name in SQL query

Phil Brammer

Naomi Nosonovsky wrote:

Hi Paul,

If the solution is unefficient, it's wrong in my book. May be the word 'wrong' is too strong then, I apologize, but I wanted to correct a post so the thread originator would not use this syntax in the future.

That Alex knows it and it was just a quick answer on a question - I have no doubt.

When I'm wrong I have no problems admitting it.

As for the font issue - that was a case very similar to the post by Tamar in another thread. Thus I had to correct the problem and I choose larger from the font size menu.



Then I'd suggest adding an errata to your book. Having an inefficient solution is not wrong. So please keep that in mind when posting in the future and respect other posters. Just because it is inefficient doesn't mean the original user wouldn't want to use that solution. Sometimes a user just needs a one-time solution and is happy to use the first answer that comes along. If you want to post code improvements so that the user can learn his/her options, then by all means post another solution and explain why yours might be better.

I'm not going to address the font issue. That is a known forum bug and is irrelevant in this discussion.

Enough said.





Re: Get column name in SQL query

Naomi Nosonovsky

Hi Vladimir,

Did you run the test Try it with a bigger table.