Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Islamabad Pakistan
    Posts
    1

    Arrow Unanswered: Dynamic column names in a Query

    Hi,

    I want to build a dynamic query for a report. The Table eg Table1 from which I want to get the data has columns like col1, col2 col3... and their column names are stored in anoher table which have two columns colName and colAlias. What i want to do is to map the table1's column names to the data in the second table like in the result I wnat to get the "EmployeeName" instead of col1.

    Is this possible and if how, please reply it soon because it is very important for me.

    Regards,
    ______________
    Asif Raza Ashraf

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    It can be done.

    There are a couple of possible solutions.

    YOu could write dymanic sql putting the real columnname into a variable

    Something like this. (ignore syntax)

    select colname, alias into variables from 1sttable

    v_sql := 'select ' || var1 ||' as ' || var2 || ' the rest of query

    If more one field you would need to loop this building the string

    v_sql := v_sql || more ', ' || var1 ||' as ' || var2 ||

    ETC.

    I think that on the report side this may not be the fastest running. An may cause issues unless all col1 values are same size and type

    Also you could write the SQL into a table and call that table to return the entire select portion of the SQL based on selections from the report.

Posting Permissions

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