Re: Variable number of fields in SELECT clause?

Use Dynamic SQL if you can, its so much less painful!

Otherwise, you may have to use a DECODE statement and output your field if it meets a certain condition, otherwise output a NULL. Its a very crude way, i did do it once in an Oracle Report and have the field collapse if it was null, thus giving the impression of 'variable number of fields'.

M

robin_at_topniveau.net (Hamm) wrote in message news:<c77120c.0201170834.17ff6772_at_posting.google.com>...
> Hi,
>
> I have the following problem I am trying to solve without reverting to
> dynamic SQL.
>
> Is it possible to have a variable number of fields in the SELECT
> clause, where the number of fields depends on the kind of value the
> field has in a corresponding table.
>
> So lets say I have this table called 'Transactions', each records is
> depicts the settings of a 'transaction'.
>
>
> FIELD1, FIELD2, FIELD3, FIELD4
> 1 1 0 1
>
>
> This means I want to make a query where FIELD1, FIELD2 and FIELD4
> appear in the SELECT clause (these fields refer to an other table with
> the exact same field names, ultimately quering that other table for
> only those fields which have been enabled with '1' in this
> transactions table).
>
> I started with something like:
>
> SELECT alias.* FROM (SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME =
> 'TRANSACTIONS' <and has '1' as data>) ALIAS
>
> Obviously the part between brackets is what I cant figure out. Anyway
> easy to refer to the data of all columns in the same statement, trying
> to avoid a massive OR statement?
Received on Fri Jan 18 2002 - 02:47:47 CST