SQL - NULL, GROUP BY, OUTER problem
Hi all,
I cannot select rows if I use more complex statement
with clause GROUP BY. I have following tables:
create table t1
(c1 integer,
c2 decimal(12,2),
c3 char(20),
c4 char(20)
);
create table t2
(c1 integer,
c2 integer
);
create table t3
(c1 integer,
c2 integer
);
create table t4
(c1 integer,
c2 integer
);
There are inserted these rows into tables. Columns c2,
c3, c4 in table t1 are NULL.
insert into t1 (c1) values (1);
insert into t2 values (1,2);
insert into t3 values (2,3);
insert into t4 values (3,100);
insert into t4 values (3,200);
Finaly I run this select:
select t1.c1, t1.c2, t1.c3, t1.c4, sum(t4.c2)
from t1, t2, t3, OUTER t4
where t1.c1 = t2.c1
and t2.c2 = t3.c1
and t3.c2 = t4.c1
group by 1,2,3,4;
I mean, this select should return one row. It returns no
rows. If I remove OUTER clause, don't select columns c2, c3,
c4 or remove joined table t2 or t3, everything runs O.K.
Of course, there are a lots of workarounds, I just
wonder, what's happening. What am I doing wrong or is it
a bug?
I use INFORMIX OnLine 4.10. In Guide to SQL - Reference
are mentioned NULLs in GROUP BY clause, but it gives me no
sense.
Thanks for your response.
--
Zdeno Miklas SOFTEC spol. s r. o.
e-mail: ash@softec.sk Kutuzovova 23
phone : 42-7-273805 ext.128 831 03 Bratislava
fax : 42-7-272537 SLOVAKIA