Very Computer

by **John Shearin** » Sun, 04 Mar 2001 05:12:10

Hi All,
I need to write a select statement that will select columns from a table
based on the column order.
For instance:
If I had a table called (MyTable) and it had 3 fields call MyField1,
MyField2 and MyField3
I would like to write a select statement the selects the data from MyField3
with a select statement like this:
SELECT 3 FROM MyTable.

I need to do this because I do not have the name of the field at run time
but I will know the position of the column in the table.

Thanks, John

by **Rob Morron** » Sun, 04 Mar 2001 06:18:08

The Names of the fields are included in the META data from the recordset.

One method that I have used is to execute the following:

recordSet.Open "SELECT * FROM MyTable WHERE 1=2", ConnectionString

1 will never equal 2, so what is returned is an empty recordset that still
contains all of the META data.

recordSet(3).name will be the name of the column.
--

Rob Morrone
http://www.webstrands.com

Quote:> Hi All,
> I need to write a select statement that will select columns from a table
> based on the column order.
> For instance:
> If I had a table called (MyTable) and it had 3 fields call MyField1,
> MyField2 and MyField3
> I would like to write a select statement the selects the data from
MyField3
> with a select statement like this:
> SELECT 3 FROM MyTable.

> I need to do this because I do not have the name of the field at run time
> but I will know the position of the column in the table.

> Thanks, John

by **DaveSat** » Sun, 04 Mar 2001 06:47:28

something that uses something like this ?:

SELECT TABLE_NAME + ".." + COLUMN_NAME
FROM information_schema.columns
WHERE TABLE_NAME = "dtproperties"
AND ORDINAL_POSITION = 5
--
HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------

Quote:> Hi All,
> I need to write a select statement that will select columns from a table
> based on the column order.
> For instance:
> If I had a table called (MyTable) and it had 3 fields call MyField1,
> MyField2 and MyField3
> I would like to write a select statement the selects the data from
MyField3
> with a select statement like this:
> SELECT 3 FROM MyTable.

> I need to do this because I do not have the name of the field at run time
> but I will know the position of the column in the table.

> Thanks, John

by **John Shearin** » Wed, 07 Mar 2001 09:31:35

Hi Rob,
That is a good trick.
Unfortunatly I am trying to do all this work from inside the stored
procedure. This is because I am trying to keep all my business logic on the
back end.
Thanks, John

>The Names of the fields are included in the META data from the recordset.

>One method that I have used is to execute the following:

>recordSet.Open "SELECT * FROM MyTable WHERE 1=2", ConnectionString

>1 will never equal 2, so what is returned is an empty recordset that still
>contains all of the META data.

>recordSet(3).name will be the name of the column.
>--

>Rob Morrone
>http://www.webstrands.com

by **John Shearin** » Wed, 07 Mar 2001 09:39:48

Hi Dave,

Yes this is what I am looking for.
Thanks for your help,
John

>something that uses something like this ?:

>SELECT TABLE_NAME + ".." + COLUMN_NAME
>FROM information_schema.columns
>WHERE TABLE_NAME = "dtproperties"
>AND ORDINAL_POSITION = 5
>--
>HTH,
>David Satz
>Principal Software Engineer
>Hyperion Solutions
>->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------

Very Computer

Don't know the field name but do know the column order

Don't know the field name but do know the column order

Don't know the field name but do know the column order

Don't know the field name but do know the column order

Don't know the field name but do know the column order

Don't know the field name but do know the column order