Please check out the following query. The SQL isn't as bad as it looks. Basically, we have a fact table and some simple joins to some dimension tables. Then we have a join to a derived table, given the alias ACCOUNTS-DIM-DEP
SELECT dw_mgr.fa_trans_fct.period,
dw_mgr.fa_trans_fct.asset_cost_company_code,
dw_mgr.fa_trans_fct.asset_cost_center_id,
dw_mgr.fa_trans_fct.depreciation_account_id,
accounts_dim_dep.description,
dw_mgr.projects_dim.project_num,
dw_mgr.projects_dim.project_name,
ROUND (dw_mgr.fa_trans_fct.activity_deprn_amount_us, 2),
organizations_cost.major_geography,
organizations_cost.business_unit || organizations_cost.bu_desc,
organizations_cost.industry_sector_num
||organizations_cost.industry_sector_desc,
hyperion_organizations.hyperion_num,
hyperion_organizations.hyperion_desc,
hyperion_organizations.hyperion_reporting
FROM dw_mgr.fa_trans_fct,
(SELECT DISTINCT flex_value account_id, description
FROM rf_fnd_flex_values_det
WHERE flex_value_set_id = '1002363'
AND summary_flag = 'N') accounts_dim_dep,
dw_mgr.projects_dim,
dw_mgr.organizations organizations_cost,
dw_mgr.organizations hyperion_organizations
WHERE
--Fact to Org on Company Code / Cost Center
(dw_mgr.fa_trans_fct.asset_cost_center_id
= organizations_cost.cost_center_id)
AND (dw_mgr.fa_trans_fct.asset_cost_company_code
= organizations_cost.company_code)
--Fact to Projects Dim on Proj Num
AND (dw_mgr.projects_dim.project_num = dw_mgr.fa_trans_fct.project_num)
--Fact to Accounts_Dim_Dep on Account ID
--convert account_ID on left to_number??????
AND (accounts_dim_dep.account_id
= dw_mgr.fa_trans_fct.depreciation_account_id)
--Fact Hyp Company Code Cost Center to Hyp Org
AND (hyperion_organizations.cost_center_id
= dw_mgr.fa_trans_fct.asset_cost_center_id AND
hyperion_organizations.company_code
= dw_mgr.fa_trans_fct.asset_cost_company_code)
--Filters
AND (
dw_mgr.fa_trans_fct.period IN ('01-Jun-2009')
--works
--AND dw_mgr.fa_trans_fct.asset_cost_center_id IN ('000296')
--does not work
AND dw_mgr.fa_trans_fct.asset_cost_center_id IN ('000296','000296')
AND dw_mgr.fa_trans_fct.asset_cost_company_code = '0007'
)
------------------------------------------------------------
Statement Id=4203172 Type=
Cost=2.64018716311899E-308 TimeStamp=06-10-09::17::51:43
(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 6
(14) NESTED LOOPS
Est. Rows: 1 Cost: 6
(11) NESTED LOOPS
Est. Rows: 1 Cost: 5
(9) HASH JOIN
Est. Rows: 1 Cost: 3
(3) TABLE TABLE ACCESS BY INDEX ROWID DW_MGR.ORGANIZATIONS [Analyzed]
(3) Blocks: 1,669 Est. Rows: 1 of 31,748 Cost: 1
Tablespace: DIM_DATA
(2) INDEX (UNIQUE) INDEX UNIQUE SCAN DW_MGR.ORG_PK [Analyzed]
Est. Rows: 1 Cost: 1
(8) PARTITION RANGE SINGLE
Est. Rows: 7 Cost: 1
(7) PARTITION LIST ALL
Est. Rows: 7 Cost: 1
(6) TABLE TABLE ACCESS BY LOCAL INDEX ROWID DW_MGR.FA_TRANS_FCT [Analyzed]
Blocks: 1,431,026 Est. Rows: 7 of 32,900,663 Cost: 1
(5) BITMAP CONVERSION TO ROWIDS
(4) INDEX (BITMAP) BITMAP INDEX SINGLE VALUE DW_MGR.FA_TRANS_AST_COMP_CC_BM_I
(10) REMOTE REMOTE.RF_FND_FLEX_VALUES_DET
Est. Rows: 1 Cost: 2
(13) TABLE TABLE ACCESS BY INDEX ROWID DW_MGR.PROJECTS_DIM [Analyzed]
(13) Blocks: 12,184 Est. Rows: 1 of 163,117 Cost: 1
Tablespace: PROJECT_DATA
(12) INDEX (UNIQUE) INDEX UNIQUE SCAN DW_MGR.PROJECTS_UI [Analyzed]
Est. Rows: 1 Cost: 1
The users were complaining that when their WebI (business intelligence) report included multiple COST CENTERS in their filter, resulting in a SQL with an "IN" include multiple values, the following error was returned:
[1]: (Error): ORA-01722: invalid number ORA-02063: preceding line from [dbname]
Otherwise, for a single COST CENTER, the report worked fine. The interesting part is that I noticed that the following join condition, which to me appears UNRELATED, was negatively impacting the SQL:
accounts_dim_dep.account_id = dw_mgr.fa_trans_fct.depreciation_account_id
The problem here is that the column on the left, accounts_dim_dep.account_id, is defined in the db as a charchar and the col on the right, dw_mgr.fa_trans_fct.depreciation_account_id, is defined as a number.
When I modified the join condition to convert the number to a varchar...
accounts_dim_dep.account_id
= to_char(dw_mgr.fa_trans_fct.depreciation_account_id)
...the SQL works regardless of the number of COST CENTERS that are specified in the filter.
I'd like to know how a type mismatch on one seemingly unrelated column affects the whether one can specify multiple COST CENTERS in the IN list.
If you had PLW errors enabled, you'd have been alerted to the situation earlier - you'd have gotten a "conversion away from type" error. I re-wrote your query:
SELECT t.period,
t.asset_cost_company_code,
t.asset_cost_center_id,
t.depreciation_account_id,
add.description,
pd.project_num,
pd.project_name,
ROUND(t.activity_deprn_amount_us, 2),
o.major_geography,
o.business_unit || o.bu_desc,
o.industry_sector_num || o.industry_sector_desc,
o.hyperion_num,
o.hyperion_desc,
o.hyperion_reporting
FROM DW_MGR.FA_TRANS_FCT t
JOIN DW_MGR.PROJECTS_DIM pd ON pd.project_num = t.project_num
JOIN DW_MGR.ORGANIZATIONS o ON o.cost_center_id = t.asset_cost_center_id
AND o.company_code = t.asset_cost_company_code
JOIN (SELECT TO_NUMBER(rffvd.flex_value) 'account_id',
rffvd.description
FROM RF_FND_FLEX_VALUES_DET rffvd
WHERE rffvd.flex_value_set_id = '1002363'
AND rffvd.summary_flag = 'N'
GROUP BY rffvd.flex_value,
rffvd.description) add ON add.account_id = t.depreciation_account_id
WHERE t.period IN ('01-Jun-2009')
AND t.asset_cost_center_id IN ('000296','000296') --doesn't work
AND t.asset_cost_company_code = '0007'
Changelog:
TO_NUMBER(RF_FND_FLEX_VALUES_DET.flex_value) so the conversion occurs before the JOINI don't know why the ORA error would occur on 2+ entries in the IN clause, but it you provide two of the same as you posted then it's not likely to be a data issue.