CODEWITHSUNDEEP
sqloracle-database
Michał Maroń
Michał Maroń

Reputation: 51

Oracle sql joining derived table with union

I have a query like this:

select       product_code,
             count(foo.container_id) as "quantity_of_containers",
             max((trunc(foo.update_date - foo.creation_date))) as "max_days"
from product
inner join stock on stock.product_id = product.product_id
inner join (
 select arch.container_id,
        arch.creation_date,
        arch.update_date
from arch
union all
select 
        container.container_id,
        container.creation_date,
        container.update_date
from container) foo on stock.container_id = foo.container_id
group by product_code
order by "max_days" desc

the question is what is wrong with this query, when i run it, it seems okay but after further inspection it looks like only records from container are there, for some reason there is not a single record from arch table. Is there different way to write this query with same logic behind it? i need to get records from both arch and container since one table is archive and another is current table. Also approach with unioning two separate queries on container and arch is working but i was looking for something faster.
@edit: im sending some sample data to make it more clear what i have actually meant.

+---------------+----------+----------+
| product_code  | quantity | max_days |
+---------------+----------+----------+
| 5999990024965 |      345 |       85 |
| 5999990027614 |      326 |       81 |
| 5999990023753 |       87 |       77 |
+---------------+----------+----------+

data from arch table,

+---------------+----------+----------+
| product_code  | quantity | max_days |
+---------------+----------+----------+
| 5999990082415 |       11 |       84 |
| 5999990059615 |        2 |       58 |
| 5999990023470 |        1 |       41 |
+---------------+----------+----------+

data from container table.

However when i run query i've pasted here i only got records from container table,
and yes stock.container_id really matches foo.container_ids (arch and container)

Upvotes: 4

Views: 1146

Answers (2)

Michał Maroń
Michał Maroń

Reputation: 51

i'm really sorry but it seems like the reason was software bug? after server restart query provided needed data. Thanks everyone for your time, seems like first query was actually correct.

Really sorry for taking your time, thank you everyone.

Upvotes: 1

Boneist
Boneist

Reputation: 23578

This isn't an answer, but it's too long to go in a comment.

What do you get when you run the following query?

select *
from product
inner join stock on stock.product_id = product.product_id
inner join (
 select arch.container_id,
        arch.creation_date,
        arch.update_date,
        'arch' qry
from arch
union all
select 
        container.container_id,
        container.creation_date,
        container.update_date,
        'container' qry
from container) foo on stock.container_id = foo.container_id
where foo.qry = 'arch';

Upvotes: 2

Related Questions