Hi everyone,

I need to create a query (select) but i cannot use the columns names, I need to use the column number, example

select col1, col2, col3 from table

where 1, 2, and 3 are the column position in a table, is this possible???

another question, is this valid? select var1 from table
where var1 its a variable with the column name

thanks a lot for your help

1) No.
2) No.

while going through some another db2 forum i found following query

WITH result(level, text) AS (
SELECT 1, colname
FROM syscat.columns
WHERE tabname = 'TDB_PATCH_DTL' AND tabschema = 'EONE' AND colno = 0
UNION ALL
SELECT level+1, r.text ||', '|| colname
FROM syscat.columns c, result r
WHERE tabname = 'TDB_PATCH_DTL' AND tabschema = 'EONE' AND c.colno =
r.level
)
SELECT 'SELECT '||text||' FROM '||'EONE.TDB_PATCH_DTL' from result
ORDER BY level DESC
FETCH FIRST 1 ROW ONLY;

or

WITH result(level, tabname, tabschema, text) AS (
SELECT 0, tabname, tabschema, CAST('' AS VARCHAR(4000))
FROM syscat.tables
WHERE tabschema = 'EONE'
and tabname like 'T%'
AND type = 'T'
UNION ALL
SELECT level+1, r.tabname, r.tabschema, r.text ||', '||
RTRIM(colname)
FROM syscat.columns c
, result r
WHERE level < 10000
AND c.tabname = r.tabname
AND c.tabschema = r.tabschema
AND c.colno = r.level
)
SELECT 'SELECT '||SUBSTR(text,3)||' FROM '||RTRIM(r.tabschema)||'.'||RTRIM(r.tabname)||' ;'
FROM result r
, syscat.tables t
WHERE t.tabname = r.tabname
AND t.tabschema = r.tabschema
AND t.colcount = r.level

Please explain Rahul ....

I don't seem to get your point ... Or is it only me ??

Sathyaram

its basically using CTE (recursive query ) to generate select statements

just replace tabname and tabschema with any table in sample database
and see the output.
second query will help to get full select statement for all tables in a schema

--Rahul Singh

Rahule,

Where you want to run this query.
Kindly answere the following questions

1) In DB Objects like stored procedure or Triger etc?
2) At client side(java,C# etc), provide the query and get the results.?
3) What about where cluase. different tables whill have different joines?

If you are using some client then almost all the DB clients provide this facility.
Here are the steps.
1) Create statement.
2) Execute query.
3) Get Result set.

var numberofcolumns = get number of coulums from result set.

while (resultset.next()){
for(int i=1; i<=numberofcolumns; i++){
system.out.println("col"+i+"="+resultset.get(i));
}
}
}

Yes you can get table meta information at client side.

In java JDBC provide some details to get the metadata information
1) you can get DatabaseMetaData
2) ParameterMetaData
3) ResultSetMetaData

In other clients(C# etc) there shuld be some mechanism..

Istikhar