Query a view [message #203052] |
Mon, 13 November 2006 13:24  |
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   |
Frank
Messages: 7901 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 #203081 is a reply to message #203056] |
Mon, 13 November 2006 16:42  |
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.
|
|
|