4.3 SQL-kommandoer

I det følgende er alle de SQL kommandoer, som vi har brugt listet op. Først vil alle SELECT statements blive vist, og derefter alle CREATE statements for MAOV's egne tabeller med tilhørende INSERT statements hvor relevant. Gennemgangen af de forskellige SELECT statements foregår på den måde, at de er inddelt efter hvilket objekt de bruges i, samt at der oven over det enkelte SELECT statement vil være en lille beskrivelse af hvad det udfører. For de forskellige CREATE statements vil gennemgangen foregå på den måde, at de vil være inddelt efter hvilken funktion den oprettede tabel eller de oprettede tabeller understøtter, og udover dette vil der, hvor relevant, være vist INSERT statements.

Alle de SELECT statements vi har brugt er listet op i det følgende.

4.3.1 FoundCustomers

Henter de 10 seneste søgte kunder for en bruger.

SELECT
  searchedForName,
  searchedForID,
  searchedForCDMID,
  custAddr1,
  custTown,
  custPhone
FROM
  RecentCustSearch
WHERE
  userID = 'userID'
  AND rownum < 11
ORDER BY
  timeStamp DESC

Henter kunders ID'er og stamdata udfra en søgestreg indtastet af brugeren i søgeformen.

SELECT
  cprod.customer_name,
  cprod.customer_id,
  NVL(ccdm.id, 'null'),
  NVL(ccdm.addr1, 'null'),
  NVL(pcode.town, 'null'),
  NVL(ccdm.phone, 'null')
FROM
  oradk.ra_customers@prod.dk.oracle.com cprod,
  cdm.cdmcomp@cdm.dk.oracle.com ccdm,
  cdm.cdmpcode@cdm.dk.oracle.com pcode
WHERE
  cprod.attribute11 like '% searchString.toUpperCase() %'
  AND cprod.status = 'A'
  AND cprod.customer_number = ccdm.oldoracleid (+)
  AND ccdm.primarypostcode = pcode.id (+)
ORDER BY
  cprod.customer_name

4.3.2 FoundEmployees

Henter de 10 seneste søgte medarbejdere for en bruger.

SELECT
  searchedForName,
  searchedForID
FROM
  RecentEmpSearch
WHERE
  userID = 'userID'
  AND rownum < 11
ORDER BY
  timeStamp DESC

Henter medarbejderes ID og navn udfra en søgestreg indtastet af brugeren i søgeformen.

SELECT
  person_id,
  full_name
FROM
  oradk.hrv_people_info@prod.dk.oracle.com
WHERE
  keystring like '% searchString %'
ORDER BY
  full_name

4.3.3 EmployeeData

Henter stamdata om en valgt medarbejder.

SELECT
  first_name,
  middle_names,
  last_name,
  telephone_number_1,
  telephone_number_2,
  work_telephone,
  email_address,
  full_name
FROM
  oradk.hrv_people_info@prod.dk.oracle.com
WHERE
  person_id = empID

4.3.4 LicensesData

Henter oplysninger om alle de produkter en kunde har licens til.

SELECT
  product_descr,
  version,
  sum(nvl(quantity,0)) total_quantity,
  licens_type
FROM
  oradk.product_license@prod.dk.oracle.com
WHERE
  customer_id = custID
  AND licens_type IS NOT NULL
GROUP BY
  product_descr,
  version,
  licens_type

4.3.5 ActivitiesData

Henter alle de aktiviteter en kunde har været og er tilmeldt til.

SELECT DISTINCT
  nvl(acti.head,'null'),
  acti.startdate
FROM
  cdm.cdmcomp@cdm.dk.oracle.com comp,
  cdm.cdmacti@cdm.dk.oracle.com acti
WHERE
  acti.head is not null and
  comp.id = acti.primcomp and
  comp.id = 'companyID'
ORDER BY
  startdate desc

4.3.6 TARsData

Henter oplysninger om alle de TAR's der er aktive for en kunde.

SELECT
  a.tar_no,
  a.contact_first_name,
  a.contact_name,
  a.contact_phone,
  a.change_date,
  a.severity,
  a.component,
  a.subject,
  a.tar_status,
  a.bug_status,
  b.description,
  c.description
FROM
  tar_head@sms.dk.oracle.com a,
  tar_status@sms.dk.oracle.com b,
  tar_status@sms.dk.oracle.com c
WHERE
  b.status = a.tar_status AND
  b.status_type = 'T' AND
  c.status(+) = a.bug_status AND
  c.status_type(+) = 'B' AND
  (a.tar_status<18 OR NVL(a.bug_status, 39)<39) AND
  a.org_id = 'custID'
ORDER BY
  a.severity,
  a.change_date

4.3.7 ContactsData

Henter alle de kontakter der er hos en kunde.

SELECT DISTINCT
  cn.firstn,
  cn.lastn,
  cn.title,
  cn.direct,
  cn.mobile,
  cn.email
FROM
  cdm.cdmcont@cdm.dk.oracle.com cn
WHERE
  cn.delu is null and
  cn.id = 'contID'

