quote:
WebQuery - isn't that a custom version of webfocus - with a lot less functionality?
Ye i think so.
quote:
Could be you can indeed not use the car database in the standard (focus db) form.
Could be that WebQuery is DB2 only.
DB2 Web Query can work with SQL Server too.
Below an SQL script for DB2 Web Query user's :
--------------------------------------------------------------------
-- Special thanks to Francis Mariani
--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_ORIGIN (
COUNTRY CHAR(10) DEFAULT NULL ,
CONSTRAINT QGPL.PK_SQLCAR_ORIGIN PRIMARY KEY( COUNTRY ) ) ;
INSERT INTO qgpl.SQLCAR_ORIGIN (COUNTRY)
VALUES ('ENGLAND'),
('JAPAN'),
('ITALY'),
('W GERMANY'),
('FRANCE');
--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_COMP (
COUNTRY CHAR(10) DEFAULT NULL ,
CAR CHAR(16) DEFAULT NULL ,
CONSTRAINT QGPL.PK_SQLCAR_COMP PRIMARY KEY( COUNTRY , CAR ) ) ;
ALTER TABLE QGPL.SQLCAR_COMP
ADD CONSTRAINT QGPL.FK_SQLCAR_COMP_ORIGIN
FOREIGN KEY( COUNTRY )
REFERENCES QGPL.SQLCAR_ORIGIN ( COUNTRY )
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
INSERT INTO qgpl.SQLCAR_COMP (COUNTRY, CAR)
VALUES ('ENGLAND', 'JAGUAR'),
('ENGLAND', 'JENSEN'),
('ENGLAND', 'TRIUMPH'),
('JAPAN', 'DATSUN'),
('JAPAN', 'TOYOTA'),
('ITALY', 'MASERATI'),
('ITALY', 'ALFA ROMEO'),
('W GERMANY', 'AUDI'),
('W GERMANY', 'BMW'),
('FRANCE', 'PEUGEOT');
--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_CARREC (
COUNTRY CHAR(10) DEFAULT NULL ,
CAR CHAR(16) DEFAULT NULL ,
MODEL CHAR(24) DEFAULT NULL ,
CONSTRAINT QGPL.PK_SQLCAR_CARREC PRIMARY KEY( COUNTRY , CAR , MODEL ) ) ;
ALTER TABLE QGPL.SQLCAR_CARREC
ADD CONSTRAINT QGPL.FK_SQLCAR_CARREC_COMP
FOREIGN KEY( COUNTRY , CAR )
REFERENCES QGPL.SQLCAR_COMP ( COUNTRY , CAR )
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
INSERT INTO qgpl.SQLCAR_CARREC (COUNTRY, CAR, MODEL)
VALUES ('ENGLAND', 'JAGUAR', 'V12XKE AUTO'),
('ENGLAND', 'JAGUAR', 'XJ12L AUTO'),
('ENGLAND', 'JENSEN', 'INTERCEPTOR III'),
('ENGLAND', 'TRIUMPH', 'TR7'),
('ENGLAND', 'TRIUMPH', '2000 SPIDER VELOCE'),
('ENGLAND', 'TRIUMPH', '2000 GT VELOCE'),
('JAPAN', 'DATSUN', 'B210 2 DOOR AUTO'),
('JAPAN', 'TOYOTA', 'COROLLA 4 DOOR DIX AUTO'),
('ITALY', 'MASERATI', 'DORA 2 DOOR'),
('ITALY', 'ALFA ROMEO', '2000 4 DOOR BERLINA'),
('W GERMANY', 'AUDI', '100 LS 2 DOOR AUTO'),
('W GERMANY', 'BMW', '2002 2 DOOR'),
('W GERMANY', 'BMW', '2002 2 DOOR AUTO'),
('W GERMANY', 'BMW', '530I 4 DOOR'),
('W GERMANY', 'BMW', '530I 4 DOOR AUTO'),
('W GERMANY', 'BMW', '3.0 SI 4 DOOR'),
('W GERMANY', 'BMW', '3.0 SI 4 DOOR AUTO'),
('FRANCE', 'PEUGEOT', '504 4 DOOR');
--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_WARANT (
COUNTRY CHAR(10) DEFAULT NULL ,
CAR CHAR(16) DEFAULT NULL ,
WARRANTY CHAR(40) DEFAULT NULL ,
CONSTRAINT QGPL.PK_SQLCAR_WARRANT PRIMARY KEY( WARRANTY , CAR , COUNTRY ) ) ;
ALTER TABLE QGPL.SQLCAR_WARANT
ADD CONSTRAINT QGPL.FK_SQLCAR_WARRANT_COMP
FOREIGN KEY( COUNTRY , CAR )
REFERENCES QGPL.SQLCAR_COMP ( COUNTRY , CAR )
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
INSERT INTO qgpl.SQLCAR_WARANT (COUNTRY, CAR, WARRANTY)
VALUES ('ENGLAND', 'JAGUAR', '12 MONTHS OR 12000 MILES'),
('ENGLAND', 'JENSEN', '12000 MILES OR 12 MONTHS'),
('ENGLAND', 'TRIUMPH', '12 MONTHS OR 12000 MILES'),
('JAPAN', 'DATSUN', '12 MONTHS OR 12000 MILES'),
('JAPAN', 'TOYOTA', '12 MONTHS OR 12500 MILES'),
('ITALY', 'MASERATI', '6 MONTHS OR 6000 MILES'),
('W GERMANY', 'AUDI', '12 MONTHS OR 20000 MILES'),
('FRANCE', 'PEUGEOT', '12 MONTHS ON 12000 MILES');
--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_EQUIP (
COUNTRY CHAR(10) DEFAULT NULL ,
CAR CHAR(16) DEFAULT NULL ,
STANDARD CHAR(40) DEFAULT NULL ,
CONSTRAINT QGPL.PK_SQLCAR_EQUIP PRIMARY KEY( COUNTRY , CAR , STANDARD ) ) ;
ALTER TABLE QGPL.SQLCAR_EQUIP
ADD CONSTRAINT QGPL.FK_SQLCAR_EQUIP_COMP
FOREIGN KEY( COUNTRY , CAR )
REFERENCES QGPL.SQLCAR_COMP ( COUNTRY , CAR )
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
INSERT INTO qgpl.SQLCAR_EQUIP (COUNTRY, CAR, STANDARD)
VALUES ('ENGLAND', 'JAGUAR', 'POWER STEERING'),
('ENGLAND', 'JAGUAR', '4 WHEEL DISC BRAKES'),
('ENGLAND', 'JAGUAR', 'WHITEWALL RADIAL PLY TIRES'),
('ENGLAND', 'JAGUAR', 'WRAP AROUND BUMPERS'),
('ENGLAND', 'JAGUAR', 'RECLINING BUCKET SEATS'),
('ENGLAND', 'JENSEN', 'AIR CONDITIONING'),
('ENGLAND', 'JENSEN', 'LEAR JET AM/FM 8 TRK STEREO'),
('ENGLAND', 'JENSEN', 'PIRELLI GR70 15 TIRES'),
('ENGLAND', 'JENSEN', 'CHRYSLER 383 CU IN V8 ENGINE'),
('JAPAN', 'DATSUN', 'POWER FRONT DISC BRAKES'),
('JAPAN', 'DATSUN', 'REAR DRUM BRAKES'),
('JAPAN', 'TOYOTA', 'BODY SIDE MOLDING'),
('JAPAN', 'TOYOTA', 'MACPHERSON STRUT FROMT SUSPENSION'),
('ITALY', 'MASERATI', 'ALL STEEL BODY'),
('ITALY', 'MASERATI', '5 LITRE SS ENGINE'),
('ITALY', 'MASERATI', 'MICHELIN 21 5/70 VR15XVWX TIRES'),
('ITALY', 'MASERATI', 'CAMPAGNOLLO LIGHT ALLOY WHEELS'),
('W GERMANY', 'AUDI', 'POWER FRONT BRAKES'),
('W GERMANY', 'AUDI', '165 SR 14 RADIAL TIRES'),
('W GERMANY', 'AUDI', 'FRONT WHEEL DRIVE'),
('ENGLAND', 'TRIUMPH', 'POWER FRONT DISC BRAKES'),
('ENGLAND', 'TRIUMPH', 'RETRACTABLE HEADLIGHTS'),
('FRANCE', 'PEUGEOT', 'RACK AND PINION STEERING'),
('FRANCE', 'PEUGEOT', 'FOUR WHEEL SUSPENSION'),
('FRANCE', 'PEUGEOT', 'ANTI SWAY BARS FRONT AND REAR');
--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_BODY (
COUNTRY CHAR(10) DEFAULT NULL ,
CAR CHAR(16) DEFAULT NULL ,
MODEL CHAR(24) DEFAULT NULL ,
BODYTYPE CHAR(12) DEFAULT NULL ,
SEATS NUMERIC(4, 0) DEFAULT NULL ,
DCOST NUMERIC(8, 0) DEFAULT NULL ,
RCOST NUMERIC(8, 0) DEFAULT NULL ,
SALES NUMERIC(8, 0) DEFAULT NULL ,
CONSTRAINT QGPL.PK_SQLCAR_BODY PRIMARY KEY( COUNTRY , CAR , MODEL , BODYTYPE ) ) ;
ALTER TABLE QGPL.SQLCAR_BODY
ADD CONSTRAINT QGPL.FK_SQLCAR_BODY_CARREC
FOREIGN KEY( COUNTRY , CAR , MODEL )
REFERENCES QGPL.SQLCAR_CARREC ( COUNTRY , CAR , MODEL )
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
INSERT INTO qgpl.SQLCAR_BODY
(COUNTRY, CAR, MODEL, BODYTYPE, SEATS, DCOST, RCOST, SALES)
VALUES ('ENGLAND', 'JAGUAR', 'V12XKE AUTO', 'CONVERTIBLE', 2, 7427, 8878, 0),
('ENGLAND', 'JAGUAR', 'XJ12L AUTO', 'SEDAN', 5, 11194, 13491, 0),
('JAPAN', 'DATSUN', 'B210 2 DOOR AUTO', 'SEDAN', 4, 2626, 3139, 43000),
('JAPAN', 'TOYOTA', 'COROLLA 4 DOOR DIX AUTO', 'SEDAN', 4, 2886, 3339, 35030),
('W GERMANY', 'AUDI', '100 LS 2 DOOR AUTO', 'SEDAN', 5, 5063, 5970, 7800),
('ITALY', 'ALFA ROMEO', '2000 4 DOOR BERLINA', 'SEDAN', 4, 4915, 5925, 4800),
('W GERMANY', 'BMW', '2002 2 DOOR', 'SEDAN', 5, 5800, 5940, 8950),
('W GERMANY', 'BMW', '2002 2 DOOR AUTO', 'SEDAN', 4, 6000, 6355, 8900),
('W GERMANY', 'BMW', '530I 4 DOOR', 'SEDAN', 5, 8300, 9097, 14000),
('W GERMANY', 'BMW', '530I 4 DOOR AUTO', 'SEDAN', 5, 8400, 9495, 15600),
('W GERMANY', 'BMW', '3.0 SI 4 DOOR', 'SEDAN', 5, 10000, 13752, 14000),
('W GERMANY', 'BMW', '3.0 SI 4 DOOR AUTO', 'SEDAN', 5, 11000, 14123, 18940),
('FRANCE', 'PEUGEOT', '504 4 DOOR', 'SEDAN', 5, 4631, 5610,0),
('ENGLAND', 'JENSEN', 'INTERCEPTOR III', 'SEDAN', 4, 14940, 17850, DEFAULT),
('ITALY', 'MASERATI', 'DORA 2 DOOR', 'COUPE', 2, 25000, 31500, DEFAULT),
('ENGLAND', 'TRIUMPH', 'TR7', 'HARDTOP', 2, 4292, 5100, DEFAULT),
('ENGLAND', 'TRIUMPH', '2000 SPIDER VELOCE', 'ROADSTER', 2, 5660, 6820, DEFAULT),
('ENGLAND', 'TRIUMPH', '2000 GT VELOCE', 'COUPE', 2, 5660, 6820, DEFAULT);
--------------------------------------------------------------------
CREATE TABLE QGPL.SQLCAR_SPECS (
COUNTRY CHAR(10) DEFAULT NULL ,
CAR CHAR(16) DEFAULT NULL ,
MODEL CHAR(24) DEFAULT NULL ,
BODYTYPE CHAR(12) DEFAULT NULL ,
LENGTH NUMERIC(6, 0) DEFAULT NULL ,
WIDTH NUMERIC(6, 0) DEFAULT NULL ,
HEIGHT NUMERIC(6, 0) DEFAULT NULL ,
WEIGHT NUMERIC(6, 0) DEFAULT NULL ,
WHEELBASE NUMERIC(6, 1) DEFAULT NULL ,
FUEL_CAP NUMERIC(6, 1) DEFAULT NULL ,
BHP NUMERIC(6, 0) DEFAULT NULL ,
RPM NUMERIC(6, 0) DEFAULT NULL ,
MPG NUMERIC(6, 0) DEFAULT NULL ,
ACCEL NUMERIC(6, 0) DEFAULT NULL ,
CONSTRAINT QGPL.PK_SQLCAR_SPECS PRIMARY KEY( COUNTRY , CAR , MODEL , BODYTYPE ) ) ;
ALTER TABLE QGPL.SQLCAR_SPECS
ADD CONSTRAINT QGPL.FK_SQLCAR_SPECS_CARREC
FOREIGN KEY( COUNTRY , CAR , MODEL )
REFERENCES QGPL.SQLCAR_CARREC ( COUNTRY , CAR , MODEL )
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
INSERT INTO qgpl.SQLCAR_SPECS
(COUNTRY, CAR, MODEL, BODYTYPE,
LENGTH, WIDTH, HEIGHT, WEIGHT, WHEELBASE, FUEL_CAP, BHP, RPM, MPG, ACCEL)
VALUES ('ENGLAND', 'JAGUAR', 'V12XKE AUTO', 'CONVERTIBLE',
189.6, 66, 48, 3435, 105, 18, 241, 5750, 16, 6.8),
('ENGLAND', 'JAGUAR', 'XJ12L AUTO', 'SEDAN',
198.8, 69.7, 54.1, 4200, 112.8, 24, 241, 5750, 9, 8.5),
('ENGLAND', 'JENSEN', 'INTERCEPTOR III', 'SEDAN',
188, 69, 53, 4000, 105, 24, 385, 4700, 11, 7.5),
('JAPAN', 'DATSUN', 'B210 2 DOOR AUTO', 'SEDAN',
163, 60.8, 53.5, 2050, 92.1, 11.6, 70, 6000, DEFAULT,33),
('ITALY', 'MASERATI', 'DORA 2 DOOR', 'COUPE',
177, 69.6, 44.6, 3700, 102.3, 25, 315, 5000, DEFAULT,6),
('JAPAN', 'TOYOTA', 'COROLLA 4 DOOR DIX AUTO', 'SEDAN',
165.2, 61.8, 54.5, 2219, 93.3, 13.2, 75, 5800, 27, DEFAULT),
('W GERMANY', 'AUDI', '100 LS 2 DOOR AUTO', 'SEDAN',
187.2, 69, 54.6, 2571, 105.3, 15.3, 95, 5500, 23, 13),
('ENGLAND', 'TRIUMPH', 'TR7', 'HARDTOP',
164.5, 66.2, 49.9, 2241, 85, 14.5, 90, 5000,25, DEFAULT),
('ENGLAND', 'TRIUMPH', '2000 SPIDER VELOCE', 'ROADSTER',
169.9, 64.2, 50.8, 2320, 88.6, 13.4, DEFAULT, DEFAULT, 21, DEFAULT),
('ENGLAND', 'TRIUMPH', '2000 GT VELOCE', 'COUPE',
163.4, 62.2, 51.8, 2305, 92.5, 14, DEFAULT, DEFAULT, 21, DEFAULT),
('ITALY', 'ALFA ROMEO', '2000 4 DOOR BERLINA', 'SEDAN',
176.7, 61.6, 56.3, 2590, 101, 14, DEFAULT, DEFAULT, 21, DEFAULT),
('W GERMANY', 'BMW', '2002 2 DOOR', 'SEDAN',
176, 62.5, 55.5, 2400, 98.4, 13, DEFAULT, DEFAULT, 24.2, DEFAULT),
('W GERMANY', 'BMW', '2002 2 DOOR AUTO', 'SEDAN',
176, 62.6, 55.5, 2400, 98.4, 12, DEFAULT, DEFAULT, 24.2, DEFAULT),
('W GERMANY', 'BMW', '530I 4 DOOR', 'SEDAN',
189.9, 67.2, 55.9, 3250, 103.8, 18.5, DEFAULT, DEFAULT, 18, DEFAULT),
('W GERMANY', 'BMW', '530I 4 DOOR AUTO', 'SEDAN',
189.9, 67.2, 55.9, 3250, 103.8, 18.5, DEFAULT, DEFAULT, 18, DEFAULT),
('W GERMANY', 'BMW', '3.0 SI 4 DOOR', 'SEDAN',
195, 68.9, 57.1, DEFAULT, 106, 20.6, DEFAULT, DEFAULT, 18, DEFAULT),
('W GERMANY', 'BMW', '3.0 SI 4 DOOR AUTO', 'SEDAN',
195, 68.9, 57.1, DEFAULT, 106, 20.6, DEFAULT, DEFAULT, 18, DEFAULT),
('FRANCE', 'PEUGEOT', '504 4 DOOR', 'SEDAN',
182.4, 66.7, 57, 2860, 108, 14.8, DEFAULT, DEFAULT, 21, DEFAULT);
WebFocus 7.7 (DB2 Web Query 1.1.2). IBM i