Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Cheers SA will try it out, and it is for a few scripts I can leave with a readme that says run this script and save the output as a flat file. That way others can't screw it up later on (Well OK they can, but it should be much harder...)
It works SA, but my ORDER BY breaks it now. Is there a way to do this whil ordering the statement in the middle?
If the statement in the middle is a varchar and you want it alphabetically, it should do this by default.
e.g.
select 'me'||CHR(13) || CHR(10) l_header from dual
UNION ALL
select distinct last_name
from per_people_f
where last_name like 'Brow%'
group by last_name
UNION ALL
Select 'him'||CHR(13) || CHR(10) l_footer
from dual
gave me: -
L_HEADER
--------------------
me
Browbank
Browell
Brown
Brown
Brownbridge
Browne
Browne-Cole
Brownell
Brownhill
Browning
Brownlie
Brownlow
Brownrigg
Brownsea
him
And whilst you're on, can you explain the SQL92 joins to me? You know, UNION, LEFT, RIGHT, yadda, yadda, yadda...
Never understood 'em
You and me both. Take the existing join mechanisms that make sense and everybody understands, and replace them with something that makes no sense and is therefore difficult to understand and learn.
If the statement in the middle is a varchar and you want it alphabetically, it should do this by default.
e.g.
select 'me'||CHR(13) || CHR(10) l_header from dual
UNION ALL
select distinct last_name
from per_people_f
where last_name like 'Brow%'
group by last_name
UNION ALL
Select 'him'||CHR(13) || CHR(10) l_footer
from dual
gave me: -
L_HEADER
--------------------
me
Browbank
Browell
Brown
Brown
Brownbridge
Browne
Browne-Cole
Brownell
Brownhill
Browning
Brownlie
Brownlow
Brownrigg
Brownsea
him
or is it a different order you want?
It's a numeric field I'm ordering by. Surely if I put in a group b I have to specify which of the initial fields are part of the group by because it will error if I don't?
God I hate Oracle sometimes, why can't it be as easy as some of the other flavours of SQL out there... (More because I learnt SQL using MySQL Postgres and MSSQL and Oracle doesn't quite match up with it's group by and limit commands)
You and me both. Take the existing join mechanisms that make sense and everybody understands, and replace them with something that makes no sense and is therefore difficult to understand and learn.
I tried them once - didn't like it, didn't bother again...
The squint, the cocked eye and clenched first are the cornerstones of all Merseyside communication from birth to grave
you can sort out the ordering like this but it's rather ugly
select mydata from
(
select 1 recordtype, 'my header' mydata
from dual
union
select 2 recordtype, "field 1 " || field 2 "" mydata
from mytable
union
select 3 recordtype, 'my footer' mydata
)
order by recordtype;
Comment