Don't know the field name but do know the column order 
Author Message
 Don't know the field name but do know the column order
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



Wed, 20 Aug 2003 04:12:10 GMT
 Don't know the field name but do know the column order

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



Wed, 20 Aug 2003 05:18:08 GMT
 Don't know the field name but do know the column order
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



Wed, 20 Aug 2003 05:47:28 GMT
 Don't know the field name but do know the column order
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
Quote:

>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



Sat, 23 Aug 2003 08:31:35 GMT
 Don't know the field name but do know the column order
Hi Dave,

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

Quote:

>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)
>-----------------------------------------------------------------



Sat, 23 Aug 2003 08:39:48 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. SQL server name has been changed and don't know why

2. I don't know the full name of the director

3. I don′t know oracle, but i need know

4. I've just replaced the last dBA and don't know the sa password

5. Oninit didn't work fine and I don't know why

6. Funny Behaving Locate - don't know where's the problem

7. Don't know if it's possible : grouping

8. ODBC data source, need to know the column names and column types

9. ODBC data source, need to know the column names and column types

10. I don't know as returning a out parameter in a aplication java

11. DTS Packages may crash server, I don't know

12. Noob question, probably very simple (sorry I don't know this myself)


 
Powered by phpBB® Forum Software