1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
# HELP;
USE /mnt/1Tdata/xbase/xbase64-4.1.1/data;
DROP TABLE IF EXISTS MAIN0.DBF;
DROP TABLE IF EXISTS LEFT0.DBF;
DROP TABLE IF EXISTS LEFT1.DBF;
DROP TABLE IF EXISTS LEFT2.DBF;
DROP TABLE IF EXISTS LEFT3.DBF;
DROP TABLE IF EXISTS INNER0.DBF;
CREATE TABLE MAIN0 ( ID NUMERIC(6,0), LEFTFK0 CHAR(3), LEFTFK1 CHAR(3), LEFTFK2 CHAR(3), LEFTFK3 CHAR(3), RIGHTFK0 CHAR(3), RIGHTFK1 CHAR(3), RIGHTFK2 CHAR(3), INNERFK0 CHAR(3) );
CREATE TABLE LEFT0.DBF ( ID NUMERIC(6,0), LEFTFK0 CHAR(3), CFLD CHAR(10) );
CREATE TABLE LEFT1.DBF ( ID NUMERIC(6,0), LEFTFK1 CHAR(3), CFLD CHAR(10) );
CREATE TABLE LEFT2.DBF ( ID NUMERIC(6,0), LEFTFK2 CHAR(3), CFLD CHAR(10) );
CREATE TABLE LEFT3.DBF ( ID NUMERIC(6,0), LEFTFK3 CHAR(3), CFLD CHAR(10) );
CREATE TABLE INNER0.DBF( ID NUMERIC(6,0), INNERFK0 CHAR(3), CFLD CHAR(10) );
INSERT INTO MAIN0.DBF ( ID, LEFTFK0, LEFTFK1, LEFTFK2, LEFTFK3, RIGHTFK0, RIGHTFK1, RIGHTFK2, INNERFK0 ) VALUES ( 1, 'L01', 'L11', 'L21', 'L31', 'R01', 'R11', 'R21', 'I01' );
INSERT INTO MAIN0.DBF ( ID, LEFTFK0, LEFTFK1, LEFTFK2, LEFTFK3, RIGHTFK0, RIGHTFK1, RIGHTFK2, INNERFK0 ) VALUES ( 2, 'L02', 'L12', 'L22', 'L32', 'R02', 'R12', 'R22', 'I02' );
INSERT INTO MAIN0.DBF ( ID, LEFTFK0, LEFTFK1, LEFTFK2, LEFTFK3, RIGHTFK0, RIGHTFK1, RIGHTFK2, INNERFK0 ) VALUES ( 3, 'L03', 'L13', 'L23', 'L33', 'R03', 'R13', 'R23', 'I03' );
INSERT INTO LEFT0.DBF ( ID, LEFTFK0, CFLD ) VALUES ( 1, 'L01', 'REC 1 data' );
INSERT INTO LEFT0.DBF ( ID, LEFTFK0, CFLD ) VALUES ( 2, 'L02', 'REC 2 data' );
INSERT INTO INNER0.DBF ( ID, INNERFK0, CFLD ) VALUES ( 1, 'I01', 'REC 1 data' );
INSERT INTO INNER0.DBF ( ID, INNERFK0, CFLD ) VALUES ( 2, 'I03', 'REC 2 data' );
#INSERT INTO TestSQL.DBF (CITY, STATE, ZIPCODE, NOTES, LASTUPDATE, ACTIVE ) VALUES ( 'San Diego', 'CA', 92007, 'San Diego is a cool place', '1989-02-09', 'Y');
#CREATE UNIQUE INDEX TestSql1.NDX ON TestSql.DBF( CHRFLD ) ASSOCIATE;
#DROP INDEX IF EXISTS TestSql1.NDX ON TestSql.DBF;
#SELECT M.ID, M.LEFTFK0, L0.CFLD, L1.CFLD AS L1cfld
#FROM MAIN0 M
#LEFT JOIN LEFT0 L0 ON M.LEFTFK0 = L0.LEFTFK0
#LEFT JOIN LEFT1 L1 ON M.LEFTFK1 = L1.LEFTFK1 AND L1.ID = L0.ID
|
