Natavia Finnie
asked on
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!
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 ;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now I get this when I remove the space: Warning: ociexecute() [function.ociexecute]: ORA-01861: literal does not match format string
ASKER
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 :(
$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 :(
Try rewriting the line 23 this way:
order by to_date(T1.months, 'l') desc
Hello, I am thinking that the there is still and error in that. So try either of these
or
order by to_date(T1.months, 'I') desc
or
order by to_date(T1.months, 'Y') desc
ASKER
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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I want to thank all of you! It finally works!!!!
ASKER
I am so confused!!!