4 Replies Latest reply on Jan 21, 2008 7:03 PM by 618517

DATE PROBLEM ORA-01858 - a non-numeric character was found where a numeric

618517 Jan 21, 2008 6:23 PM

hi

i am trying to create a view to query holiday dates within my database. It is suppossed to show all the dates within the following month, the code is as follows :

CREATE VIEW HolsInNextMonth AS
SELECT Site_Details.site_code,
Site_Details.name,
Holiday_Details.hol_code,
Holiday_Details.description,
Departure_Dates.Departure_Date,
TRUNC(ADD_MONTHS(SYSDATE, 1), 'MONTH') AS next_month_start,
LAST_DAY(ADD_MONTHS(SYSDATE, 1)) AS next_month_end
FROM Site_Details, Holiday_Details, Departure_Dates
WHERE Departure_Dates.Departure_Date BETWEEN to_date('next_month_start', 'dd-mon-yyyy')
AND to_date('next_month_end', 'dd-mon-yyyy')

Ive set up 2 fields within the view to create the start and end dates for the month after the system date. I then want to use this with the between statement to show all the holidays within these dates but i get the ORA-01858: a non-numeric character was found where a numeric was expected error.

Can someone tell me where i am going wrong and please help!

Thanks in advance.

1. Re: DATE PROBLEM ORA-01858 - a non-numeric character was found where a numeric

546595 Jan 21, 2008 6:44 PM (in response to 618517)
you can not use alias in the same query, you can use alias of the subquery in the outer query. not sure what you want , but you will have modify rou query with subquery if you want ti use the values from query.
just example
SQL> ed
Wrote file afiedt.buf

1 SELECT dt
2 FROM (SELECT SYSDATE dt
3* FROM DUAL)
SQL> /

DT
---------
21-JAN-08
Like Show 0 Likes(0)

Actions

2. Re: DATE PROBLEM ORA-01858 - a non-numeric character was found where a num

513949 Jan 21, 2008 6:48 PM (in response to 618517)

Try:

CREATE VIEW HolsInNextMonth AS

SELECT Site_Details.site_code,

Site_Details.name,

Holiday_Details.hol_code,

Holiday_Details.description,

Departure_Dates.Departure_Date,

TRUNC(ADD_MONTHS(SYSDATE, 1), 'MONTH') AS next_month_start,

LAST_DAY(ADD_MONTHS(SYSDATE, 1)) AS next_month_end

FROM Site_Details, Holiday_Details, Departure_Dates

WHERE Departure_Dates.Departure_Date BETWEEN TRUNC(ADD_MONTHS(SYSDATE, 1), 'MONTH') 

AND LAST_DAY(ADD_MONTHS(SYSDATE, 1))

I think this query solves the error you are trying to solve.

Another question: you have three tables in FROM clause, but any join comparison on WHERE clause. It will result in a cartesian product. Is it your intention?

3. Re: DATE PROBLEM ORA-01858 - a non-numeric character was found where a numeric

546595 Jan 21, 2008 6:58 PM (in response to 618517)
or may be you can use
TRUNC(ADD_MONTHS(SYSDATE, 1), 'MONTH') and LAST_DAY(ADD_MONTHS(SYSDATE, 1)) in the where clause direcly instead of alise as variable
somthing like this
WHERE Departure_Dates.Departure_Date BETWEEN TRUNC(ADD_MONTHS(SYSDATE, 1), 'MONTH') AND LAST_DAY(ADD_MONTHS(SYSDATE, 1))
oops too late
Message was edited by:
devmiral
Like Show 0 Likes(0)

Actions
4. Re: DATE PROBLEM ORA-01858 - a non-numeric character was found where a numeric

618517 Jan 21, 2008 7:03 PM (in response to 618517)

Hi Guys

Thank you all.

miguelfornari solution worked a treat thank you all though. Im sure i did try this and i got another error but just re-tried it and it worked fine.

Thank you very much!
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.