Oracle select bug

[beirti]

I have built a stock control system using HTML, PHP, & Oracle.
Whenever stock is inc/decreased, an entry is made in the transaction table. I can search through these transactions using each field but whenever I search between a certain date and the start of the month, e.g.('1-JAN-03' to '1-FEB-03'), it returns no results. What is even stranger is that it only happens for certain months: Feb, APR, MAY, ...

Here's the select statement that my program generates:

SELECT * FROM stockTransactions WHERE stType IN (SELECT stType FROM stockTransactions) AND stSerialNumber IN (SELECT stSerialNumber FROM stockTransactions) AND stDate BETWEEN '1-MAY-2003' AND '1-JAN-2004' ORDER BY stNo

Here are the tables:

CREATE TABLE stockDetails(
sdSerialNumber Varchar2(255),
sdType Varchar2(20),
sdDescription Varchar2(255),
sdWarningLevel Number(12),
sdQuantity Number(12),
CONSTRAINT stockDetails_pk_sdSerialNumber PRIMARY KEY(sdSerialNumber)
);

CREATE TABLE stockTransactions(
stNo Number(12),
stDate Date,
stType Varchar2(20),
stSerialNumber Varchar2(255),
stQuantity Number(12),
CONSTRAINT transactions_pk_stNo PRIMARY KEY(stNo),
CONSTRAINT transactions_fk_stSerialNumber FOREIGN KEY(stSerialNumber) REFERENCES stockDetails(sdSerialNumber)
);

The problem seems to pop up randomly but has not affected me greatly as of yet.
Any explanations?

 

[sem]

I'd thoroughly recommend you to use explicit conversion wherever it's possible.

In your case I suspect that contrary to your expectations stDate is converted to char and then compared to string literals '1-MAY-2003' AND '1-JAN-2004'. As you may see '1-MAY-2003' > '1-JAN-2004', thus the condition is evaluated as FALSE.

I'd also suggest to specify format mask to be independent from default NLS settings.

Regards, Dima

 

[beirti]

Cheers - will try that