Results 1 to 7 of 7

Thread: query

  1. #1
    Join Date
    Aug 2006
    Posts
    8

    Unanswered: query

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    1) No.
    2) No.

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Please explain Rahul ....

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

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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

  6. #6
    Join Date
    Sep 2006
    Posts
    19
    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));
    }
    }
    }

  7. #7
    Join Date
    Sep 2006
    Posts
    19
    Quote Originally Posted by dbMemo
    another question, is this valid? select var1 from table
    where var1 its a variable with the column name
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •