Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: SELECT statement question

    Hi,

    I was wondering if there is a way to select columns by their relative position. I.E. I want to select the second and third column of a table w/o knowing their name.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    How do you define second and third column in a table ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Mar 2004
    Posts
    15

    more precisions...

    Lets immagine a table with 5 rows

    someID | someData1 | someDate2 | someDate | someInteger
    -------------------------------------------------------------------------

    I want to be able to get the equivalent of

    SELECT someData1,someData2 from someTable

    Even if I don't know the real column names, I only know they are in second and third position... like in the fictious following solution :

    SELECT [Column2],[Column3] from someTable

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Well
    I could give you a solution but I would like to ask you a question ... what if I decide to add another column in between the two columns ...

    What is the reason you want to select the columns by the no's
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You would need to write dynamic SQL that grabs column names from the schema tables or system tables. It'll be ugly.

    Smells like you have a data normalization problem to me.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Posts
    492

    Re: SELECT statement question

    syscolumns has a colorder-column that could help, but as mentioned, you might want to consider alternative ways to come to a cleaner solution althogether.

  7. #7
    Join Date
    Mar 2004
    Posts
    15

    more clearer clarifications

    This is not strictly a SQL problem... here's the why of this question...

    the original table I'm trying to get data from is on an old DBase format and I have absolutly no way of changing it's design, but I need to import those data on a regular basis and was planning to do so with the help of DTS. Everything seemed cool and fine until the point I noticed with amazement then with horror that 3 columns have the exact same name(a thing that SQL Server would never allow) !!!!!!!!! when I try to copy the content of each of those fields into a properly designed SQL Table the assistant get confused and can't handle the request properly. Hence my question, I now want to write a request in the form of

    SELECT [column1] as meaningfulname1, [column2] as meaningfulname2 from messedupDBaseExportedFile

    in order to be able to smoothly and automatically push the data into the new datawarehouse.

    No columns will never be added and the actual form of the table and order in which I get all the columns is set in stone and will never ever change. I fully understant why it's a BAD thing in a real world modern application but I MUST import those datas and it's not a one shot job so I need to be able to make it automatic.

    Hope that time I will get some answer and no more questions :-) Even if I understand your concerns.

  8. #8
    Join Date
    Feb 2004
    Posts
    492

    Re: more clearer clarifications

    I'm surprised that DBase allows tables sharing the same column name. However, there might be a explanation:
    following the DBase table specification, columns were allowed names up to 10 chars but newer versions introduced enhancements that increased this limit. I would think that this is the case. Perhaps DTS does not support this feature and truncates at 10.
    Which version is the DBase-db?
    As an alternative: can you create a view in the DBase-db that provides access to the table by different column names?

  9. #9
    Join Date
    Mar 2004
    Posts
    15

    Column Names Truncated

    Its not DTS who truncates the table names, it happens at the export time, from the original software who hosts the datas. The columns name are originally unique but longer than 10 chars and the exported file, in DBaseIII format, already has the column names truncated. The odd thing is that I cannot open the file in Access (cannot import a field more than once) but DTS is somehow able to manage that, and present me with the table structure with 3 equally named columns, and that was the reason why I thought I could create a SQL request that would be fit to push the datas in a SQL Table

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are many libraries that write "dBase" files that have nothing to do with the dBase product. My suspicion is that this "dBase" file may never have ever seen the "dBase" data engine!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •