Another One For You Budding Oracle Guru's

[SallyAnne]

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...)

No worries - give me a shout if it doesn't work.

And dont forget the DISTINCT.

[Ardesco]

No worries - give me a shout if it doesn't work.

And dont forget the DISTINCT.

I won't I only want 1 copy of each record not a million or so

[Ardesco]

It works SA, but my ORDER BY breaks it now. Is there a way to do this whil ordering the statement in the middle?

[SallyAnne]

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



or is it a different order you want?

[chicane]

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.

[Ardesco]

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)

[EqualOpportunities]

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...

[thunderlizard]

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;

[Weltchy]

<<Slaps hand against forehead>>

You guys are honestly contractors?

[SallyAnne]

<<Slaps hand against forehead>>

You guys are honestly contractors?

It's frightening isn't it?