Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Column List in Select query (oracle 11g linux)
Dynamic Column List in Select query [message #604989] Mon, 06 January 2014 15:23 Go to next message
deepa_balu
Messages: 74
Registered: March 2005
Member
Hi,

I have a table with columns A,B,C1,C2 ,D1 ,D2

I would like to write a select query that will not select columns starting with 'C'

Say

I need only

SELECT A,B,D1,D2 from table.

how do I dynamically select the column list.

I tried to create a string list without the columns starting with C but don't know how to use the string list as column.

select to_char(wm_concat(column_name)) from dba_tab_columns where table_name=REFERENCE' and
column_name not like 'C%' )
Re: Dynamic Column List in Select query [message #604991 is a reply to message #604989] Mon, 06 January 2014 15:34 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
One thing you can do is use the SQL from SQL approach, wrapping a valid SQL statement (yours is not) in the FROM part.
Re: Dynamic Column List in Select query [message #604993 is a reply to message #604991] Mon, 06 January 2014 15:40 Go to previous messageGo to next message
deepa_balu
Messages: 74
Registered: March 2005
Member
I am sorry I didn't understand. Can u show me an example?
Re: Dynamic Column List in Select query [message #605001 is a reply to message #604993] Mon, 06 January 2014 17:56 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
The select list is determined at run time. You can therefore use dbms_sql.
Re: Dynamic Column List in Select query [message #605002 is a reply to message #605001] Mon, 06 January 2014 18:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
write SQL that writes SQL; like below

set pages 0
spool capture.sql
SELECT 'SELECT ' 
       || Chr(39) 
       || table_name 
       || Chr(39) 
       || ' TABLE_NAME, COUNT(*) FROM ' 
       || table_name 
       || ' ;' 
FROM   user_tables 
WHERE  table_name NOT LIKE 'C%' ;
spool off
@capture.sql

[Updated on: Mon, 06 January 2014 18:30]

Report message to a moderator

Re: Dynamic Column List in Select query [message #605050 is a reply to message #605002] Tue, 07 January 2014 03:28 Go to previous message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
"BlackSwan Tue, 07 January 2014 01:26"
write SQL that writes SQL; like below...

Maybe I didn't understand the problem correctly. But according to what the OP said:
"deepa_balu Mon, 06 January 2014 22:23"
I tried to create a string list without the columns starting with C but don't know how to use the string list as column
What I understand from the bold part is that the purpose is not only generate the column list but also use this generated columns list as a select list of another select statement and actually run it at the same time. That's why I suggested dbms_sql

What you suggest just generates a set of select statements.

(Again, maybe I misinterpreted the problem Smile )
Previous Topic: Can a procedure have multiple OUT sy_refcursor
Next Topic: DML written on a script autocommits after a DDL in the same script without any commit statements?
Goto Forum:
  


Current Time: Thu Mar 28 04:19:18 CDT 2024