Re: ORA-01481: invalid number format model....Line 33
From: ddf <oratune_at_msn.com>
Date: Fri, 7 Aug 2009 07:21:38 -0700 (PDT)
Message-ID: <a6ab2492-8ab1-4ba3-bbec-570f8fd25205_at_k26g2000vbp.googlegroups.com>
On Aug 7, 8:59 am, will <wml..._at_gmail.com> wrote:
> On Aug 7, 8:57 am, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > 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
>
> Hello. DATE type.- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 7 Aug 2009 07:21:38 -0700 (PDT)
Message-ID: <a6ab2492-8ab1-4ba3-bbec-570f8fd25205_at_k26g2000vbp.googlegroups.com>
On Aug 7, 8:59 am, will <wml..._at_gmail.com> wrote:
> On Aug 7, 8:57 am, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > 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
>
> 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 Received on Fri Aug 07 2009 - 09:21:38 CDT