ORA-01858: a non-numeric character was found where... - Page 2 - Qlik Community

joshibabu_are · ‎2017-01-31

Hi Guru's,

Iam getting ORA-01858 Error while loading below query in qlikview.

where iam wrong.help me to resolve it.

SELECT transaction_type,

(site_use_code || ' - ' || qualifier_attribute_meaning)

site_use_code,

TO_CHAR (transaction_number),

trans_creation_date,

transaction_status,

customer_name,

customer_number,

NULL call_type,

party_number,

party_name,

TO_CHAR (party_creation_date) party_creation_date,

party_addr1,

party_addr2,

party_addr3,

party_addr4,

party_addr5,

site_location,

site_addr1,

site_addr2,

site_addr3,

site_addr4,

site_addr5,

TO_CHAR (party_site_creation_date) party_site_creation_date,

party_site_status,

customer_acct_site_status,

site_use_status,

primary_flag,

identifying_address_flag,

party_site_number,

TO_CHAR (cust_acct_site_id) cust_acct_site_id,

'NA' item_number,

'NA' item_description,

user_name created_by,

'NA' so_line_type,

'NA' contract_start_date,

'NA' contract_end_date,

'NA' ib_org,

operating_unit --added by AA as per the ticket #255270 on 8nov12

FROM (SELECT (lh.list_type_code || ' Modifier') transaction_type,

(qui.qualifier_context || ' Qualifier') site_use_code,

(CASE

WHEN lh.list_type_code = 'PRL'

THEN

( lh.NAME

|| ' Price List at Group Number '

|| qui.qualifier_grouping_no)

WHEN ( lh.list_type_code = 'DLT'

AND qui.list_line_id = -1)

THEN

( lh.NAME

|| ' Modifier in List Header Qualifiers at Grouping Number '

|| qui.qualifier_grouping_no)

WHEN ( lh.list_type_code = 'SLT'

AND qui.list_line_id = -1)

THEN

( lh.NAME

|| ' Modifier in List Header Qualifiers at Grouping Number '

|| qui.qualifier_grouping_no)

ELSE

( lh.NAME

|| ' Modifier in Line Modifier No '

|| qui.list_line_id

|| ' Grouping Number '

|| qui.qualifier_grouping_no)

END)

transaction_number,

qui.creation_date trans_creation_date,

'Active' transaction_status,

ac.customer_name,

ac.customer_number,

party.party_number,

party.party_name,

party.party_type,

party.creation_date party_creation_date,

' Account Site Level Only' party_addr1,

' Account Site Level Only' party_addr2,

' Account Site Level Only' party_addr3,

' Account Site Level Only' party_addr4,

' Account Site Level Only' party_addr5,

su.LOCATION site_location,

loc.address1 site_addr1,

loc.address2 site_addr2,

loc.address3 site_addr3,

loc.address4 site_addr4,

DECODE (loc.city, NULL, NULL, loc.city || ', ')

|| DECODE (loc.state,

NULL, loc.province || ', ',

loc.state || ', ')

|| DECODE (loc.postal_code,

NULL, NULL,

loc.postal_code || ', ')

|| DECODE (loc.country, NULL, NULL, loc.country)

site_addr5,

ps.creation_date party_site_creation_date,

ps.status party_site_status,

cas.status customer_acct_site_status,

su.status site_use_status,

cas.bill_to_flag,

su.primary_flag,

ps.identifying_address_flag,

cas.ship_to_flag,

su.site_use_id,

su.cust_acct_site_id,

loc.location_id,

ps.party_site_id,

ps.party_id,

ps.party_site_number,

cas.cust_account_id,

qui.qualifier_grouping_no,

qui.list_line_id,

qui.qualifier_id,

qui.qualifier_attr_value,

(CASE

--WHEN qui.qualifier_attribute IS NULL

--THEN 'No Qualifier'

WHEN qui.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2'

THEN

'Customer Name'

WHEN qui.qualifier_attribute = 'QUALIFIER_ATTRIBUTE14'

THEN

'BILL_TO'

WHEN qui.qualifier_attribute = 'QUALIFIER_ATTRIBUTE11'

THEN

'SHIP_TO'

WHEN ( qui.qualifier_attribute =

'QUALIFIER_ATTRIBUTE32'

AND qui.qualifier_context = 'CUSTOMER')

THEN

'Item Source'

ELSE

'Qualifier Type Not Mapped, Call IT'

END)

qualifier_attribute_meaning,

qui.list_header_id qual_list_header_id,

qui.start_date_active qual_start_date_active,

qui.end_date_active qual_end_date_active,

(CASE

WHEN qui.end_date_active IS NULL

THEN

TO_DATE ('01-MAR-2099')

ELSE

qui.end_date_active

END)

qual_end_date,

(CASE

WHEN lh.end_date_active IS NULL

THEN

TO_DATE ('01-MAR-2099')

ELSE

lh.end_date_active

END)

header_end_date,

qui.rule_name qual_rule_name,

qui.rule_description qual_rule_description,

lh.creation_date header_creation_date,

lh.list_header_id header_list_header_id,

lh.start_date_active header_start_date_active,

lh.end_date_active header_end_date_active,

lh.active_flag,

qui.created_by,

names.user_name,

hr.NAME operating_unit

--added by AA as per the ticket #255270 on 8nov12

FROM qp_secu_list_headers_vl lh,

qp_qualifiers_v qui,

ar_customers ac,

hz_cust_site_uses_all su,

hz_party_sites ps, --

hz_locations loc, --

hz_parties party, --

hz_cust_acct_sites_all cas,

fnd_user names,

hr_operating_units hr

--added by AA as per the ticket #255270 on 8nov12

WHERE qui.list_header_id = lh.list_header_id

AND qui.qualifier_context = 'CUSTOMER'

--and to_char(ac.customer_id) = qui.QUALIFIER_ATTR_VALUE

AND cas.cust_account_id = ac.customer_id

AND qui.qualifier_attr_value = su.site_use_id

AND su.cust_acct_site_id = cas.cust_acct_site_id(+)

AND cas.party_site_id = ps.party_site_id(+)

AND ps.party_id = party.party_id

AND loc.location_id(+) = ps.location_id

AND lh.active_flag = 'Y'

AND qui.qualifier_attribute IN

('QUALIFIER_ATTRIBUTE14', 'QUALIFIER_ATTRIBUTE11')

--and lh.name = 'PLADOD'

AND qui.created_by = names.user_id(+)

AND su.org_id = hr.organization_id --added by AA as per the ticket #258775 on 18DEC12

) mod_data

WHERE SYSDATE < qual_end_date AND SYSDATE < header_end_date;

Thanks,

Joshi

damian_thomson · ‎2017-11-06

Thanks for this article

We had a similar issue where the SQL worked in Oracle SQL Developer but failed on the Qlik Oracle Connector, we narrowed it down to our WHERE clause as it was filtering on a date being = '01-Jan-1970'.

We tried to_date which didnt resolve the issue and then looked at the source column which was a timestamp, by changing our where clause:

WHERE

A.closeddate = to_timestamp('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

The issue was resolved

ORA-01858: a non-numeric character was found where a numeric was expected