SELECT x columns
Is it possible to select all column except one without typing each column name ? We have a table with about 30 columns in it and I want to get all except the first one. I don't want have to rely on a pretyped script explicitly stating all columns as we sometimes have a need to add extra columns. I know I can list the column from syscolumns and match the tabid with that from systables but I need to format the select for the columns so it comes back as 1 row not a row per column. Any help / ideas appreciated. Brian Sent via Deja.com http://www.deja.com/ Before you buy.
What are you using for a front-end? If dbaccess/isql you'll just have to
type. If esql/c or some other programming language you can use brute
force (ie read systables/syscolumns). This is rather simple:
EXEC SQL DECLARE col_curs CURSOR FOR
SELECT colno, colname
FROM syscolumns sc, systables st
WHERE tabname = "sometable"
AND sc.tabid = st.tabid
AND colno > 1
ORDER BY colno;
EXEC SQL OPEN col_curs;
collist[0] = (char)0;
for (;;) {
EXEC SQL FETCH col_curs INTO :colno, :colname;
if (sqlca.sqlcode == SQLNOTFOUND)
break;
if (colno > 2)
strcat( collist, ", " );
strcat( collist, colname );
}
sprintf( statement, "SELECT %s FROM sometable .......", collist );
:::
Art S. Kagel
1900live@my-deja.com wrote:
>
> Is it possible to select all column except one without typing each
> column name ?
>
> We have a table with about 30 columns in it and I want to get all except
> the first one. I don't want have to rely on a pretyped script
> explicitly stating all columns as we sometimes have a need to add extra
> columns.
>
> I know I can list the column from syscolumns and match the tabid with
> that from systables but I need to format the select for the columns so
> it comes back as 1 row not a row per column.
>
> Any help / ideas appreciated.
>
> Brian
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.