Re: ORA-01481: invalid number format model....Line 33
From: ddf <oratune_at_msn.com>
Date: Fri, 7 Aug 2009 06:57:37 -0700 (PDT)
Message-ID: <adbc83b2-4c25-4bad-bd2c-40f37b6bf409_at_j19g2000vbp.googlegroups.com>
On Aug 7, 8:52 am, will <wml..._at_gmail.com> wrote:
> 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
Date: Fri, 7 Aug 2009 06:57:37 -0700 (PDT)
Message-ID: <adbc83b2-4c25-4bad-bd2c-40f37b6bf409_at_j19g2000vbp.googlegroups.com>
On Aug 7, 8:52 am, will <wml..._at_gmail.com> wrote:
> 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
And START_DATE_TIME is defined with which datatype?
David Fitzjarrell Received on Fri Aug 07 2009 - 08:57:37 CDT