Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Format first and last record of result query

54373Jan 7 2009 — edited Jan 7 2009
Hello
I have the following query

<tt>select 1 seq, 'This is First record' data from dual union all
select 2, 'Data ' || tname from tab union all
select 3, 'This was last record Last record' from dual
order by 1</tt>

When i spool this statement to a listfile with col seq noprint option i get:

This is First record
Data MLA_ACCESS_LIST
Data MLA_APPLICATIONS
Data MLA_VPD_PCK
Data MLA_VPD_TABLES
This was last record Last record

But i want:

This is First record MLA_ACCESS_LIST
Data MLA_APPLICATIONS
Data MLA_VPD_PCK
MLA_VPD_TABLES This was last record Last record

I tried it with 1 statement with usage of lead and lag, because first and last record have to differ from the other result records. But i get ORA-30484: missing window specification for this function

Is this possible with 1 statement or am i doomed to edit the results by myself?

Thanks Auke
This post has been answered by 66470 on Jan 7 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 4 2009
Added on Jan 7 2009
1 comment
428 views