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
non20
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
SELECT r.Request_id REQUEST_ID,
r.description SAR_SUBJECT,
r.visible_parameter31 SAR_PRIORITY_CODE,
r.status_name STATUS_NAME,
round(kcvs_utilities.CALC_NUM_HRS(current_Date,
to_date(nvl(r.parameter12,
r.parameter2),
'YYYY-MM-DD HH24:MI:SS'),
9,
'9:00',
17,
'17:00',
'USER',
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
Frank
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
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

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.


regards

[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
non20
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: Sat Dec 03 10:08:35 CST 2016

Total time taken to generate the page: 0.08585 seconds