Re: For the SQL gurus: Is this practical? Is it even possible?

Success!!

With T1 as (
SELECT FOO.NINEDIGIT AS NINEDIGIT,
max(abs(IFNULL(BAR.FWEEP,0))) AS FWEEP,
max(IFNULL(BAZ.BOZ,'')) AS BOZ,
max(abs(IFNULL(GAZONK.GRIFFY,0))) AS GRIFFY
FROM (ROBJHHL/FOO LEFT OUTER JOIN ROBJHHL/QUUX ON
FOO.NINEDIGIT = QUUX.NINEDIGIT)
LEFT OUTER JOIN ROBJHHL/BAR ON
QUUX.NINETEENDIGIT = BAR.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/BAZ ON
QUUX.NINETEENDIGIT = BAZ.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/GAZONK ON
QUUX.NINETEENDIGIT = GAZONK.NINETEENDIGIT
group by foo.ninedigit
)
select * from t1 where fweep=2 and boz='SPAM' and griffy=
3.141592653589793

returns

....+....1....+....2....+....3....+.. | ..+...14....+...15....+..
NINEDIGIT FWEEP BOZ | GRIFFY
1 2 SPAM | 3.1415926535897931E+000
******** End of data ********

Technically, you could make the queries much easier by
create view robjhhl/fooview as (
SELECT FOO.NINEDIGIT AS NINEDIGIT,
max(abs(IFNULL(BAR.FWEEP,0))) AS FWEEP,
max(IFNULL(BAZ.BOZ,'')) AS BOZ,
max(abs(IFNULL(GAZONK.GRIFFY,0))) AS GRIFFY
FROM (ROBJHHL/FOO LEFT OUTER JOIN ROBJHHL/QUUX ON
FOO.NINEDIGIT = QUUX.NINEDIGIT)
LEFT OUTER JOIN ROBJHHL/BAR ON
QUUX.NINETEENDIGIT = BAR.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/BAZ ON
QUUX.NINETEENDIGIT = BAZ.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/GAZONK ON
QUUX.NINETEENDIGIT = GAZONK.NINETEENDIGIT
group by foo.ninedigit
)

Then you could simply do:
select * from robjhhl/fooview
where fweep=2 and boz='SPAM' and griffy=3.141592653589793
To see the sample data this is culled from:
SELECT FOO.NINEDIGIT AS NINEDIGIT,
QUUX.FOREIGNTABLE AS FOREIGNTABLE,
QUUX.NINETEENDIGIT AS NINETEENDIGIT,
IFNULL(BAR.FWEEP,0) AS FWEEP,
IFNULL(BAZ.BOZ,'') AS BOZ,
IFNULL(GAZONK.GRIFFY,0) AS GRIFFY
FROM (ROBJHHL/FOO LEFT OUTER JOIN ROBJHHL/QUUX ON
FOO.NINEDIGIT = QUUX.NINEDIGIT)
LEFT OUTER JOIN ROBJHHL/BAR ON
QUUX.NINETEENDIGIT = BAR.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/BAZ ON
QUUX.NINETEENDIGIT = BAZ.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/GAZONK ON
QUUX.NINETEENDIGIT = GAZONK.NINETEENDIGIT

returns:

....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10...
NINEDIGIT FOREIGNTABLE NINETEENDIGIT FWEEP BOZ
GRIFFY
1 BAR 71 2
0.0000000000000000E+000
1 BAZ 81 0
SPAM 0.0000000000000000E+000
1 GAZONK 91 0
3.1415926535897931E+000
2 BAR 72 4
0.0000000000000000E+000
2 BAZ 82 0
BUCK 0.0000000000000000E+000
2 GAZONK 92 0
2.7000000000000000E+001
3 - - 0
0.0000000000000000E+000
******** End of data ********

You can regenerate test data and all by the following script:


create collection robjhhl;

create table robjhhl/foo (
ninedigit dec(9,0));

create table robjhhl/bar (
nineteendigit dec(19,0),
fweep dec(3,0));

create table robjhhl/baz (
nineteendigit dec(19,0),
boz char(4));

create table robjhhl/gazonk (
nineteendigit dec(19,0),
griffy float);

