for SQL gurus - tricky syntax question

From: <kal121_at_my-deja.com>
Date: Mon, 19 Jul 1999 19:53:04 GMT
Message-ID: <7mvvmp$68c$1@nnrp1.deja.com>

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. Received on Mon Jul 19 1999 - 14:53:04 CDT