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 <ViewName>
	     (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(<DateField>,
	'MM/YYYY')),'DD/MM/YYYY') SelectionDate
	       FROM
	           <Table>
	       WHERE
	           TO_NUMBER(TO_CHAR(LAST_DAY(<DateField>),'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_at_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 <ViewName>
> (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(<DateField>,
> 'MM/YYYY')),'DD/MM/YYYY') SelectionDate
> FROM
> <Table>
> WHERE
> TO_NUMBER(TO_CHAR(LAST_DAY(<DateField>),'DD')) >= 31) DS;
>
> <Table> is a table that contains a date field <DateField>.
>
> When i execute the query "select * from <ViewName>", i have the correct
> result.
> When i execute the query "select * from <ViewName> 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_at_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_at_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
Received on Tue Sep 05 2000 - 12:35:45 CDT