Re: for SQL gurus - tricky syntax question

Try this:
 SELECT
 DECODE(column_id, 1 , 'SELECT ', ' ')    || UTC.column_name
   || DECODE (UTC.column_id, CC.column_count,    ' FROM '''||UTC.table_name||''';' , '||'','',')  FROM user_tab_columns UTC, column_counts CC  WHERE UTC.table_name = CC.table_name
   AND UTC.table_name = 'COMPANY'
 ORDER BY UTC.table_name;

It will return what you asked for. Please note that there will be multiple columns returned; based on the comma that you placed in the generated select statement. Your decode statement contained too many doubled up quotes and it wasn't parsing cleanly.

HTH
    James
In article <7mvvmp$68c$1_at_nnrp1.deja.com>,   kal121_at_my-deja.com wrote:
> The following SQL statement gives the result below:
>
> SELECT
> DECODE(column_id, 1 , 'SELECT ', ' ')
> || UTC.column_name
> || DECODE (UTC.column_id, CC.column_count,
> 'FROM'||UTC.table_name||';' , '||' ||''''','''''|| ',')
> FROM user_tab_columns UTC, column_counts CC
> WHERE UTC.table_name = CC.table_name
> AND UTC.table_name = 'COMPANY'
> ORDER BY UTC.table_name;
>
> THE RESULT is:
>
> SELECT ID||'','',
> NAME||'','',
> DUNS||'',''
> PREFERENCES FROM COMPANY;
>
> BUT, what I want is:
>
> SELECT ID||',',
> NAME||',',
> DUNS||','
> PREFERENCES FROM COMPANY;
>
> But when I do this:
>
> SELECT
> DECODE(column_id, 1 , 'SELECT ', ' ')
> || UTC.column_name
> || DECODE (UTC.column_id, CC.column_count,
> 'FROM'||UTC.table_name||';' , '||' ||'''',''''|| ',')
> FROM user_tab_columns UTC, column_counts CC
> WHERE UTC.table_name = CC.table_name
> AND UTC.table_name = 'COMPANY'
> ORDER BY UTC.table_name;
>
> I get this:
>
> SQL> SELECT
> 2 DECODE(column_id, 1 , 'SELECT ', ' ')
> 3 || UTC.column_name
> 4 || DECODE (UTC.column_id, CC.column_count,
> 5 'FROM'||UTC.table_name||';' , '||' ||'''',''''|| ',')
> 6 FROM user_tab_columns UTC, column_counts CC
> 7 WHERE UTC.table_name = CC.table_name
> 8 AND UTC.table_name = 'COMPANY'
> 9 ORDER BY UTC.table_name;
> 'FROM'||UTC.table_name||';' , '||' ||'''',''''|| ',')
> *
> ERROR at line 5:
> ORA-01722: invalid number
>
> Apparently, it doesn't like having only 4 of these: '
>
> Can somebody help?
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jul 19 1999 - 16:27:27 CDT