Selecting the first 6 columns of a table by column order/ column id

Selecting the first 6 columns of a table by column order/ column id

Post by Joel Wagl » Wed, 11 Jul 2001 06:58:40



Hello,

I would like a generic way of selecting the first 6 columns of many
tables.  How would you do that?  I know syscolumns has all the column
names and column ID's and table ID's

It cannot be by name since I am doing this with n number of table with
m number of columns.  I want to list the first 6 (if there are that
many) of each table.

Thanks,
Joel

 
 
 

Selecting the first 6 columns of a table by column order/ column id

Post by BP Margoli » Wed, 11 Jul 2001 12:43:01


Joel,

You are going to have to use dynamic SQL to construct the query on the fly.
One can not reference columns positionally in SQL except in the ORDER BY
clause, and even that is being deprecated in ANSI SQL.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Hello,

> I would like a generic way of selecting the first 6 columns of many
> tables.  How would you do that?  I know syscolumns has all the column
> names and column ID's and table ID's

> It cannot be by name since I am doing this with n number of table with
> m number of columns.  I want to list the first 6 (if there are that
> many) of each table.

> Thanks,
> Joel


 
 
 

Selecting the first 6 columns of a table by column order/ column id

Post by Joe Celk » Wed, 11 Jul 2001 22:01:13


The whole idea of SQL is that you reference schema objects by names, and not by their PHYSICAL position.  In the old punch card days, we did the sort of thing you want to do inside 3GL programming languages.  The READ() statement would go to the start of the record, grab bytes, and put them into local variables int he order in which they PHYSICALLY appeared.  That is why "READ(a,b,c)" is not the same as "READ(c,a,b)", but "SELECT a, b, c FROM Foobar;" is the same as "SELECT c, a, b FROM Foobar;".  

Since officially the columns have no ordering within a row (just as the rows have no ordering in a table), what does the phrase "first six columns" mean?  You need to learn to think in terms of LOGICAL models.  

Yes, you can use the schema information tables to build a slow, meaningless query.  But why do you want to do this?  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!