Home » SQL & PL/SQL » SQL & PL/SQL » script help needed
script help needed [message #240095] Thu, 24 May 2007 02:21 Go to next message
patber
Messages: 9
Registered: May 2007
Junior Member
Hi,
I hav e a db that looks like this

a - account info
b - link between a and c
c - site info
d(a) - service info
d(b) - access info
d(c) - connection info
e - link between c and d(a)
f - link between d(a) and d(b)

Now to my quite easy question the relatoin between c and d(a) is one to many, in d(a) there is a staus column now I would like to extract only the sites that have _all_ the services at the status deinstalled

any ideas

Cheers,


Re: script help needed [message #240107 is a reply to message #240095] Thu, 24 May 2007 02:45 Go to previous messageGo to next message
jungle
Messages: 6
Registered: May 2007
Junior Member

If the statuses are just two, 'installed' and 'deinstalled'
and we have 1 id_site for N id_services in the table d(a)
try this:

select *
from c A
where exists( select id_site
from d(a) B
where not exists (select id_site from d(a) C
where status = 'istalled'
and B.id_site=C.id_site)
anv A.id_site=B.id_site)
Re: script help needed [message #240120 is a reply to message #240107] Thu, 24 May 2007 03:12 Go to previous messageGo to next message
patber
Messages: 9
Registered: May 2007
Junior Member
yes manged to get int to work, thx a lot

[Updated on: Thu, 24 May 2007 04:01]

Report message to a moderator

Re: script help needed [message #240139 is a reply to message #240120] Thu, 24 May 2007 04:17 Go to previous messageGo to next message
jungle
Messages: 6
Registered: May 2007
Junior Member

Great.
Anyway....

If table_site_part looks like this

serial_no |objid | part_status
----------------------------------
1 | 1 | installed
2 | 1 | deinstalled
3 | 1 | deinstalled
4 | 2 | installed
5 | 2 | deinstalled


.....TRY.....

SELECT ac.org_id org_no,
ac.name org_name,
ts.site_id site_id,
ts.name site_name,
tsp.serial_no service,
tsp.part_status service_status
FROM sa.table_bus_org_for_ke ac,
table_bus_site_role tbsr,
table_site ts,
(select objid, serial_no, part_status
from table_site_part A
where exists (select objid B
from table_site_part
where part_status ='installed'
and A.serial_no=B.serial_no)) tsp,--SERVICE
table_site_part acc,--ACCESS
table_site_part pc,--PROVIDER CONNECTION
table_prt_prt_role tppr,
table_sit_prt_role tspr
WHERE tbsr.bus_site_role2bus_org = ac.objid
AND tbsr.bus_site_role2site = ts.objid
AND tppr.role_for2site_part = tsp.objid
AND tppr.player2site_part = acc.objid
AND acc.objid = pc.site_part2site_part
AND pc.x_provider_access_type IS NOT NULL
AND tbsr.bus_site_role2bus_org = ac.objid
AND tbsr.bus_site_role2site = ts.objid
AND tspr.prt_role2site = ts.objid
AND tbsr.role_name = 'Equipment'
AND tspr.prt_role2site_part = tsp.objid
Re: script help needed [message #240178 is a reply to message #240139] Thu, 24 May 2007 05:49 Go to previous messageGo to next message
patber
Messages: 9
Registered: May 2007
Junior Member
I did it lilke your first suggestion

namely

SELECT ac.org_id org_no,
ac.name org_name,
ts.site_id site_no,
ts.name site_name,
tsp.serial_no service_no,
tsp.part_status service_status,
pc.serial_no pc_id,
pc.x_recurring_cost_end_date pc_end_date
FROM sa.table_bus_org_for_ke ac,
table_bus_site_role tbsr,
table_site ts,
table_site_part tsp,--SERVICE
table_site_part acc,--ACCESS
table_site_part pc,--PROVIDER CONNECTION
table_prt_prt_role tppr,
table_sit_prt_role tspr
WHERE tbsr.bus_site_role2bus_org = ac.objid
AND tbsr.bus_site_role2site = ts.objid
AND tppr.role_for2site_part = tsp.objid
AND tppr.player2site_part = acc.objid
AND acc.objid = pc.site_part2site_part
AND pc.x_provider_access_type IS NOT NULL
AND tbsr.bus_site_role2bus_org = ac.objid
AND tbsr.bus_site_role2site = ts.objid
AND tspr.prt_role2site = ts.objid
AND tbsr.role_name = 'Equipment'
AND tspr.prt_role2site_part = tsp.objid
AND (pc.x_recurring_cost_end_date != to_date('1753-01-01', 'YYYY-MM-DD') OR pc.x_recurring_cost_end_date IS NOT NULL)
AND EXISTS (
SELECT b.objid
FROM table_site_part b
WHERE NOT EXISTS (
SELECT c.objid
FROM table_site_part c
WHERE c.part_status IN ('-','Access Confirmed','Cancelled','Complained','Delayed','Downgraded','Installed','Installed & Invoiced','Installed Move Info','Installed To be Invoiced','Monthly','On call','Please Specify','Reconfigured','Reserved','To Be Installed','To be Cancelled','To be Downgraded','To be Moved','To be Reconfigured','To be Upgraded','To be cancelled','To be changed','To be deactivated','To be downgraded','To be installed','To be migrated','To be moved','To be re-installed','To be re-installed ','To be upgraded','Update Info','Upgraded','')
AND b.objid = c.objid)
AND tsp.objid = b.objid)

Now I will try to figure out how to get a list with the accounts that only have sites that all services are deinstalled.

Cheers,
Re: script help needed [message #240242 is a reply to message #240178] Thu, 24 May 2007 07:50 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
patber wrote on Thu, 24 May 2007 06:49

AND (pc.x_recurring_cost_end_date != to_date('1753-01-01', 'YYYY-MM-DD') OR pc.x_recurring_cost_end_date IS NOT NULL)



Lines like this make me tend to think you are using some sort of "dummy" values for when there is no DATE. This is not good programming and it will come back to bite you. I could be wrong, and maybe the date that Britain decided that Jan 1 would be the new New Year's Day is tracked in your table, but based on the table name, I didn't think so Wink
Re: script help needed [message #240248 is a reply to message #240242] Thu, 24 May 2007 08:02 Go to previous messageGo to next message
patber
Messages: 9
Registered: May 2007
Junior Member
I get what you say and will remeber that, but to my defence I do only selects so someone else have do the coding for updating the system Wink
Re: script help needed [message #240257 is a reply to message #240248] Thu, 24 May 2007 08:28 Go to previous messageGo to next message
patber
Messages: 9
Registered: May 2007
Junior Member
hmm, my query do not actually work that well, I will get sites back that have services that are both installed and deinstalled, and the aim of the game was to get only the sites that have _all_ services deinstalled...

any ideas?
Re: script help needed [message #240514 is a reply to message #240257] Fri, 25 May 2007 02:15 Go to previous messageGo to next message
patber
Messages: 9
Registered: May 2007
Junior Member
I need to loop thrugh the result I get from my query that looks like this


Site_id;service_id_service_status
1;1;installed
1;2;deinstalled
2;3;deinstalled
2;4;deinstalled
3;5;deinstalled
3;6;deinstalled
4;7;deinstalled
4;8;installed

from that I would like to to return the site_id's 2 & 3 and filer out 1 & 4 because they have services with status of installed.

Any pointers on getting me in the right direction?
Re: script help needed [message #240553 is a reply to message #240514] Fri, 25 May 2007 03:38 Go to previous messageGo to next message
jungle
Messages: 6
Registered: May 2007
Junior Member

BYE BYE

SQL> create table orafaq(Site_id number, service_id number, service_status varchar2(100));

Tabella creata.

SQL> insert into orafaq values(1,1,'installed');

Creata 1 riga.

SQL> insert into orafaq values(1,2,'deinstalled');

Creata 1 riga.

SQL> insert into orafaq values(2,3,'deinstalled');

Creata 1 riga.

SQL> insert into orafaq values(2,4,'deinstalled');

Creata 1 riga.

SQL> insert into orafaq values(3,5,'deinstalled');

Creata 1 riga.

SQL> insert into orafaq values(3,6,'deinstalled');

Creata 1 riga.

SQL> insert into orafaq values(4,7,'deinstalled');

Creata 1 riga.

SQL> insert into orafaq values(4,8,'installed');

Creata 1 riga.

SQL> select *
2 from orafaq A
3 where not exists (select distinct site_id
4 from orafaq B
5 where service_status = 'installed'
6* and A.site_id=B.site_id)
SQL> /

SITE_ID SERVICE_ID SERVICE_STATUS
---------- ---------- ---------------------------------------------------------------------------------
3 6 deinstalled
3 5 deinstalled
2 4 deinstalled
2 3 deinstalled

SQL>
Re: script help needed [message #240557 is a reply to message #240553] Fri, 25 May 2007 03:46 Go to previous message
patber
Messages: 9
Registered: May 2007
Junior Member
...

[Updated on: Fri, 25 May 2007 04:13]

Report message to a moderator

Previous Topic: i'm lazy - here's my pathetic title
Next Topic: hlp with analytical function to calculate missing data
Goto Forum:
  


Current Time: Sun Dec 04 02:54:14 CST 2016

Total time taken to generate the page: 0.16192 seconds