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
