Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Unanswered: Required to convert Oracle query to DB2 Query

    Any champ of query conversion can test his knowledge Below is the oracle query.
    Please see the attachment for desired result.

    SELECT cols.owner schema,
    cols.table_name,
    cols.column_name tableField,
    cols.data_type dataType,
    CASE WHEN cols.data_type = 'NUMBER' THEN cols.data_precision
    ELSE data_length
    END maxLength,
    CASE
    WHEN cols.data_type = 'NUMBER' THEN ''''||((cols.data_precision - cols.data_scale)||'''' ||','|| ''''||cols.data_scale||'''')
    ELSE TO_CHAR(data_length)
    END dataSize,
    cols.NULLABLE allowNull,
    cons.constraint_type isPrimaryKey
    FROM all_tab_columns cols
    LEFT OUTER JOIN (SELECT cons.table_name,cons.owner,cons.column_name, MIN(cons.constraint_type) constraint_type
    FROM (SELECT cons.table_name,cons.owner,cons.column_name, con.constraint_type
    FROM all_cons_columns cons
    INNER JOIN all_constraints con ON cons.table_name = con.table_name AND cons.owner = con.owner
    AND cons.constraint_name = con.constraint_name AND con.constraint_type IN ('P','R','U')
    AND cons.table_name = '(TableName)' AND cons.owner = '(SchemaName)'
    UNION
    SELECT idxcols.table_name,idxcols.table_owner,idxcols.col umn_name,'U' constraint_type
    FROM all_ind_columns idxcols
    WHERE EXISTS (SELECT 1
    FROM all_indexes idx
    WHERE idx.table_name = idxcols.table_name AND idx.table_owner = idxcols.table_owner
    AND idx.index_name = idxcols.index_name AND idx.uniqueness = 'UNIQUE'
    AND idx.table_name = '(TableName)' AND idx.table_owner = '(SchemaName)'
    )) cons
    GROUP BY cons.table_name,cons.owner,cons.column_name) cons
    ON cols.table_name = cons.table_name AND cols.owner = cons.owner AND cols.column_name = cons.column_name
    WHERE cols.table_name = '(TableName)'
    AND cols.owner = '(SchemaName)'
    ORDER BY cols.column_id
    Attached Thumbnails Attached Thumbnails Result_Image.jpg  

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You need to query SYSCAT.TABLES, SYSCAT.COLUMNS, SYSCAT.INDEXES, SYSCAT.INDEXCOLUSE.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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