Home » SQL & PL/SQL » SQL & PL/SQL » Sub Query Assistance (Oracle 10g)
Sub Query Assistance [message #327766] Tue, 17 June 2008 08:55 Go to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
I’m struggling to work out where I need a ‘Sub Query’ for the following that I’m trying to achieve.

I have a tables (admin_modification_log & admin_modification_fields) which saves any modifications made to other parts of the database by users.

In addition to these two tables I’m using a further 3 (articles, uom_units & operators).

CREATE TABLE admin_modification_log (
 id                     NUMBER(9)       CONSTRAINT pk_admin_modification_log
                                        PRIMARY KEY
                                        CONSTRAINT ck_adminmodlog_id
                                        CHECK (id > 0),
 table_name             VARCHAR(40)     NOT NULL,
 operation              NUMERIC(1)      NOT NULL
                                        CONSTRAINT ck_adminmodlog_operation
                                        CHECK (operation IN (1,2,3)),
 operator_id            NUMBER(9)       CONSTRAINT fk_adminmodlog_operators
                                        REFERENCES operators(id),
 terminal_name          VARCHAR(60)     NOT NULL,
 operation_timestamp    DATE            NOT NULL
)
;

CREATE TABLE admin_modification_fields (
 admin_modification_log_id NUMBER(9)    NOT NULL
                                        CONSTRAINT fk_admin_modification_fields
                                        REFERENCES admin_modification_log(id)
                                        ON DELETE CASCADE,
 field_name             VARCHAR(40)     NOT NULL,
 old_value              VARCHAR(2000),
 new_value              VARCHAR(2000)
);


Articles Table

ID
Code
Description
UOM_GROUP_ID

UOM_UNITS Table

ID
UOM_GROUP_ID
Factor
Description

Operators Table

ID
Description


My query so far is: -

select      ar.id art_id, ar.description, mf1.old_value old_val, mf1.new_value new_val,
            op.description op_desc, ml.operation_timestamp date_time, ml.terminal_name terminal
from        admin_modification_fields mf1, admin_modification_log ml, articles ar, operators op
where       ml.id = mf1.admin_modification_log_id
and         ar.id in (select old_value from admin_modification_fields mf2 where field_name = 'ARTICLE_ID' and admin_modification_log_id = ml.id)
and         ml.table_name = 'PRICES'
and         mf1.field_name = 'PRICE'
and         ml.operator_id = op.id


This shows any price changes made by an operator: -

         1 Fosters             2.7        2.8        Admin   2-Jun-2008 11:50:21       LTOP02
         2 Stella               3          2.95      Admin   2-Jun-2008 12:24:17       LTOP02
         2 Stella               1.5        1.55      Admin   2-Jun-2008 12:27:21       LTOP02
        14 WKD Blue         2.7        2.75      Admin   2-Jun-2008 12:28:36       LTOP02


However what I’d like to show in addition is the ‘UOM’ (Unit of Measure) if there is one, in the case of the ‘Fosters’ & ‘Stella’.

My question is how can I do this?

If there are any questions or anything else is needed, let me know.

Thanks & Regards


Re: Sub Query Assistance [message #327789 is a reply to message #327766] Tue, 17 June 2008 11:03 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How about posting some test data ?

Regards

Raj
Re: Sub Query Assistance [message #327894 is a reply to message #327789] Wed, 18 June 2008 05:17 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

Here is some sample data: -

Create table admin_modification_log (
	id			number(9),
	table_name		varchar(40),
	operation		numeric(1),
	operator_id		number(9),
	terminal_name		varchar(60),
	operation_timestamp	date
);

INSERT into admin_modification_log values (1,'PRICES',2,1,'LTOP02',to_date('2008/06/17 08:00:44', 'yyyy/mm/dd hh24:mi:ss'));
INSERT into admin_modification_log values (2,'PRICES',2,1,'LTOP02',to_date('2008/06/17 08:01:44', 'yyyy/mm/dd hh24:mi:ss'));
INSERT into admin_modification_log values (3,'PRICES',2,1,'LTOP02',to_date('2008/06/17 08:02:44', 'yyyy/mm/dd hh24:mi:ss'));


create table admin_modification_fields (
	admin_modification_log_id	number(9),
	field_name			varchar(40),
	old_value			varchar(2000),
	new_value			varchar(2000)
);

INSERT into admin_modification_fields values (1,'ID','30124','30124');
INSERT into admin_modification_fields values (1,'ARTICLE_ID','7453','7453');
INSERT into admin_modification_fields values (1,'PRICE','2.2','2.1');
INSERT into admin_modification_fields values (1,'UOM_UNIT_ID','','');
INSERT into admin_modification_fields values (2,'ID','30111','30111');
INSERT into admin_modification_fields values (2,'ARTICLE_ID','7439','7439');
INSERT into admin_modification_fields values (2,'PRICE','2','2.1');
INSERT into admin_modification_fields values (2,'UOM_UNIT_ID','3','3');
INSERT into admin_modification_fields values (3,'ID','30110','30110');
INSERT into admin_modification_fields values (3,'ARTICLE_ID','7438','7438');
INSERT into admin_modification_fields values (3,'PRICE','2.1','2.25');
INSERT into admin_modification_fields values (3,'UOM_UNIT_ID','3','3');

CREATE TABLE OPERATORS (
	ID		NUMBER(9),
	CODE		NUMBER(8),
	DESCRIPTION	VARCHAR2(40)
);

INSERT INTO OPERATORS VALUES (1,1234,'ANDREW');

CREATE TABLE ARTICLES (
	ID		NUMBER(9),
	CODE		VARCHAR2(20),
	DESCRIPTION	VARCHAR2(20)
);

INSERT INTO ARTICLES VALUES (7453,'0001','WKD BLUE');
INSERT INTO ARTICLES VALUES (7439,'0002','STELLA');
INSERT INTO ARTICLES VALUES (7438,'0003','FOSTERS');

CREATE TABLE UOM_UNITS (
	ID		NUMBER(9),
	DESCRIPTION	VARCHAR2(40)
);

INSERT INTO UOM_UNITS VALUES (3,'PINT');




Hopefully I haven't missed anything.

Regards
Re: Sub Query Assistance [message #327899 is a reply to message #327894] Wed, 18 June 2008 06:05 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
All you need is an outer join to the uom_units table. It will be something like this.
select
..., uom_units.description
articles , uom_units
where
to_number(articles.code) = uom_units.id (+)

Refer this link for more information.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#sthref3175

Regards

Raj

P.S : It's a very bad idea of storing numbers in a string. There is very high probability of you hitting ora-1722. So beware.
Re: Sub Query Assistance [message #327909 is a reply to message #327899] Wed, 18 June 2008 06:24 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Thanks for the reply.

Here is what I currenlty have: -

select      ar.id art_id, um.description ||' '|| ar.description, mf1.old_value old_val, mf1.new_value new_val,
            op.description op_desc, ml.operation_timestamp date_time, ml.terminal_name terminal
from        admin_modification_fields mf1, admin_modification_log ml, articles ar, operators op, uom_units um
where       ml.id = mf1.admin_modification_log_id
and         ar.id in (select old_value from admin_modification_fields mf2 where field_name = 'ARTICLE_ID' and admin_modification_log_id = ml.id)
and         ml.table_name = 'PRICES'
and         mf1.field_name = 'PRICE'
and         ml.operator_id = op.id
and         um.id = (select old_value from admin_modification_fields where field_name = 'UOM_UNIT_ID' and admin_modification_log_id = ml.id)


However this only returns records where there is a UOM_UNIT_ID. I need it to also return any thing with out one.

Regards.
Re: Sub Query Assistance [message #327940 is a reply to message #327909] Wed, 18 June 2008 07:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It means you didn't the read the link I have posted. Read the link again.

Regards

Raj
Re: Sub Query Assistance [message #327947 is a reply to message #327766] Wed, 18 June 2008 07:30 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

Have read the link you posted, and unless I've miss read it I'm not able to use the outer join within my query as it currently is, in particular on the following line: -

and         um.id = (select old_value from admin_modification_fields where field_name = 'UOM_UNIT_ID' and admin_modification_log_id = ml.id)


So if I'm not able to use the outer join here, how can achieve the results I want?

Regards
Re: Sub Query Assistance [message #327965 is a reply to message #327947] Wed, 18 June 2008 08:06 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Then try to do it an alternative fashion. As far as I know there is no limitation on the number of tables you can join. Clue is self join .

Regards

Raj
Re: Sub Query Assistance [message #327990 is a reply to message #327766] Wed, 18 June 2008 09:39 Go to previous message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Raj,

Thanks for the reply and the clue, although it has melted my tiny grey matter further Confused

I'll leave it for now and leave the UOM Description out.

Thanks once again.
Previous Topic: allocating grades
Next Topic: More Efficient Top N
Goto Forum:
  


Current Time: Sun Dec 04 22:56:05 CST 2016

Total time taken to generate the page: 0.05952 seconds