Re: ORA-01481: invalid number format model....Line 33
From: will <wmlpez_at_gmail.com>
Date: Fri, 7 Aug 2009 06:59:29 -0700 (PDT)
Message-ID: <de953e4f-2ad0-4389-a6a9-2e8c1e856a1d_at_g1g2000vbr.googlegroups.com>
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
Date: Fri, 7 Aug 2009 06:59:29 -0700 (PDT)
Message-ID: <de953e4f-2ad0-4389-a6a9-2e8c1e856a1d_at_g1g2000vbr.googlegroups.com>
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. Received on Fri Aug 07 2009 - 08:59:29 CDT