Row position in select statement

[mdl2]

SELECT ROWNUM,
('TO_CHAR(' || COLUMN_NAME || ') AS FIELD' || ROWNUM) AS COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME LIKE V_TABLE
ORDER BY ROWNUM

The previous statement is not consistant across databases
(production versus test -- field1 on test may be field 5 on production) I was hoping to order by column_name but I don't know how to get the position of the record) Does anyone know of a way I can get the position of record in the select clause.

 

[BJCooperIT]

Hopefully this is what you mean by "position of record".

CREATE TABLE mytab1
 (flda NUMBER, fldb DATE, fldc VARCHAR2(5));
CREATE TABLE mytab2
 (fldb DATE, fldc VARCHAR2(5), flda NUMBER);

SELECT table_name, column_id,
       ('TO_CHAR(' || column_name || ') AS FIELD' || rownum) AS column_name,
FROM   all_tab_columns
WHERE  table_name LIKE 'MYTAB%'
ORDER  BY table_name, column_id;

TABLE_NAME   COLUMN_ID   COLUMN_NAME
MYTAB1       1           TO_CHAR(FLDA) AS FIELD1
MYTAB1       2           TO_CHAR(FLDB) AS FIELD2
MYTAB1       3           TO_CHAR(FLDC) AS FIELD3
MYTAB2       1           TO_CHAR(FLDB) AS FIELD4
MYTAB2       2           TO_CHAR(FLDC) AS FIELD5
MYTAB2       3           TO_CHAR(FLDA) AS FIELD6

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

MDL2, please forgive my dimness, but I need your help clarifying your need:

I was hoping to order by column_name but I don't know how to get the position of the record

Your needs seem to be literally at cross purposes...column_name is on the "X axis" and record position is on the "Y axis".


Do you mean that you are have trouble dealing with the fact that column_names are in different positions within each record description?...And you want to know how to reconcile those positional differences?

If that is the case, I'm wondering why column position within record description even matters.

Perhaps it's just early in the morning and I'm not firing on all cylinders yet.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]