Re: ORA-01481: invalid number format model....Line 33
From: will <wmlpez_at_gmail.com>
Date: Fri, 7 Aug 2009 07:46:50 -0700 (PDT)
Message-ID: <2d16c56d-1cd6-405f-babc-573ccffc9fff_at_f10g2000vbf.googlegroups.com>
On Aug 7, 9:21 am, ddf <orat..._at_msn.com> wrote:
> 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
Date: Fri, 7 Aug 2009 07:46:50 -0700 (PDT)
Message-ID: <2d16c56d-1cd6-405f-babc-573ccffc9fff_at_f10g2000vbf.googlegroups.com>
On Aug 7, 9:21 am, ddf <orat..._at_msn.com> wrote:
> 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
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. Received on Fri Aug 07 2009 - 09:46:50 CDT