Query - General - Query with temperorary tables?

Hello,

ik have the following tables

Order
-----
Orderno
customer
note_id

orderline
------------
ordern
lineno
note_id
note_text

chargeline
------------
orderno
chargelineno
note_id

note
--------
note_id
note_text

An order can have more than one orderline and more than one chargeorderline. You can store a text at orderlevel, orderlinelevel and at chargelinelevel (is not mandatory). I want to create a report which can show all these 3 text per order (and only shows an order when a text is available). I tried it with the following query which put the text for the orderlines and the text for the chargeorderlines in a temperorary table first. But this doesn't seem to work. Does anyone has an idea? Thanks!

select c.ordernr,
c.klantnr,
d.notitie_text,
l.regelnr,
l.notitie_text,
nvl(s2.sequence_no,'') Toeslagregelnr,
nvl(s2.note_text,'') Toeslagregeltekst
from order c,
orderregel l,
notitie d,

(select c.ordernr, l.regelnr, l.notitietekst
from order c, orderregel l
where c.ordernr = l.ordernr
and l.notitie_text is not NULL) s1

(select c.ordernr, ch.toeslagregelnr, d.notitie_text
from order c, toeslagregel ch, notitie_text d
where c.ordernr = ch.ordernr
and ch.notitie_id = d.notitie_id) s2

where c.ordernr = sl.ordernr(+)
and c.ordernr = s2.ordernr (+)
and c.notitie_id = d.notitie_id

Try this. I started by simplifying the query - and if I understand the information you provided correctly - this might do what you want (i.e. only display rows where at least one test is available)

--

thanks for your reply (and sorry for my incomplete translation). It is still not working the way i would like. I found out that the note_id in orderline also refers to the note_id in table note (and not has a value in l.notitie_text). So I rebuild your query like this:

select c.ordernr,
c.klantnr,
d.notitie_text,
nvl(ch.sequencenr,''),
nvl(d2.notitie_text,''),
nvl(l.linenr,''),
nvl(d3.notitie_text,'')
from
order c
left join
notitie_text d on c.notitie_id = d.notitie_id
left join
orderregel l on c.ordernr = l.ordernr
left join
toeslagregel ch on c.ordernr = ch.ordernr
left join
notitie_text d2 on ch.notitie_id = d.notitie_id
left join
notitie_text d3 on l.notitie_id = d.notitie_id
where d.notitie_text is not null or d2.notitie_text is not null
or d3.notitie_text is not null

But now I don't get my orders with only a orderline_text or a chargeline_text. I only get the orders with a ordertext. I guess that has something to do with the first join. But i'm not sure because an order always has a notitie_id. Do you have any idea?

You made a slight mistake when joining d2 and d3. I've bolded my changes for you. This should work.

Thanks man! That's a good start of my day. It works fine now!!