You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Non-Numeric?

10 replies [Last post]
Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25

Hello Forum Members!

I am puzzled by an error message. First, I am using the following Expression in Query to tell me how many days tenure an employee has:

TO_DATE(:1,'YYYY-MM-DD') - TO_DATE(A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD')
The Expression Type is "Number"
I am using this as a Field
The Prompt ":1" is a date that I select.
This works fine, and returns a Numeric Value of how many days the employee was hired prior to the Prompt date.

I want to use this as Criteria so I can only receive employees with 177 to 183 days of service as of the Prompt date. Here is the Criteria:

TO_DATE(:1,'YYYY-MM-DD') - TO_DATE(A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD') BETWEEN 177 AND 183

However, I get the following error message:

SQL error. Stmt #: 5653 Error Position: 1927 Return: 1858 - ORA-01858: a non-numeric character was found where a numeric was expected
A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=1858, Message=ORA-01858: a non-numeric character was found where a numeric was expected (50,380)

Can anyone shed some light on this?

Many Thanks,

Ed

CompShack's picture
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Non-Numeric?

Hey Ed,

I don't see anything wrong with what you've done. I actually ran the same criteria (see below) and it works fine! Are you sure this is what is causing your error (may be different criteria)? Can you post the actual SQL on the forum?

SELECT 'X'
  FROM DUAL
 WHERE TO_DATE (:1, 'YYYY-MM-DD') - TO_DATE (:2, 'YYYY-MM-DD') BETWEEN 177 AND 183;

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Non-Numeric?

Hey CompShack!

Here is the SQL. I ran the query two ways: First - I used the Criteria:
A.AMEX_LATE_HIRE_DT Not Greater Than :1 That ran perfectly, but it gave me all employees whose latest hire date was not greater than the Prompt date.

Then I deleted that Criteria and changed it to:
TO_DATE(TO_DATE(:1,'YYYY-MM-DD'),'YYYY-MM-DD') - TO_DATE( A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD') BETWEEN 177 AND 183 ) Unfortunately - that did not work.

Query SQL:

SELECT A.EMPLID, A.NAME, A.EMAILID, TO_CHAR(A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD'), TO_DATE(':1','YYYY-MM-DD') - TO_DATE( TO_CHAR(A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD'),'YYYY-MM-DD'), A.AMEX_UNIT_DESCR, A.DEPTNAME, A.JOBTITLE, A.LOCATION, A.LOCATION_DESCR, A.MANAGER_NAME
  FROM PS_AMX_EMPLOYEES_V A, PS_FAST_PERSGL_VW1 A1
  WHERE A.EMPLID = A1.EMPLID
    AND A1.ROWSECCLASS = 'USA&LOB'
    AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_AMX_EMPLOYEES_V A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_AMX_EMPLOYEES_V A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPL_STATUS IN ('A','L','P')
     AND A.AMEX_BUSINESS_CD = 'R9769'
     AND A.AMEX_UNIT_CD IN ('R9782','R9787')
     AND ( A.JOBCODE IN ('018013','028527','024070','028564','001708','002645','002605')
     OR A.JOBTITLE LIKE 'Customer Care P%')
     AND A.REG_REGION = 'USA'
     AND TO_DATE(TO_DATE(:1,'YYYY-MM-DD'),'YYYY-MM-DD') - TO_DATE( A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD') BETWEEN 177 AND 183 )
  ORDER BY 5

Any thoughts?

CompShack's picture
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Non-Numeric?

This is what I would like you to do - at the top of your Select change:

TO_DATE(':1','YYYY-MM-DD')
-- to: (notice that I took the ' ' around the :1)
TO_DATE(:1,'YYYY-MM-DD')

I also want you to take all of this out:
AND TO_DATE(TO_DATE(:1,'YYYY-MM-DD'),'YYYY-MM-DD') - TO_DATE( A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD') BETWEEN 177 AND 183

Run and report back, this is a good way to trouble shoot things.
If the above works, then go ahead and add your code back without the to_date being twice in there.
AND TO_DATE(:1,'YYYY-MM-DD') - TO_DATE( A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD') BETWEEN 177 AND 183

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Non-Numeric?

Hi again!

At the top of the Select in the SQL, the first instance of TO_DATE(':1','YYYY-MM-DD') you want me to take the ' ' from around the :1

Here may be the problem, or part of it...
In the SQL, the TO_DATE(':1','YYYY-MM-DD') in the Select comes from an Expression I wrote, and am using as a Field. But... in the original Expression, there is no ' ' around the :1 - PeopleSoft is putting it there somehow in the SQL. Unfortunately, I cannot edit the actual SQL (perhaps because my PeopleSoft access is Web-Based.)

Here is the actual Expression:

Expression Type: Number
Length: 10
Aggregate Function NO
Decimals: NO DECIMAL
Expression Text:
TO_DATE(:1,'YYYY-MM-DD') - TO_DATE(A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD')

CompShack's picture
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Non-Numeric?

Ed,

Not sure why the ' ' is getting placed around the 1. I just added your expression to one of my queries and didn't get any ' '. I would delete that expression and re-create it. So at the top of your select, you need

TO_DATE(:1,'YYYY-MM-DD') - TO_DATE( TO_CHAR(A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD'),'YYYY-MM-DD')

And in the criteria section, make sure you have:
TO_DATE(:1,'YYYY-MM-DD') - TO_DATE( TO_CHAR(A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN 177 AND 183

If you can get the above into your query it should then run fine.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

HH's picture
HH (not verified)
Re: Non-Numeric?

Instead of TO_DATE(:1,'YYYY-MM-DD') - TO_DATE(A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD')
use TO_DATE(:1,'YYYY-MM-DD') - A.AMEX_LATE_HIRE_DT
Since A.AMEX_LATE_HIRE_DT is a field on the table,it should already be a date and therefore no need for adding to_date.

TO_DATE(DateField) will be handled by oracle as follows:

1. Oracle will implicitly convert the date field to a char value using to_date and the nls_date param format mask..
2. and reconvert this character to a date. Since the nls date format depends on a number of factors, hence the error.

HTH.

Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Non-Numeric?

Hi CompShack and HTH,

I tried what you mentioned and the query ran, but zero rows of data were returned. I played around and came up with this Criteria which works:

My actual entry on Criteria Tab:
** Data Field between expression and expression **
A.AMEX_LATE_HIRE_DT BETWEEN (:1) - 183 AND (:1) - 177

But this is how PeopleSoft displays that piece of Criteria on the SQL Tab:
A.AMEX_LATE_HIRE_DT BETWEEN (TO_DATE(:1,'YYYY-MM-DD'))-183 AND (TO_DATE(:1,'YYYY-MM-DD'))-177

PeopleSoft added things that I did not put there, but it works. I do not understand the how or why, but it does work.

Can anyone explain why or how this happens?

Thanks,

Ed

CompShack's picture
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Non-Numeric?

I tried what you mentioned and the query ran, but zero rows of data were returned.

I'm not 100% sure but I think TO_DATE(:1,'YYYY-MM-DD') - A.AMEX_LATE_HIRE_DT needs to be TO_DATE(:1,'YYYY-MM-DD') - TO_DATE( TO_CHAR(A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD'),'YYYY-MM-DD')

We don't know what is the date format in the database so doing a straight up subtraction from TO_DATE(:1,'YYYY-MM-DD') - A.AMEX_LATE_HIRE_DT might not yield the right number!

I'm not sure about your 2nd question regarding query manager behavior. I really hardly use it, I'm more of a developer type that goes straight development tools!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Non-Numeric?

I'm not 100% sure but I think TO_DATE(:1,'YYYY-MM-DD') - A.AMEX_LATE_HIRE_DT needs to be TO_DATE(:1,'YYYY-MM-DD') - TO_DATE( TO_CHAR(A.AMEX_LATE_HIRE_DT,'YYYY-MM-DD'),'YYYY-MM-DD')

I gave that a try, but zero data rows come back. It's got to be some quirky thing with Query Manager. I wish we did not have to be stuck with Query Manager only access, but they don't trust us with anything sharper than crayons around here. ;-)

Thanks for your time, and as I mentioned before, this really is a great place to learn!!!!!

Best Regards,

Ed

Guest's picture
Guest (not verified)
Re: Non-Numeric?

Hey Ed-D and Compshack:

I am having the same problem with quotes ' ' being added in the SQL. See my example below:

I have written the following Expression:

months_between(to_date(:1, 'yyyy/mm/dd') , sysdate)+.91

The SQL adds quotes around the prompt:

months_between(to_date(':1', 'yyyy/mm/dd') , sysdate)+.91

Any ideas on how to format the prompt in the Expression to make this query work?
Any help would be appreciated.
Thanks.