Home » SQL & PL/SQL » SQL & PL/SQL » Query Problem (Oracle 10g)
Query Problem [message #329391] Wed, 25 June 2008 05:37 Go to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

I've been trying to figure this query out for 2 days and now have started going round in circles.

TABLE & DATA: -
create table transactions (
	id		number(9),
	trans_num	numeric(5),
	pricelevel_id	number(9)
)

insert into transactions values(1,12345,12);
insert into transactions values(2,12346,12);
insert into transactions values(3,12347,12);
insert into transactions values(4,12348,12);

create table trans_articles (
	transaction_id	number(9),
	article_id	number(9),
	price		numeric(10,2),
	qty		numeric(10,2),
	pricelevel_id	number(9),
	uom_id		number(9)
)

insert into trans_articles values(1,1,1.90,1,12,3);
insert into trans_articles values(2,1,1.50,1,14,3);
insert into trans_articles values(3,1,1.90,1,12,3);
insert into trans_articles values(4,2,1.50,1,14,,);

create table articles (
	id		number(9),
	code		varchar(20),
	description	varchar(20)
)

insert into articles values(1,'0001','Carling');
insert into articles values(2,'0002','Peroni');

create table prices (
	id		number(9),
	article_id	number(9),
	pricelevel_id	number(9),
	uom_unit_id	number(9),
	price		numeric(10,2)
)

insert into table prices values(1,1,1,3,1.90);
insert into table prices values(2,1,14,3,1.50);
insert into table prices values(3,2,1,,2.00);
insert into table prices values(4,2,14,,1.50);

create table uom_units (
	id		number(9),
	uom_group_id	number(9),
	factor		numeric(5,2),
	description	varchar(40),
	display_desc	varchar(40)
)

insert into uom_units values(3,1,1.00,'Pint','Pint');



What I'm trying to return...

I want to select the articles where the transaction pricelevel is different to the transaction article pricelevel and show the price from the transaction article table along with the price it is normaly.

However, you'll notice in the prices table that there aren't any records for the article with a pricelevel the same as the transactions pricelevel.

This is correct and not a mistake... The prices table holds a system price and then all other prices are taken from the system price, unless the user manually enters the price for that particular pricelevel manually.

Hope that makes sense?

So I'm not sure how I can pick up the price from the prices table when an article is taking its price from the system pricelevel (1) although it is using 12?

So the results should show: -

ar.code	ar.description	ta.qty	ta.price	pr.price
0001	Carling		1	1.50		1.90
0002	Peroni		1	1.50		2.00


If anyone could give me guidance on the above it would be very much appreciated.

Regards
Re: Query Problem [message #329410 is a reply to message #329391] Wed, 25 June 2008 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no constraint, pk and fk, on your tables so how could we know what are the relations between the columns of the tables?

What is the meaning of pricelevel_id in transactions table?
How does it happens that some articles are referenced in trans_articles and does not exist in articles?
Functionally, how could this be possible?
And so on...

Regards
Michel
Re: Query Problem [message #329416 is a reply to message #329410] Wed, 25 June 2008 06:44 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Michel Cadot wrote on Wed, 25 June 2008 11:31
There is no constraint, pk and fk, on your tables so how could we know what are the relations between the columns of the tables?

What is the meaning of pricelevel_id in transactions table?
How does it happens that some articles are referenced in trans_articles and does not exist in articles?
Functionally, how could this be possible?
And so on...

Regards
Michel



Hi,

Thanks for the reply.

Sorry for not making that clear in my post..

General:
--------
id                : A primary key field in a table.
[reftablename]_id : Foreign key field in a table.


The 'pricelevel_id' is reference to another table, which as far as I'm aware isn't need for the query, it holds the details of the different pricelevels in the system...

CREATE TABLE pricelevels(
 id                     NUMBER(9),
 code                   NUMERIC(8),
 description            VARCHAR(40)
)


The 'Articles' table holds all the products in the system. The Transactions table holds the 'Header' info of any sales made, and the Trans_Articles table holds details of the articles sold in that transaction.

Hope that makes the relationship of the tables a bit clearer?

Regards
Re: Query Problem [message #329436 is a reply to message #329416] Wed, 25 June 2008 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
At first sight I don't see why just joining the tables will not provide the result.
What did you already try?

Regards
Michel
Re: Query Problem [message #329440 is a reply to message #329416] Wed, 25 June 2008 07:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

So I'm not sure how I can pick up the price from the prices table when an article is taking its price from the system pricelevel (1) although it is using 12?


If the above statement is always true then you could achieve the same using scalar subquery (i.e) instead of joining prices along with your main select you could do something like this.
  1  select temp.code, temp.description, temp.qty, temp.price,
  2  (select prices.price from prices where prices.article_id = temp.article_id and pricelevel_id = 1) pr_price
  3  from
  4  (
  5  select art.code, art.description, ta.qty, ta.price, art.id article_id
  6  from
  7  transactions t,
  8  trans_articles ta,
  9  articles art
 10  where
 11  t.id = ta.transaction_id
 12  and
 13  ta.article_id = art.id
 14  and
 15  t.pricelevel_id != ta.pricelevel_id
 16* ) temp
SQL> /

CODE                 DESCRIPTION                 QTY      PRICE   PR_PRICE
-------------------- -------------------- ---------- ---------- ----------
0001                 Carling                       1        1.5        1.9
0002                 Peroni                        1        1.5          2

Hope that helps.

Regards

Raj

Re: Query Problem [message #329441 is a reply to message #329416] Wed, 25 June 2008 07:40 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Technical glitch Smile posted it twice.

[Updated on: Wed, 25 June 2008 07:41]

Report message to a moderator

Re: Query Problem [message #329451 is a reply to message #329391] Wed, 25 June 2008 08:29 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

Thanks for the reply.

Quote:

Quote:

So I'm not sure how I can pick up the price from the prices table when an article is taking its price from the system pricelevel (1) although it is using 12?



If the above statement is always true then you could achieve the same using scalar subquery (i.e) instead of joining prices along with your main select you could do something like this.



This statement is not always true, it is possible that the user could manual change the price in pricelevel 12, to make it different from pricelevel 1.

I tried the query you posted against my data and got an error: -

ORA-01427: single-row subquery returns more than one row


Quote:

Michel Cadot

At first sight I don't see why just joining the tables will not provide the result.
What did you already try?




I tried the following: -

select      tr.id, tr.trans_num, tr.pricelevel_id, ar.code, ar.description, um.id, um.description, ta.price, ta.pricelevel_id, pr.uom_unit_id, pr.price std_price
from        transactions tr, trans_articles ta, articles ar, prices pr, uom_units um
where       tr.id = ta.transaction_id
and         tr.pricelevel_id <> ta.pricelevel_id
and         ta.article_id = ar.id
and         ta.uom_id = um.id (+)
and         ta.article_id = pr.article_id
and         pr.pricelevel_id = '1'
and         ta.price <> pr.price
and         ta.article_id = '7441'
and         tr.shop_id = '5'
order by    tr.trans_num desc


And got...

        ID  TRANS_NUM PRICELEVEL_ID CODE                                                                             DESCRIPTION                                                                            ID_1 DESCRIPTION_1                                                                                                                                                         PRICE PRICELEVEL_ID_1 UOM_UNIT_ID  STD_PRICE
---------- ---------- ------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- --------------- ----------- ----------
    742153       3434            12 0004                                                                             CARLING                                                                                   4 Half Pint                                                                                                                                                              0.95              14           3        1.9 
    742153       3434            12 0004                                                                             CARLING                                                                                   4 Half Pint                                                                                                                                                              0.95              14           5        1.9 
    742153       3434            12 0004                                                                             CARLING                                                                                   4 Half Pint                                                                                                                                                              0.95              14          16        1.9 
    742144       3425            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14           3        1.9 
    742144       3425            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14           4       0.95 
    742144       3425            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14           6       0.95 
    742144       3425            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14          17       0.95 
    742144       3425            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14          16        1.9 
    742144       3425            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14           5        1.9 
    742143       3424            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14           3        1.9 
    742143       3424            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14           4       0.95 
    742143       3424            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14          16        1.9 
    742143       3424            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14          17       0.95 
    742143       3424            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14           6       0.95 
    742143       3424            12 0004                                                                             CARLING                                                                                   3 Pint                                                                                                                                                                    1.5              14           5        1.9 


Now the problem with this is that the 'Half Pints' are picking up the price of the 'Pint' in the last column, when in fact the half pint price is 0.95.

The other 'Pints' appear to be picking up all the prices where the pricelevel is 1, to expand in the prices table for this article i have: -

        ID ARTICLE_ID PRICELEVEL_ID UOM_UNIT_ID      PRICE FORMULA                                 
---------- ---------- ------------- ----------- ---------- ----------------------------------------
     30113       7441             1           3        1.9                                          
     30558       7441             1           4       0.95                                          
     30560       7441             1           5        1.9                                          
     30561       7441             1           6       0.95                                          
     30557       7441             1          16        1.9                                          
     30559       7441             1          17       0.95                                          
     30572       7441            14           3        1.5                                          
     30574       7441            14           5        1.5                                          
     30573       7441            14          16        1.5                                          


So, I have a transaction where the pricelevel is 12, however the 'Carling Pint' in the transaction is sold at pricelevel 14 (1.50 instead of the normal price of 1.90), this is what I need to return.

The prices table only holds pricelevel 1 (System Price) and all other pricelevels are calculated from level 1, the only time a record for another pricelevel is added to the prices table is if the user over writes the price for the associated pricelevel that has been calculated from level 1. Hope that makes some sense?

So although not visible in the table the system still has a level 12, but this is caluclated from level 1.

Like I said, I've been trying to get this to work for 2 days, I'm sure it's possible?

Regards
Re: Query Problem [message #329468 is a reply to message #329451] Wed, 25 June 2008 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Edit your post to fix your column size and keep your lines in 80 characters.

Regards
Michel
Re: Query Problem [message #329474 is a reply to message #329468] Wed, 25 June 2008 09:12 Go to previous message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

I would edit the post if I could see how?

How do I re-edit a previous post?

Regards
Previous Topic: SQL Query Rewrite
Next Topic: schema level
Goto Forum:
  


Current Time: Sun Dec 04 00:26:09 CST 2016

Total time taken to generate the page: 0.15794 seconds