Alle de CREATE statements vi har brugt er listet op i det følgende, med tilhørende INSERT statements, hvor relevant.

4.3.8 Senest søgte og valgte kunder

create table RecentCustSearch (
  userID varchar2(19),
  searchedForID number(19),
  searchedForCDMID varchar2(19),
  searchedForName varchar2(35),
  custAddr1 varchar2(35),
  custTown varchar2(30),
  custPhone varchar2(20),
  timestamp date
  );

4.3.9 Senest søgte og valgte medarbejdere

create table RecentEmpSearch (
  userID varchar2(19),
  searchedForID number(19),
  searchedForName varchar2(35),
  timestamp date
  );

4.3.10 Prislisten

create table pricelist_startuptext (
  startuptext varchar2(40)
  );

create table pricelist_details (
  id number not null,
  productname varchar2(50) not null,
  nuss number, /* Named User Single Server */
  nums number, /* Named User Multi Server */
  upu number,  /* Universal Power Unit */
  license number
  );

create table pricelist_groups (
  id number not null,
  groupname varchar(30) not null
  );

/* Startuptext */
insert into pricelist_startuptext values ('E-Business Global Price List 13.10.00');

/* Database */
insert into pricelist_groups values (1,'Oracle Database');
insert into pricelist_details values(1,'Oracle Database Standard Edition',1299,1624,122,null);
insert into pricelist_details values(1,'Oracle Database Enterprise Edition',4872,6090,812,null);
insert into pricelist_details values(1,'Trusted Oracle Enterprise Edition',5847,7308,974,null);
insert into pricelist_details values(1,'Oracle Database Personal Edition',3208,null,null,null);
insert into pricelist_details values(1,'Oracle Database Lite',2396,null,null,null);
insert into pricelist_details values(1,'Advanced Security',974,1218,122,null);
insert into pricelist_details values(1,'Parallel Server',1949,2436,244,null);
insert into pricelist_details values(1,'Partitioning',1949,2436,244,null);
insert into pricelist_details values(1,'Spatial',2923,3654,325,null);
insert into pricelist_details values(1,'Diagnostic Management Pack',325,406,81,null);
insert into pricelist_details values(1,'Tuning Management Pack',325,406,81,null);
insert into pricelist_details values(1,'Change Management Pack',325,406,81,null);
insert into pricelist_details values(1,'Management Pack for SAP R/3',325,406,81,null);

/* Application Server */
insert into pricelist_groups values (2,'Application Server');
insert into pricelist_details values(2,'Internet Application Server Standard Edition',284,365,41,null);
insert into pricelist_details values(2,'Internet Application Server Enterprise Edition',1624,2030,244,null);
insert into pricelist_details values(2,'Internet Application Server Wireless Edition',771,974,1218,null);

/* Tools */
insert into pricelist_groups values (3,'Tools');
insert into pricelist_details values(3,'Internet Developer Suite',32441,40562,null,null);
insert into pricelist_details values(3,'Discoverer Plus',6456,8080,null,null);
insert into pricelist_details values(3,'SQL*Plus',4020,5035,null,null);
insert into pricelist_details values(3,'Programmer',8080,10110,null,null);

/* Integration Products */
insert into pricelist_groups values (4,'Integration Products');
insert into pricelist_details values(4,'Open System Gateways',null,null,null,121807);
insert into pricelist_details values(4,'Mainframe Integration Gateways',null,null,null,771443);
insert into pricelist_details values(4,'Enterprise Integration Gateways',null,null,null,771443);
insert into pricelist_details values(4,'EDA/SQL Gateways',974454,null,null,null);
insert into pricelist_details values(4,'Each Additional EDA/SQL Driver',null,null,null,487227);
insert into pricelist_details values(4,'Applications InterConnect Toolkit',812,1015,122,null);

/* Other Server Products */
insert into pricelist_groups values (5,'Other Server Products');
insert into pricelist_details values(5,'Video Server',325,406,81,null);
insert into pricelist_details values(5,'Email Server',122,154,81,null);
insert into pricelist_details values(5,'Internet Directory',null,null,406,41);
insert into pricelist_details values(5,'Message Broker',771,974,81,null);

/* Data Warehousing Products */
insert into pricelist_groups values (6,'Data Warehousing Products');
insert into pricelist_details values(6,'Warehouse Builder',null,null,203,null);
insert into pricelist_details values(6,'Pure Name & Address (US)',null,null,244,null);
insert into pricelist_details values(6,'Pure Name & Address (Canada)',null,null,65,null);
insert into pricelist_details values(6,'Geocode',null,null,41,null);
insert into pricelist_details values(6,'Oracle Data Mining Suite',null,null,406,null);
insert into pricelist_details values(6,'Express Server',12952,16200,1624,null);
insert into pricelist_details values(6,'Express Analyzer',4832,6050,null,null);
insert into pricelist_details values(6,'Express Objects',32441,40562,null,null);

4.3.11 Action points

create table ActionPoints (
  subject varchar2(30),
  body varchar2(250)
  );

insert into table ActionPoints values('Ring','Ring hurtigst muligt til mig.');