Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1112
  • Last Modified:

How do I get around this problem "a non-numeric character was found where a numeric was expected "

When I run my code in PHP I get this error message: a non-numeric character was found where a numeric was expected. When I change the last line of code "to_date('1 ' || T1.months) desc " to "to_char('1 ' || T1.months) desc " it works fine. However, it does not sort the data. I am trying to get it to print the months starting with the most recent e.g (February 2011, January 2011, December 2010, ...).  Is there a work around this? Or should I try to do it in PHP using a recordset? Please help either way.

Thanks!
select T1.months, T2.totalehrs, T2.totaleexp as totaleexp,
       T2.totalahrs, T2.totalaexp, decode(T2.totalehrs,NULL,'No data Found','') as note
from (
      select to_char(add_months(sysdate,  0  ) , 'MONTH YYYY') as months from dual 
union select to_char(add_months(sysdate,  -1 ) , 'MONTH YYYY') as months from dual 
union select to_char(add_months(sysdate,  -2 ) , 'MONTH YYYY') as months from dual 
union select to_char(add_months(sysdate,  -3 ) , 'MONTH YYYY') as months from dual 
union select to_char(add_months(sysdate,  -4 ) , 'MONTH YYYY') as months from dual 
union select to_char(add_months(sysdate,  -5 ) , 'MONTH YYYY') as months from dual 
union select to_char(add_months(sysdate,  -6 ) , 'MONTH YYYY') as months from dual)  T1,
(
select to_char(completed,'MONTH YYYY') as months, 
    nvl2( SUM(EST_HRS), SUM(EST_HRS) , '0') as totalehrs, 
    nvl2( SUM(EST_EXP), SUM(EST_EXP) , '0') as totaleexp, 
    nvl2( SUM(ACTUAL_HRS), SUM(ACTUAL_HRS), '0') as totalahrs, 
    nvl2( SUM(ACTUAL_EXP), SUM(ACTUAL_EXP), '0') as totalaexp 
from Table 
where 
    completed >= add_months(sysdate,-6)
    group by to_char(completed,'MONTH YYYY')
)  T2
   where T1.months = T2.months(+)
   order by to_date('1 ' || T1.months) desc ;

Open in new window

0
taviaf
Asked:
taviaf
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
nvl2( SUM(EST_HRS), SUM(EST_HRS) , '0') as totalehrs,

is a problem, the '0' would be varchar, while the part before is numerical.

this would be ok:
nvl2( SUM(EST_HRS), SUM(EST_HRS) , 0) as totalehrs,


same for the lines below that.
0
 
taviafAuthor Commented:
@ angellll, I made the change as you mentioned and I also removed the note column "decode(T2.totalehrs,NULL,'No data Found','') as note" and I still get the same error message: Warning: ociexecute() [function.ociexecute]: ORA-01858: a non-numeric character was found where a numeric was expected.

I am so confused!!!
0
 
Ovid BurkeCreative DirectorCommented:
On line 23 there appears to be an space at '1 '.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
taviafAuthor Commented:
Now I get this when I remove the space: Warning: ociexecute() [function.ociexecute]: ORA-01861: literal does not match format string
0
 
taviafAuthor Commented:
So I tried to set my format by doing:
$s = oci_parse($conng, "alter session set nls_date_format='MONTH YYYY'");
oci_execute($s);

Then I get: Warning: ociexecute() [function.ociexecute]: ORA-01843: not a valid month

I don't know which is correct or what to do to fix either one :(
0
 
Ovid BurkeCreative DirectorCommented:
Try rewriting the line 23 this way:

order by to_date(T1.months, 'l') desc

Open in new window


0
 
Ovid BurkeCreative DirectorCommented:
Hello, I am thinking that the there is still and error in that. So try either of these

order by to_date(T1.months, 'I') desc

Open in new window


or

order by to_date(T1.months, 'Y') desc

Open in new window

0
 
taviafAuthor Commented:
when I do
 order by to_date(T1.months, 'Y') desc
 I get this:  Warning: ociexecute() [function.ociexecute]: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

And when I do
order by to_date(T1.months, 'I') desc
I get this: Warning: ociexecute() [function.ociexecute]: ORA-01820: format code cannot appear in date input format

When I do : order by to_date(T1.months, '1') desc
I get this: Warning: ociexecute() [function.ociexecute]: ORA-01821: date format not recognized

When I do : order by to_date(T1.months, 1) desc
I get this: Warning: ociexecute() [function.ociexecute]: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Why so many different warnings?!?!?! I don't know what to do...
0
 
gatorvipCommented:
>>> order by to_date('1 ' || T1.months) desc ;

The error is here ... T1.months has the format 'MONTH YYYY'  so when you append 1 to it, you'll get something like
to_date( '1 FEBRUARY 2011') and Oracle doesn't know how to read that if your date format is not DD MONTH YYYY.

So you probably want to change the line to read

order by to_date('1 ' || T1.months, 'DD MONTH YYYY') desc ;


Another way would be to use pure dates instead of strings. You can obtain the first of the month with simple SQL: trunc(<date_field>, 'MM')
0
 
taviafAuthor Commented:
I want to thank all of you! It finally works!!!!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now