Message-Id: <10610.116229@fatcity.com> From: "Armstead, Michael A" Date: Tue, 5 Sep 2000 13:35:45 -0400 Subject: RE: Question about SQL query's behavior Paul, It looks like the CREATE statement created your columns with datatype DATE. You may want to do a DESCRIBE of the view to check the column datatype. If you want to make sure the datatypes of the columns is NUMBER, I would try to CREATE OR REPLACE VIEW (ProdMonth NUMBER, ProdYear NUMBER) AS SELECT TO_NUMBER(TO_CHAR(DS.SelectionDate,'MM')), TO_NUMBER(TO_CHAR(DS.SelectionDate,'YYYY')) FROM (SELECT DISTINCT TO_DATE(CONCAT('31/', TO_CHAR(, 'MM/YYYY')),'DD/MM/YYYY') SelectionDate FROM WHERE TO_NUMBER(TO_CHAR(LAST_DAY(),'DD')) >= 31) DS; This is just a guess. I hope it works. Michael Armstead Database Administrator, OCP-Certified Corporate & Finance Information Systems Glaxo Wellcome > -----Original Message----- > From: Paul van Dijken [SMTP:paul.vandijken@sema.nl] > Sent: Tuesday, September 05, 2000 10:45 AM > To: Multiple recipients of list ORACLE-L > Subject: Question about SQL query's behaviour > > > Hello, > > Does someone have an idea about the behaviour described below ? > > I create a view in Oracle V7 as follows : > > CREATE OR REPLACE VIEW > (ProdMonth, > ProdYear) > AS SELECT > TO_NUMBER(TO_CHAR(DS.SelectionDate,'MM')), > TO_NUMBER(TO_CHAR(DS.SelectionDate,'YYYY')) > FROM > (SELECT DISTINCT > TO_DATE(CONCAT('31/', TO_CHAR(, > 'MM/YYYY')),'DD/MM/YYYY') SelectionDate > FROM >
> WHERE > TO_NUMBER(TO_CHAR(LAST_DAY(),'DD')) >= 31) DS; > >
is a table that contains a date field . > > When i execute the query "select * from ", i have the correct > result. > When i execute the query "select * from where ProdMonth = 8" > (or, another value), I have the following message : > > ERROR: > ORA-01839: date not valid for month specified > > Thanks for your answer. > > -- > Author: Paul van Dijken > INET: paul.vandijken@sema.nl > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may