create table robjhhl/quux (
ninedigit dec(9,0),
foreigntable char(10),
nineteendigit dec(19,0));

INSERT INTO ROBJHHL/FOO VALUES(1);

INSERT INTO ROBJHHL/FOO VALUES(2);

INSERT INTO ROBJHHL/FOO VALUES(3);

INSERT INTO ROBJHHL/BAR VALUES(71, 2);

INSERT INTO ROBJHHL/BAR VALUES(72, 4);

INSERT INTO ROBJHHL/BAZ VALUES(81, 'SPAM');

INSERT INTO ROBJHHL/BAZ VALUES(82, 'BUCK');

INSERT INTO ROBJHHL/GAZONK VALUES(91, 3.141592653589793);

INSERT INTO ROBJHHL/GAZONK VALUES(92, 27);

INSERT INTO ROBJHHL/QUUX VALUES(1, 'BAR', 71);

INSERT INTO ROBJHHL/QUUX VALUES(2, 'BAR', 72);

INSERT INTO ROBJHHL/QUUX VALUES(1, 'BAZ', 81);

INSERT INTO ROBJHHL/QUUX VALUES(2, 'BAZ', 82);

INSERT INTO ROBJHHL/QUUX VALUES(1, 'GAZONK', 91);

INSERT INTO ROBJHHL/QUUX VALUES(2, 'GAZONK', 92);

SELECT FOO.NINEDIGIT AS NINEDIGIT,
QUUX.FOREIGNTABLE AS FOREIGNTABLE,
QUUX.NINETEENDIGIT AS NINETEENDIGIT,
IFNULL(BAR.FWEEP,0) AS FWEEP,
IFNULL(BAZ.BOZ,'') AS BOZ,
IFNULL(GAZONK.GRIFFY,0) AS GRIFFY
FROM (ROBJHHL/FOO LEFT OUTER JOIN ROBJHHL/QUUX ON
FOO.NINEDIGIT = QUUX.NINEDIGIT)
LEFT OUTER JOIN ROBJHHL/BAR ON
QUUX.NINETEENDIGIT = BAR.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/BAZ ON
QUUX.NINETEENDIGIT = BAZ.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/GAZONK ON
QUUX.NINETEENDIGIT = GAZONK.NINETEENDIGIT;

With T1 as (
SELECT FOO.NINEDIGIT AS NINEDIGIT,
max(abs(IFNULL(BAR.FWEEP,0))) AS FWEEP,
max(IFNULL(BAZ.BOZ,'')) AS BOZ,
max(abs(IFNULL(GAZONK.GRIFFY,0))) AS GRIFFY
FROM (ROBJHHL/FOO LEFT OUTER JOIN ROBJHHL/QUUX ON
FOO.NINEDIGIT = QUUX.NINEDIGIT)
LEFT OUTER JOIN ROBJHHL/BAR ON
QUUX.NINETEENDIGIT = BAR.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/BAZ ON
QUUX.NINETEENDIGIT = BAZ.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/GAZONK ON
QUUX.NINETEENDIGIT = GAZONK.NINETEENDIGIT
group by foo.ninedigit
)
select * from t1 where fweep=2 and boz='SPAM' and griffy=
3.141592653589793;

create view robjhhl/fooview as (
SELECT FOO.NINEDIGIT AS NINEDIGIT,
max(abs(IFNULL(BAR.FWEEP,0))) AS FWEEP,
max(IFNULL(BAZ.BOZ,'')) AS BOZ,
max(abs(IFNULL(GAZONK.GRIFFY,0))) AS GRIFFY
FROM (ROBJHHL/FOO LEFT OUTER JOIN ROBJHHL/QUUX ON
FOO.NINEDIGIT = QUUX.NINEDIGIT)
LEFT OUTER JOIN ROBJHHL/BAR ON
QUUX.NINETEENDIGIT = BAR.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/BAZ ON
QUUX.NINETEENDIGIT = BAZ.NINETEENDIGIT
LEFT OUTER JOIN ROBJHHL/GAZONK ON
QUUX.NINETEENDIGIT = GAZONK.NINETEENDIGIT
group by foo.ninedigit
);

select * from robjhhl/fooview
where fweep=2 and boz='SPAM' and griffy=3.141592653589793;

Rob Berendt