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

From: will <wmlpez_at_gmail.com>
Date: Fri, 7 Aug 2009 06:52:35 -0700 (PDT)
Message-ID: <0411045a-da20-4149-9799-9d906408c5d4_at_r24g2000vbn.googlegroups.com>

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- -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 Received on Fri Aug 07 2009 - 08:52:35 CDT