How to write the SQL for this?

[skss]

I'm new to Oracle and I have the data I need linked together in 8 tables.

Region (RegionCode, RegionDesc)
Customer (CustomerNo, CustomerName, RegionCode)
OrderHeader (CustomerNo, OrderNo, OrderDate)
OrderDetail (OrderNo, ProdNo)
OrderHistoryHeader (CustomerNo, OrderNo, OrderDate)
OrderHistoryDetail (OrderNo, ProdNo)
Product (ProdNo, ProdDesc, SubGroupCode, SubCategoryCode)
CodeDesc (Code, CodeDesc)

SubGroupCode and SubCategoryCode are both linked to the Code field in the CodeDesc table. I know this is a bad design as the earlier database designer should have separated Group and Category into two different tables. Below is the list of columns I want:

1) RegionDesc
2) CustomerNo
3) CustomerName
4) ProdCode(from both the OrderDetail and OrderHistory table)
5) ProdDesc
6) CodeDesc(for SubGroup)
7) CodeDesc(for SubCategory)

I want this dataset as a result from one SQL statement, not two. I'm at a lost as how to write the SQL statement for this. I cannot change the structure of the database. Can someone please help? Thanks in advance.

 

[SantaMufasa]

SKSS,

I didn't take the extra time to build and populate sample tables in order to test this code, but here is what I came up with. If you run it and find any errors, my apologies; let me know how close it came:

select r.RegionDesc
, c.CustomerNo
, c.CustomerName
, p.ProdCode
, p.ProdDesc
, p.SubGroupCodeDesc
, p.SubCatCodeDesc
from Region r
, Customer c
, (select cu.CustomerNo
,pr.ProdCode
,pr.ProdDesc
,sg.CodeDesc SubGroupCodeDesc
,sc.CodeDesc SubCatCodeDesc
from Product pr
,OrderHeader oh
,OrderDetail od
,CodeDesc sg
,CodeDesc sc
,Customer cu
where cu.CustomerNo = oh.CustomerNo
and oh.OrderNo = Od.OrderNo
and od.ProdNo = pr.ProdNo
and pr.SubGroupCode = sg.Code
and pr.SubCategoryCode = sc.Code
Union
select cu.CustomerNo
,pr.ProdCode
,pr.ProdDesc
,sg.CodeDesc SubGroupCodeDesc
,sc.CodeDesc SubCatCodeDesc
from Product pr
,OrderHistoryHeader oh
,OrderHistoryDetail od
,CodeDesc sg
,CodeDesc sc
,Customer cu
where cu.CustomerNo = oh.CustomerNo
and oh.OrderNo = Od.OrderNo
and od.ProdNo = pr.ProdNo
and pr.SubGroupCode = sg.Code
and pr.SubCategoryCode = sc.Code) p
where r.RegionCode = c.RegionCode
and c.CustomerNo = p.CustomerNo
order by RegionDesc,CustomerName,ProdDesc
/

 

[skss]

Thank you for your quick reply. It is still not working. I get error ORA-00936: Missing expression. What could be wrong?

 

[SantaMufasa]

SKSS,

Please post the error message with the asterisk indicating for which clause there is a missing expression.

Dave

 

[skss]

SantaMufasa,
I've corrected all the typing errors in the sql statement. No more errors but it hangs when I try to execute it. Is it because my OrderHistoryDetail file is a really huge file?

 

[SantaMufasa]

SKSS,

Table size could definitely be a performance issue, but you should be able to reduce/eliminate that issue if you ensure that you have indexes on all primary keys and foreign keys.

Dave