What is wrong with this SQL?
What is wrong with this SQL query? select T1.Order_Date SEP, T2.Order_Date OCT from Sales T1, Sales T2 where T1.Order_Date between '01-SEP-98' and '30-SEP-98' and T2.Order_Date between '01-OCT-98' and '30-OCT-98' and T1.Dept_Number = T2.Dept_Number It returns: SEP OCT --- --- 09-22-98 09-22-98 I want to see SEP OCT ---- ---- 22-SEP-98 15-OCT-98 What am I missing? paschal.
----- Original Message -----
From: Paschal Mushubi <bs260@FreeNet.Carleton.CA>
Newsgroups: comp.databases.informix
Sent: Saturday, February 06, 1999 01:32
Subject: What is wrong with this SQL?
>
>What is wrong with this SQL query?
>select T1.Order_Date SEP,
> T2.Order_Date OCT
>from Sales T1,
> Sales T2
>where T1.Order_Date between '01-SEP-98' and '30-SEP-98'
>and T2.Order_Date between '01-OCT-98' and '30-OCT-98'
>and T1.Dept_Number = T2.Dept_Number
>
>It returns:
>
>SEP OCT
>--- ---
>09-22-98 09-22-98
>
>I want to see
>
>SEP OCT
>---- ----
>22-SEP-98 15-OCT-98
>
>What am I missing?
>
>paschal.
First:
OrderDate seems to be a char field, so
'01-OCT-98' < '02-SEP-98 < '31-OCT-98'. ('01' < '02' < '31')
Your where clause should be:
where T1.Order_Date like '%-SEP-98'
and T2.Order_Date like '%-OCT-98'
and T1.Dept_Number = T2.Dept_Number
but the OrderDate column shoud either be a DATE field
or should be stored as 'YYYY-MM-DD' ('98' is not Y2K compliant) so that
ordering is right.
Second:
This query returns a cartesian join:
T1:
01-SEP-98
02-SEP-98
03-SEP-98
...
T2:
01-OCT-98
02-OCT-98
03-OCT-98
...
results:
SEP OCT
---- ----
01-SEP-98 01-OCT-98
01-SEP-98 02-OCT-98
01-SEP-98 03-OCT-98
01-SEP-98 ...
02-SEP-98 01-OCT-98
02-SEP-98 02-OCT-98
02-SEP-98 03-OCT-98
02-SEP-98 ...
...
I dont think there is an easy way of doing this query.
Maybe something like:
create temp table temp1 (OrderDate char(10), number serial);
create temp table temp2 (OrderDate char(10), number serial);
insert into temp1
select OrderDate from T1
where ...
;
insert into temp2
select OrderDate from T2
where ...
;
select a.number, a.OrderDate, b.OrderDate
from temp1, outer temp 2
where a.number = b.number
union
select b.number, ' ', b.OrderDate
from temp 2
where number > (select max(number) from temp1)
Carlos Costa e Silva
----
Carlos Costa e Silva <ccs@minimal.pt>
Minimal Lda
Lisboa
Portugal