SELECTing a field by number? Unavoidable field named "Desc" is wreaking havoc on database queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECTing a field by number? Unavoidable field named "Desc" is wreaking havoc on database queries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-15 : 07:24:48

Ben writes "I am not the designer of the database and know that it is terrible design but this is unchanagable....

I have a very large table ICITEM with fields like:
ITEMNO
UNITS
UNITWGT
SERIALNO
OPTFLD1
DESC
...etc...

Ack! That last field "DESC" - what a terrible name for a field. I am in serious trouble. My application is on a web site page with vb asp.

I want to SELECT [ITEMNO], [OPTFLD1], [DESC] FROM ICITEM ORDER BY ITEMNO DESC

But I cannot do this because the DESC (description field) causes error. Currently I select * and then parse through each field and check to see if it is called DESC and if so, I get rsrecords(5) since it is the fifth field. However, selecting * is not good because it takes a very long time.

In SQL, is there a way to say:

"Please select the first, second and fifth fields. Thank you."

Pseudo coded like so:
SELECT ITEMNO AS itemNumber, UNITS as Units, DESC as Description

Any solutions to this horridly designed database and its frustrations?

Thanks.

SQL Server 2000.
IIS 4 on Win2k SP2."

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-15 : 07:42:44

Your pseudo code syntax is actually permitted. Did you try

SELECT [ITEMNO] as ItemNumber, [DESC] as Description

It should work fine.

Sam

dsdeming

479 Posts

Posted - 2003-05-15 : 08:04:38

Do you get the same error if you order by ItenNo ASC?

Edited by - dsdeming on 05/15/2003 08:05:47

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-15 : 08:31:12

Ironically, you can't select by a field number .. but you CAN order by a field #.

here's your options:

select field1, [desc]
from
Table
order by 1,2

-- OR --

Select field1, D
from
(select field1, [Desc] as D FROM Table) A
ORDER BY field1, D

Either should work fine. #2 might be better because it is more clear what you are doing.

Hope that helps.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources