locked
Get column name in SQL query

    Question

  • 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.

    Thursday, July 19, 2007 5:41 PM

Answers

  • 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

    Thursday, July 19, 2007 5:55 PM

All replies

  • 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

    Thursday, July 19, 2007 5:55 PM
  • 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.
    Thursday, July 19, 2007 6:47 PM
    Moderator
  • 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.

    Thursday, July 19, 2007 7:18 PM
  • 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
    Thursday, July 19, 2007 8:32 PM
    Answerer
  • 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.

    Thursday, July 19, 2007 9:09 PM
    Moderator
  • 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.

    Friday, July 20, 2007 10:32 AM
  • 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

    Friday, July 20, 2007 1:39 PM
    Moderator
  • "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 ...

     

    Friday, July 20, 2007 5:08 PM
  •  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
    Friday, July 20, 2007 7:03 PM
  • 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.

    Friday, July 20, 2007 7:29 PM
    Moderator
  • 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.

    Friday, July 20, 2007 7:37 PM
    Moderator
  •  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!!!
    Friday, July 20, 2007 7:47 PM
  •  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.
    Friday, July 20, 2007 7:52 PM
  • Hi Vladimir,

     

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

    Friday, July 20, 2007 7:52 PM
    Moderator
  • Ok, I agree. Let's consider my post then as a code improvement and a suggestion for the future use. I hope that the thread originator will check it.

     

    I do respect Alex, Tamar, Cetin and others in this forum greatly.

     

    For every technical problem we're seeking the best possible solutions - that's the goal.

    Friday, July 20, 2007 8:05 PM
    Moderator
  •  Naomi Nosonovsky wrote:

    Hi Vladimir,

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



    Hi Naomi,
    I read many others forums.
    Yes Evaluate()  is slower, but works.
    IMO Alex's answer isn't wrong, just different solution.

    Friday, July 20, 2007 8:16 PM
  • If you have two different solutions - one slower and one faster - which one would you prefer?
    Friday, July 20, 2007 8:28 PM
    Moderator
  •  Naomi Nosonovsky wrote:
    If you have two different solutions - one slower and one faster - which one would you prefer?


    Closing thread.  You've made your point.
    Friday, July 20, 2007 8:32 PM