Re: What is wrong with this SQL?

On 6 Feb 1999, Paschal Mushubi wrote: > 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 Probably the data type of the string constants. What type is the column Order_Date? Is the Dept_Number really the correct self-join key? Show us a sample schema and some sample data? Obviously, you have a record with 22-SEP-98 as the order date. Apparently you also have a record with 15-OCT-98 as the order date since you expect to see it. Does it have the same Dept_Number as the other? What value of DBDATE are you using? You should be able to send us some example code like... CREATE TEMP TABLE Sales ( Order_date ????? NOT NULL, Dept_Number ????? NOT NULL[, Order_number ????? NOT NULL...] ); INSERT INTO Sales VALUES('22-SEP-98', ????[, ????...]); INSERT INTO Sales VALUES('15-OCT-98', ????[, ????...]); You need to replace the question marks with the correct information, obviously. Informix has DATE and DATETIME YEAR TO DAY types. Your 22-SEP-98 format might or might not be accepted with a DATE field; it would be rejected by DATETIME fields (not ISO 8601 format). > 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? Yours, Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn