ORA-01481: invalid number format model....Line 33

will

unread,

Aug 7, 2009, 9:52:35 AM8/7/09

to

Oracle 9i.

Hello:

My query is bombing and I'm guessing it my fault because I'm not
comparing the dates properly. Please note this query was working
before I added the where clause.

First, I set the date format: alter session set NLS_DATE_FORMAT='YYYY-
MM-DD HH24:MI:SS'; The default date format is DD-MON-YY.

Second, I find out the date I need: SELECT max(start_date_time) FROM
GENIECS.CONTACT_RECORD

Last, I plug that value in the where clause. I've tried using a
combination of the to-date & to_char functions but I'm missing
something. Here's the query. Thx

SELECT /*+ USE_HASH ( a b) */
to_char(b.start_date_time,'YYYY-MM-DD HH24:MI:SS') As actualstart,
NVL(a.model_desc,' ') As ModelDesc, CAST(c.customer_id As nvarchar2
(25)) As CustomerID,
b.call_id As CallID, NVL(c.model_number,'NA') As ModelNumber, NVL
(c.date_code,' ') As DateCode,
to_char(NVL(c.date_of_purchase,'1968-03-01'),'YYYY-MM-DD') As
DatePurchased,
to_char(NVL(o.completion_date,'1968-03-01'),'YYYY-MM-DD') As
actualend, NVL(o.order_status,' ') As OrderStatus,
NVL(f.subject_desc,' ') As subject, NVL(g.problem_desc,' ') As
category, NVL(h.resolution_desc,' ') As Resolution,
NVL(i.reason_desc,' ') As Reason,
decode(e.notes, null, '', 'AGENT NOTES: ' || e.notes) || ' ' || decode
(q.notes, null, '', '
QP NOTES: ' || q.notes) As Notes,
NVL(j.escalation_id, 0) as EscalationID, NVL(j.escalation_desc, ' ')
as EscalationDesc,
NVL(k.agent_id,' ') As ownerid, NVL(k.first_name,' ') As
AgentFirstName, NVL(k.last_name,' ') As AgentLastName
FROM GENIECS.model a, GENIECS.contact_record b, GENIECS.product_record
c, GENIECS.warranty d, GENIECS.call_detail e,
GENIECS.subject f, GENIECS.problem g, GENIECS.resolution h,
GENIECS.reason i, GENIECS.escalation j, GENIECS.agent k,
GENIECS.order_record o, GENIECS.qa q
WHERE b.start_date_time > to_date('2009-08-01 08:54:00','YYYY-MM-DD
HH24:MI:SS')
--WHERE b.start_date_time > TO_DATE('31-Jul-09','DD-MON-YY')
AND b.customer_id = c.customer_id
AND b.model_id = c.model_number
AND b.seqno = c.seqno
AND c.model_number = a.model_number
AND d.model_number = a.model_number
AND e.call_id = b.call_id
AND e.subject_id = f.subject_id
AND e.problem_id = g.problem_id
AND e.resolution_id = h.resolution_id
AND i.reason_id(+) = e.reason_id
AND j.escalation_id(+) = b.escalation_id
AND b.agent_id = k.agent_id
AND b.call_id=o.call_id(+)
AND o.order_id=q.order_id(+)
ORDER BY c.customer_id

ddf

unread,

Aug 7, 2009, 9:57:37 AM8/7/09

to

And START_DATE_TIME is defined with which datatype?

David Fitzjarrell

will

unread,

Aug 7, 2009, 9:59:29 AM8/7/09

to

Hello. DATE type.

ddf

unread,

Aug 7, 2009, 10:21:38 AM8/7/09

to

> Hello. DATE type.- Hide quoted text -
>
> - Show quoted text -

We'll need some table create statements and some sample data to try to
replicate this problem, as I, for one, can't see where the problem
lies.

David Fitzjarrell

will

unread,

Aug 7, 2009, 10:46:50 AM8/7/09

to

Thanks! I found the issue....another developer changed something in
the query....tried applying a date format to a non-date field. Sorry!
Thanks so much.