What is this code doing?

[mrpaulwass]

I'm looking at somebody elses code, and I'm tring to figure out what is going on?

What does the select 1 etc.
unioned with Select 2,1 etc doing?

Help!

Thanks,

Paul

select 1 order_by1, column_id order_by2,
decode(column_id, 1, 'select ', ' ||') ||
'''"''' || '||' || decode(data_type,
'NUMBER', 'to_char(',
'DATE', 'to_char(') || column_name ||
decode(data_type,
'NUMBER', ')',
'DATE', ',''DD-MON-YYYY'')')
|| ' || ' || '''",''' text
from all_tab_columns
where table_name = Upper('&tablename')
and data_type != 'LONG'
and owner = Upper('&Owner')
UNION
select 2, 1, ' from &tablename'
from dual
UNION
select 3, 1, '&whereclause;'
from dual
order by 1,2;

[Heath]

They're just using a "trick" to make sure that the rows returned from the first query in the statement come before the ones from the second query and before the rows from the third query. It is perfectly valid to specify a literal value in a select statement. BY "tagging" each row from each query with a numeric value, the ORDER BY at the end will ensure that the rows from the first query come first, the rows from the second one next and, finally, the rows from the third.

HTH,

Heath