oracle "non-numeric" error

[jessieyull]

ok, this error isn't really formula related but I seem to get the best response in this forum so I thought I'd give it a try.

I have some sql views written that work perfectly well in other tools (toad, discoverer, oracle reports) but when i try to use them in crystal i get the following error...

"ORA-01858 A non-numeric character was found where a numeric was expected."

Is anyone familiar with this error? And how I can fix it?

Thanks!
jessie

 

[synapsevampire]

Post the SQL for the view.

All of the tools you mentioned are fairly forgiving, Crystal is not.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jessieyull]

Here is one of the views that is not working...

CREATE OR REPLACE VIEW LEGX_SALARY_ALL_V ( EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE, LAST_NAME, FIRST_NAME, MIDDLE_NAMES,
CHANGE_DATE, CHANGE_VALUE, CHANGE_PERCENTAGE, REASON,
SALARY, APPROVED, RATING, RANKING,
GROUP_SIZE, PAY_BASIS, PERSON_TYPE, EMPLOYEE_NUMBER,
DEPARTMENT, PERCENT_BONUS_PAYOUT, SALARY_BASIS, PAY_ANNUALIZATION_FACTOR,
UNION_NUM, PRIMARY_FLAG, CREATION_DATE, ASSIGNMENT_ID,
PERSON_ID ) AS SELECT
pee.effective_start_date
,pee.effective_end_date
,papf.last_name, papf.first_name, papf.middle_names
,pfshv.change_date
,pfshv.change_amount, pfshv.chg_percent, pfshv.reason, pfshv.actual_salary *12, pfshv.approved_flag
,ppp.attribute1, ppp.attribute2, ppp.attribute3
,pfshv.pay_basis
,ppt.user_person_type
,papf.employee_number
,haou.name
,ppp.attribute4
,ppb.name
,ppb.pay_annualization_factor,'PAY RECORD',paaf.primary_flag,ppp.creation_date
,paaf.assignment_id,papf.person_id
FROM
hr.per_all_assignments_f paaf
,hr.per_all_people_f papf
,apps.per_full_salary_history_v pfshv
,hr.per_pay_proposals ppp
,hr.pay_element_entries_f pee
,hr.per_person_types ppt
,hr.hr_all_organization_units haou
,hr.per_pay_bases ppb
WHERE
pfshv.row_id = paaf.rowid
and papf.person_id = paaf.person_id
and TRUNC(SYSDATE) between nvl(papf.effective_start_date,'01-JAN-1900')
and nvl(papf.effective_end_date,'01-DEC-2099')
and papf.person_type_id<>30
and ppp.pay_proposal_id(+) = pfshv.pay_proposal_id
and pee.creator_id(+) = ppp.pay_proposal_id
and pee.creator_type(+) = 'SP'
and pee.effective_start_date(+) = ppp.change_date
and papf.person_type_id = ppt.person_type_id
and haou.organization_id (+) = paaf.organization_id
and paaf.assignment_type = 'E'
and ppb.pay_basis_id(+) = paaf.pay_basis_id