Home » SQL & PL/SQL » SQL & PL/SQL » Query a view
Query a view [message #203052] Mon, 13 November 2006 13:24 Go to next message
Messages: 2
Registered: November 2006
Junior Member
There must be something i am missing on this. I have a view
create or replace view the_view AS
r.description SAR_SUBJECT,
r.visible_parameter31 SAR_PRIORITY_CODE,
r.status_name STATUS_NAME,
1) / 8) days_till_due,
to_date(r.visible_parameter2, 'YYYY-MM-DD HH24:MI:SS') DUE_DATE,
u.full_name ASSIGNED_TO,
u.user_id assigned_to_id,
to_Date(visible_parameter12, 'YYYY-MM-DD HH24:MI:SS') MODIFIED_DUE_DATE,
r.visible_parameter26 SAR_CLOSURE_DATE,
to_Date(substr(nvl(r.parameter12, r.parameter2), 1, 10) ||
' 00:00:00',
'YYYY-MM-DD HH24:MI:SS') due_date_comb
FROM kcrt_requests_v r, knta_users_v u
WHERE r.request_type_name = 'SAR'
AND r.status_name not like 'Closed%'
AND u.user_id = r.assigned_to_user_id
AND r.batch_number = 1
ORDER BY nvl(visible_parameter12, visible_parameter2)

after creating the view i try and select from the view

select * from the_view where days_till_due in (1,2,3)

I receive an error ora-01861 literal does not match format string

i have run the view query on its own and i receive results without an error.

If a run the query "select * from the_view" the query runs fine no errors and i have a results set. it is only when i add "where days_till_due in (1,2,3)" that i receive an error. i am at a loss has anybody seen this before? Is there a problem with the oracle instance or with the query?

Thanks for the help.
Re: Query a view [message #203054 is a reply to message #203052] Mon, 13 November 2006 13:46 Go to previous messageGo to next message
Messages: 7880
Registered: March 2000
Senior Member
there is one occurrence where you concatenate nvl(r.parameter12, r.parameter2) with a time before applying the dateformat and one occurrence when you don't.
Can't possibly be both right!

[edit: sorry, I didn't see the substr in the second occurrence...]

[Updated on: Mon, 13 November 2006 13:49]

Report message to a moderator

Re: Query a view [message #203056 is a reply to message #203052] Mon, 13 November 2006 13:47 Go to previous messageGo to next message
Messages: 294
Registered: February 2006
Senior Member

Please use code tags for the select statement,

Describle all the tables associated with the View.
What is the datatype of parameter12 and parameter2 columns.


[Updated on: Mon, 13 November 2006 13:48]

Report message to a moderator

Re: Query a view [message #203081 is a reply to message #203056] Mon, 13 November 2006 16:42 Go to previous message
Messages: 2
Registered: November 2006
Junior Member
the query is using two views. the parameter12 and parameter2 are both varchar2(200) there are other values besides date values in those parameters that is why i am using the view. In the view i am able to gather just the data that i need to select on. In the view all parameter2 and parameter12 is varchar2(200) with a data type of data stored as YYYY-MM-DD HH24:MI:SS. am i using the view incorrectly? Normally what happens if i select right from the views or the tables that those views are based on is that i will get the same error because the parameter12 and parameter2 are not always dates in the correct format. but when i use the view i built the result set is all in that date format. At first i thought there might be an error or a user had entered an invalid date such as an extra number in the year but i validated all the data and it is all correct. So the data the view pull is the data i want in the correct format but when i select off the view on the days_till_due field which returns the correct value as a number in the results set i receive the error.
Previous Topic: ora-1555 error
Next Topic: SQL Query - Update performance
Goto Forum:

Current Time: Mon Oct 24 04:49:27 CDT 2016

Total time taken to generate the page: 0.04176 seconds