Last post Mar 10, 2012 06:57 AM by os_os
Feb 04, 2012 07:54 AM|os_os|LINK
I Have oralce DataBase for engineering offices,
and there are about 10 activities for these engineering offices
(e.g architecture , civil engineering, mechanical engineering , .... and so on)
i want to fetch those engineering offices which only and only have one activity (e.g civil engineering )
pls need suggestion .. whether to use cursor or normal pl sql block .
Feb 04, 2012 09:15 AM|abhijeetmishra|LINK
This can be done through a simple select statement. Why you want to use cursor and loop the data.
Can you please elaborate your requirements in detail, otherwise its a plain select statement.
Feb 05, 2012 03:17 AM|os_os|LINK
thanks alot for your reply .
i have tried to do that with plain select statement but i have got the list of all engineering offices whih have (e.g civil engineering) but my requirement to fetch a list of engineering office which only and only have civil engineering activiy.
one office -------> can have ------> many activies .
table 1 : office_info
table 2 : office_activity(arch. activity, civil activity, ....... so on)
Feb 27, 2012 07:39 AM|os_os|LINK
any suggestions or example for this matter .
Feb 27, 2012 07:48 AM|Sum8|LINK
SELECT * FROM office_info A
INNER JOIN office_activity B ON A.officeID = B.officeID
WHERE B.arch_activity IS NULL AND B.civil_activity NOT NULL AND B.other_activity IS NULL, etc..
Mar 07, 2012 04:44 AM|os_os|LINK
thanks alot for your reply ... i tried this before it wont give the desire result..
it gives all the engineering offices list which have 'civil engineering' activity , and my requirement is to list only and only the offices that have just civil engineering activity .
actually the activities list is in LOOKUP table( lkp_activity)
and the office ------ > sends request for activities -------> the request is evaluated ------> then the approved activites saved in (approved activity table )
thanks in advance
Mar 07, 2012 08:05 PM|Lannie|LINK
/* We have three offices */
CREATE TABLE OFFICES
NAME_OFFICE VARCHAR2(16 BYTE),
CITY_OFFICE VARCHAR2(16 BYTE)
SET DEFINE OFF;
Insert into OFFICES Values (1, 'BLUE', 'BANGOR');
Insert into OFFICES Values (2, 'GREEN', 'DENVER');
Insert into OFFICES Values (3, 'RED', 'ALTUS');
/* Each office can have one or more Activities */
CREATE TABLE ACTIVITIES
SET DEFINE OFF;
Insert into ACTIVITIES Values (1, 3, 'CE');
Insert into ACTIVITIES Values (2, 3, 'ARCH');
Insert into ACTIVITIES Values (3, 3, 'DRAFT');
Insert into ACTIVITIES Values (4, 2, 'ARCH');
Insert into ACTIVITIES Values (5, 1, 'CE');
Insert into ACTIVITIES Values (6, 1, 'IT');
/* Only Denver office has one activity, architecture */
/* Find offices with only and only one activity */
/* Oracle Analytics naturally */
COUNT(a.OFFICE_SEQ) OVER (PARTITION BY a.OFFICE_SEQ) AS COUNT_OFFICE
FROM OFFICES a
INNER JOIN ACTIVITIES b ON a.OFFICE_SEQ = b.OFFICE_SEQ
WHERE c.COUNT_OFFICE = 1
Mar 10, 2012 06:57 AM|os_os|LINK
thanks alot ,your suggestion helped me so much ..