Home » SQL & PL/SQL » SQL & PL/SQL » Need suggestions on Debugging a huge Union query with inline views (merged)
| Need suggestions on Debugging a huge Union query with inline views (merged) [message #384391] |
Tue, 03 February 2009 10:40  |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
I am trying to debug a huge sql*plus report that has a bunch of unions and inline views. Within each inline view it produces different sections of the reports output. My problem is the final report will show duplicate line information. Example,
xxxx 8989
xxxx 0000
xxcc 8989
xxcc 0000
It should be just:
xxxx 0000
xxcc 8989
I ran the individual inline views separately and no duplicates were produced within the inline queries, so I'm confused to as where the problem may be coming from. I even checked the last part of the huge where clauses in the report.
Any suggestions on how to debug this? Maybe I can force the one line to appear in the report some how using some type of code trick....any suggestions?
Anne
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views [message #384395 is a reply to message #384391] |
Tue, 03 February 2009 10:52  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Well to me this looks like a cartesian product. It's a pure guess since you have not shared with us how you get the data using what views.
I assume you get col_1 from one view and you get col_2 from another view and in your outer select statement you select these two columns from these views without a proper join condition.
I could be completely wrong. If you need some help then you should spend some time in reading the forum guidelines and provide what is requested over there.
Regards
Raj
|
|
|
|
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384410 is a reply to message #384407] |
Tue, 03 February 2009 13:52 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
This is what it looks like:
Program takes six parameters#
##1 from period
##2 to period
##3 from project org number
##4 to project org number
##5 from project number
##6 to project number
##7 from natural account
##8 to natural account
SET termout off
SET head off
SET pages 0
SET feedback off
SET verify off
SET lines 2000
SET define #
COL start_date noprint new_value s_date
COL end_date noprint new_value e_date
COL err_msg noprint new_value err_message
COL sort_col noprint
---------------------------------------------------
-- Get period start and end dates
-- This query replaced above query June 25, 2008
---------------------------------------------------
SELECT s.start_date, e.end_date,
DECODE
(SIGN (e.end_date - s.start_date),
-1, '*** Note: From Period is later than To Period, no expenditures reported ***'
) err_msg
FROM apps.pa_periods s, apps.pa_periods e
WHERE TO_DATE (SUBSTR ('##1', 1, 10), 'YYYY/MM/DD') BETWEEN s.start_date
AND s.end_date
AND TO_DATE (SUBSTR ('##2', 1, 10), 'YYYY/MM/DD') BETWEEN e.start_date
AND e.end_date;
---------------------------------------------------
SET termout on
--Setting Headers and columns
SELECT 'TAMS Project Expenditures for Dates ##s_date - ##e_date, Project Organizations ##3 - ##4, Projects ##5 - ##6 and Accounts ##7 - ##8',
0 sort_col
FROM DUAL
UNION
SELECT '##err_message', 1
FROM DUAL
UNION
SELECT 'Award Number^Project Number^Task Number^Project Start Date^Project End Date^Status^Project Manager^'
|| 'Task Start Date^Task Completion Date^Task Manager^Award Short Name^Project Organization^'
|| 'Task Organization^Expense Code^OMB Code^Revenue Line^Burden Rate^Burden Structure^Site^Sponsor^Type^Customer^'
|| 'Award Type^Award Purpose^Federal Flow Thru Code^IDC Schedule Name^Total Expenditure^Direct Charges^'
|| 'Indirect Charges^Cost Share Charges^Program Income^Total Commitments^Direct Commitments^Indirect Commitments^Cost Share Commitments^'
|| 'Total Invoiced^Total Revenue^Total Funding^'
|| 'Total Budget^Direct Budget^Indirect Budget^Cost Share Budget',
2
FROM DUAL
UNION
SELECT DISTINCT a.award_number
|| '^'
|| p.segment1
|| '^'
|| t.task_number
|| '^'
|| p.start_date
|| '^'
|| p.completion_date
|| '^'
|| p.project_status_code
|| '^'
|| m.full_name
|| '^'
-- || ra.full_name responsible accountant
-- || '^'
|| t.start_date
|| '^'
|| t.completion_date
|| '^'
|| tm.full_name
|| '^'
|| RTRIM (TRANSLATE (a.award_short_name, CHR (10), ' '))
|| '^'
|| o1.NAME
|| '^'
|| o2.NAME
|| '^'
|| NVL (c1.class_code, ' ')
|| '^'
|| NVL (c2.class_code, ' ')
|| '^'
|| NVL (c3.class_code, ' ')
|| '^'
|| NVL (c4.class_code, ' ')
|| '^'
|| NVL (c5.class_code, ' ')
|| '^'
|| NVL (c6.class_code, ' ')
|| '^'
|| NVL (c7.class_code, ' ')
|| '^'
|| NVL (c8.class_code, ' ')
|| '^'
|| c.customer_name
|| '^'
|| a.TYPE
|| '^'
|| a.award_purpose_code
|| '^'
|| a.attribute10
|| '^'
|| i.ind_rate_sch_name
|| '^'
|| e.total_exp
|| '^'
|| e.dir_exp
|| '^'
|| e.ind_exp
|| '^'
|| e.cs_exp
|| '^'
|| e.program_income --New column added 1/12/09
|| '^'
|| ct.tot_commit
|| '^'
|| ct.dir_commit
|| '^'
|| ct.ind_commit
|| '^'
|| ct.cs_commit
|| '^'
|| e.billed
|| '^'
|| e.rev
|| '^'
|| f.funding
|| '^'
|| b.budget_total
|| '^'
|| b.direct_budget
|| '^'
|| b.indirect_budget
|| '^'
|| b.cs_budget,
3
FROM
--Breakdown of Budget total, direct budget, cs budget, indirect budget
(SELECT gbca.award_id, gra.project_id, gra.task_id,
gra.budget_version_id,
SUM (bl.burdened_cost) budget_total,
SUM
(DECODE (SUBSTR (r.NAME, 1, 10),
'Award Reve', NVL (bl.burdened_cost, 0),
'F & A Cost', NVL (bl.burdened_cost, 0),
0
)
) indirect_budget,
SUM (DECODE (SUBSTR (r.NAME, 1, 10),
'Award Reve', 0,
'F & A Cost', 0,
'Cost Share', 0,
NVL (bl.burdened_cost, 0)
)
) direct_budget,
SUM (DECODE (SUBSTR (r.NAME, 1, 10),
'Cost Share', NVL (bl.burdened_cost, 0),
0
)
) cs_budget
FROM apps.gms_resource_assignments gra,
apps.gms_budget_lines bl,
apps.pa_resource_list_members rlm,
apps.pa_resources r,
apps.gms_budgetary_control_award_v gbca
WHERE rlm.resource_id = r.resource_id
AND gra.resource_list_member_id =
rlm.resource_list_member_id
AND bl.resource_assignment_id =
gra.resource_assignment_id
AND gbca.project_id = gra.project_id
AND gbca.resource_list_id = rlm.resource_list_id
--and gbv.budget_type_code = 'AC'
AND gra.budget_version_id IN (
SELECT MAX (bv.budget_version_id)
FROM gms.gms_budget_versions bv
WHERE bv.project_id =
gra.project_id)
GROUP BY gbca.award_id,
gra.project_id,
gra.task_id,
gra.budget_version_id) b,
--Breakdown of tot commit, dir commit,ind commit, cs commit
(SELECT gbca.award_id, pct.project_id, pct.task_id,
SUM (pct.tot_cmt_burdened_cost) tot_commit,
SUM (DECODE (pct.expenditure_category,
'F & A Costs', 0,
'Cost Share', 0,
NVL (pct.tot_cmt_burdened_cost, 0)
)
) dir_commit,
SUM (DECODE (pct.expenditure_category,
'F & A Costs', NVL (pct.tot_cmt_burdened_cost, 0),
0
)
) ind_commit,
SUM (DECODE (pct.expenditure_category,
'Cost Share', NVL (pct.tot_cmt_burdened_cost, 0),
0
)
) cs_commit
FROM apps.pa_commitment_txns pct, apps.gms_budgetary_control_award_v gbca,apps.gms_awards_basic_v gmsa
WHERE gbca.project_id = pct.project_id
and gmsa.award_id =gbca.award_id
and gmsa.TASK_ID = pct.task_id
GROUP BY gbca.award_id, pct.project_id, pct.task_id)ct,
--Breakdown for tot exp, dir exp, ind exp, cs exp, program income, billed, rev
(SELECT gmsa.award_id,
gmsac.project_id,
gmsac.task_id,
--SUM(NVL(GMSAC.BURDENED_COST,0)) TOTAL_EXP,
SUM(DECODE (SUBSTR (gmsac.expenditure_type, 1, 3),
'IDC', 0,
NVL (GMSAC.BURDENED_COST, 0)))
+ SUM(DECODE (SUBSTR (et.expenditure_category, 1, 10),
'F & A Cost', NVL (GMSAC.BURDENED_COST, 0),
0))
+ SUM(DECODE (SUBSTR (et.expenditure_category, 1, 20),
'Cost Share', NVL (GMSAC.BURDENED_COST, 0),
'Cost Share-Direct', NVL (GMSAC.BURDENED_COST, 0),
'Cost Share-Indirect', NVL (GMSAC.BURDENED_COST, 0),
'Research Enhancement', NVL (GMSAC.BURDENED_COST, 0),
0))
+ SUM(DECODE (SUBSTR (et.expenditure_category, 1, 16),
'Program Income', NVL (GMSAC.BURDENED_COST, 0),
'Program - Income', NVL (GMSAC.BURDENED_COST, 0),
0))
TOTAL_EXP,
--SUM(cdl.PROJECT_BURDENED_COST) total_exp,
SUM(Decode(substr(gmsac.expenditure_type,1,3), 'IDC', 0, NVL(GMSAC.BURDENED_COST,0))) DIR_EXP,
--SUM(DECODE(SUBSTR(et.expenditure_category,1,10),'F & A Cost',0,
--'Cost Share',0,
--NVL(gmsac.burdened_cost,0))) dir_exp,
SUM(DECODE(SUBSTR(et.expenditure_category,1,10),'F & A Cost',NVL(GMSAC.BURDENED_COST,0), 0)) IND_EXP,
--NVL(cdl.PROJECT_BURDENED_COST,0),0)) ind_exp,
--SUM(DECODE(SUBSTR(et.expenditure_category,1,10),'Cost Share',NVL(GMSAC.BURDENED_COST,0), 0)) CS_EXP,
SUM(DECODE(SUBSTR(et.expenditure_category, 1, 20),
'Cost Share', NVL(GMSAC.BURDENED_COST, 0),
'Cost Share-Direct', NVL(GMSAC.BURDENED_COST, 0),
'Cost Share-Indirect', NVL(GMSAC.BURDENED_COST, 0),
'Research Enhancement', NVL(GMSAC.BURDENED_COST, 0),0)) CS_EXP,
--NVL(cdl.PROJECT_BURDENED_COST,0),0)) cs_exp,
SUM(DECODE(SUBSTR(et.expenditure_category,1,16),'Program Income',NVL(GMSAC.BURDENED_COST,0),'Program - Income',NVL(GMSAC.BURDENED_COST, 0),0)) PROGRAM_INCOME,
SUM(NVL(gmsac.BILLED_AMOUNT,0)) billed,
SUM(NVL(gmsac.REVENUE_AMOUNT,0)) rev
FROM tams.tams_gms_status_actuals gmsac,
apps.pa_expenditure_types et,
apps.gms_awards_all gmsa,
apps.gl_code_combinations gcc,
apps.PA_COST_DISTRIBUTION_LINES_ALL cdl
WHERE
cdl.EXPENDITURE_ITEM_ID (+) = gmsac.expenditure_item_id
and gmsac.award_id = gmsa.AWARD_ID
-- and gmsac.pa_date(+) BETWEEN to_date('##s_date','DD-MON-YYYY') AND to_date('##e_date','DD-MON-YYYY') --changed Nov 5, 2008
and gmsac.pa_date(+) BETWEEN to_date(substr('##1',1,10),'YYYY/MM/DD') AND to_date(substr('##2',1,10),'YYYY/MM/DD')
and gcc.CODE_COMBINATION_ID = cdl.dr_CODE_COMBINATION_ID
and cdl.line_num ='1'
and gcc.segment3 between nvl('##7',gcc.segment3) and nvl('##8',gcc.segment3)
and et.EXPENDITURE_TYPE (+) = gmsac.expenditure_type
group by gmsa.award_id,gmsac.project_id,
gmsac.task_id) e,
-- Breakdown of funding and rest of query
(SELECT pf.project_id, pf.task_id,
SUM (pf.funding_amount) funding
FROM apps.gms_project_fundings pf
GROUP BY pf.project_id, pf.task_id) f,
apps.gms_awards_all a,
apps.pa_projects_all p,
apps.pa_tasks t,
apps.ra_customers c,
apps.pa_project_parties pp1,
apps.pa_project_parties pp2,
apps.gms_project_fundings pf,
apps.gms_installments gi,
apps.hr_all_organization_units o1,
apps.hr_all_organization_units o2,
apps.pa_segment_value_lookups d1,
apps.per_people_f m,
apps.per_people_f tm,
-- apps.per_people_f ra, --not using column in report
apps.pa_project_classes c1,
apps.pa_project_classes c2,
apps.pa_project_classes c3,
apps.pa_project_classes c4,
apps.pa_project_classes c5,
apps.pa_project_classes c6,
apps.pa_project_classes c7,
apps.pa_project_classes c8,
apps.pa_ind_rate_schedules_all_bg i
WHERE p.project_type = 'Sponsored Program'
AND p.segment1 BETWEEN NVL ('##5', p.segment1)
AND NVL ('##6', p.segment1)
AND t.project_id = p.project_id
AND pp1.project_id = p.project_id
AND pp1.project_role_id = 1 -- project manager
AND pp1.start_date_active <= SYSDATE
AND NVL (pp1.end_date_active, SYSDATE) >= SYSDATE
AND m.person_id = pp1.resource_source_id
AND pp2.project_id = p.project_id
AND pp2.project_role_id = 1004 -- responsible accountant
AND pp2.start_date_active <= SYSDATE
-- AND ra.person_id(+) = pp2.resource_source_id --not using column in report anymore
AND NVL (pp2.end_date_active, SYSDATE) >= SYSDATE
AND o1.organization_id = p.carrying_out_organization_id
AND d1.segment_value_lookup = o1.NAME
AND d1.segment_value BETWEEN NVL ('##3', d1.segment_value)
AND NVL ('##4', d1.segment_value)
AND o2.organization_id = t.carrying_out_organization_id
AND c1.project_id = p.project_id
AND c1.class_category = 'Expense Code'
AND c2.project_id = p.project_id
AND c2.class_category = 'OMB A-21'
AND c3.project_id = p.project_id
AND c3.class_category = 'Revenue Line'
AND c4.project_id = p.project_id
AND c4.class_category = 'Burden Rate'
AND c5.project_id = p.project_id
AND c5.class_category = 'Burden Structure'
AND c6.project_id = p.project_id
AND c6.class_category = 'Site'
AND c7.project_id = p.project_id
AND c7.class_category = 'Sponsor'
AND c8.project_id = p.project_id
AND c8.class_category = 'Type'
AND pf.task_id = t.task_id
AND gi.installment_id = pf.installment_id
AND a.award_id = gi.award_id
AND pf.project_id = t.project_id
AND b.project_id(+) = t.project_id
AND b.task_id(+) = t.task_id
AND ct.project_id(+) = t.project_id
AND ct.task_id(+) = t.task_id
AND tm.person_id(+) = t.task_manager_person_id
AND e.project_id = t.project_id
AND e.task_id = t.task_id
AND f.project_id = t.project_id
AND f.task_id = t.task_id
AND i.ind_rate_sch_id = a.idc_schedule_id
AND c.customer_id = a.funding_source_id
and a.award_number in ('532437C10', '532437C11') -- for testing only remove
ORDER BY 2;
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384430 is a reply to message #384391] |
Tue, 03 February 2009 16:49 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
First off, use UNION ALL and NOT UNION.
UNION will perform a distinct between "queries" which involves sorting the data, and you are only generating header data in the first 2 UNION'ed queries.
Then you can get rid of the ORDER BY at the bottom, as it only orders by your generated number in column position 2.
As for the duplicates, the UNION is not the cause here, its the result of that huge query which needs splitting down into its component inline views and joined back together slowly.
Check each join condition carefully as this is where the bug will likey be.
[Updated on: Tue, 03 February 2009 16:49] Report message to a moderator |
|
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384570 is a reply to message #384410] |
Wed, 04 February 2009 05:41 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Did you try to get an explain plan for the query and if so could you please post it here in a formatted fashion.
I think it could be either of these two
a) You are missing a join
b) There is a one to many relationship between some of your tables and it is not been filtered to the level you are expecting.
Without the explain plan and the data model relationship it is very difficult to comment which step could lead to this issue you have mentioned.
Hope this helps.
Regards
Raj
|
|
|
|
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384608 is a reply to message #384504] |
Wed, 04 February 2009 07:37 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
The requirement is:
xxxx 8989
xxcc 0000
See below an example of the real output that is duplicates being produced from the report. I removed some of the columns so it could fit here, for you to view. I should only see award #532437C14 with 0 amounts, and 532437C15 with the amounts you see below. I noticed because the project numbers are the same, it's not distinguishing or breaking it down right. That is why I used Distinct in my select statement hoping it would break it down.
Also I'm not sure why the rows with 0 amounts are even being produced from the report????
Award# Project # Task # Total Exp. Dir. Charges
532437C14 532437 U11 -257.18 3473.85
532437C14 532437 U11 0 0
532437C15 532437 U11 -257.18 3473.85
532437C15 532437 U11 0 0
|
|
|
|
|
|
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384624 is a reply to message #384619] |
Wed, 04 February 2009 09:11 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Au contraire,
In an SQL Editor:explain plan for select sysdate from dual;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS FULL | DUAL | | | |
--------------------------------------------------------------------
Note: rule based optimization
Now try with the query of your choice.
I'll be honest - I think that view's too big for an Explain Pan to tell us anything. If you do post one, could you post it as an attachment, so I don't have to scroll down through 300 lines of plat to get to the bottom?
|
|
|
|
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384630 is a reply to message #384629] |
Wed, 04 February 2009 09:40 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You have a very large query that you posted further up.
Just typeEXPLAIN PLAN FOR
SELECT 'TAMS Project Expenditures for Dates ##s_date - ##e_date, Project Organizations ##3 - ##4, Projects ##5 - ##6 and Accounts ##7 - ##8',
0 sort_col
FROM DUAL
UNION
SELECT '##err_message', 1
FROM DUAL
UNION
SELECT 'Award Number^Project Number^Task Number^Project Start Date^Project End Date^Status^Project Manager^'
|| 'Task Start Date^Task Completion Date^Task Manager^Award Short Name^Project Organization^'
|| 'Task Organization^Expense Code^OMB Code^Revenue Line^Burden Rate^Burden Structure^Site^Sponsor^Type^Customer^'
|| 'Award Type^Award Purpose^Federal Flow Thru Code^IDC Schedule Name^Total Expenditure^Direct Charges^'
|| 'Indirect Charges^Cost Share Charges^Program Income^Total Commitments^Direct Commitments^Indirect Commitments^Cost Share Commitments^'
|| 'Total Invoiced^Total Revenue^Total Funding^'
|| 'Total Budget^Direct Budget^Indirect Budget^Cost Share Budget',
2
FROM DUAL
UNION
SELECT DISTINCT a.award_number
|| '^'
|| p.segment1
|| '^'
...
..
.
.
Looking at it, you should replace the UNION statements at the top of the query with UNION ALL statements - there's no possibility of duplicate lines there.
|
|
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384636 is a reply to message #384616] |
Wed, 04 February 2009 10:21 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Anne Simms wrote on Wed, 04 February 2009 15:42 | That sounds right as I begin to look at it more! However, what about the 0's below? How can I get rid of those?
|
Why do you want to get rid of them?
- because they are 0's - then follow cookiemonster's advice
- because they have the same (Award#, Project#, Task#) as another row - then it is a little harder
Firstly, you shall understand, what is the relationship between those tables (inner selects) and what this query is supposed to do. From a brief glance, you did not post it whole, as you reference rows from A, however it is not present in the FROM clause.
Anyway, you should know, from which inner SELECT do columns "Total Exp." and "Dir. Charges" come from. My bet is, that it is the inner subquery aliased with B. If you have a look at it, you see, that you GROUP BY four columns (AWARD_ID, PROJECT_ID, TASK_ID, BUDGET_VERSION_ID), however you join only two of them (PROJECT_ID, TASK_ID). The other two ones (AWARD_ID, BUDGET_VERSION_ID) seem to be superfluous there, as they are not used anywhere in the outer query. They may lead to multiplication of rows with the same (PROJECT_ID, TASK_ID).
So, you have two possibile ways here:
- join these superfluous columns with another columns from T table (as you already were suggested), or
- get rid of them from the B subquery
Again, it depend on understanding, what the underlying tables represent and what is this query supposed to return.
However, this may be totally wrong. There may also be another missing join conditions or extra grouping columns, which multiply its resultset. But, as this query is very complex, you shall be able to break it to pieces, understand each its part and manage to join them together. I am afraid, that this will do nobody here instead of you, as it needs debugging with real data and its perfect knowledge. Maybe a time to hire a consultant...
[Edit: FROM instead of WHERE in the second paragraph]
[Updated on: Wed, 04 February 2009 10:22] Report message to a moderator |
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384677 is a reply to message #384636] |
Wed, 04 February 2009 15:12 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
Hi Flyboy,
I see part of the culprit below:
SELECT gbca.award_id, gra.project_id, gra.task_id,
gra.budget_version_id,
SUM (bl.burdened_cost) budget_total,
SUM
(DECODE (SUBSTR (r.NAME, 1, 10),
'Award Reve', NVL (bl.burdened_cost, 0),
'F & A Cost', NVL (bl.burdened_cost, 0),
0
)
) indirect_budget,
SUM (DECODE (SUBSTR (r.NAME, 1, 10),
'Award Reve', 0,
'F & A Cost', 0,
'Cost Share', 0,
NVL (bl.burdened_cost, 0)
)
) direct_budget,
SUM (DECODE (SUBSTR (r.NAME, 1, 10),
'Cost Share', NVL (bl.burdened_cost, 0),
0
)
) cs_budget
FROM apps.gms_resource_assignments gra,
apps.gms_budget_lines bl,
apps.pa_resource_list_members rlm,
apps.pa_resources r,
apps.gms_budgetary_control_award_v gbca
WHERE rlm.resource_id = r.resource_id
AND gra.resource_list_member_id =
rlm.resource_list_member_id
AND bl.resource_assignment_id =
gra.resource_assignment_id
AND gbca.project_id = gra.project_id
AND gbca.resource_list_id = rlm.resource_list_id
AND gra.budget_version_id IN (
SELECT MAX (bv.budget_version_id)
FROM gms.gms_budget_versions bv
WHERE bv.project_id =
gra.project_id)
GROUP BY gbca.award_id,
gra.project_id,
gra.task_id,
gra.budget_version_id) b,
As stated before no join for the award_id. This is going to be a challenge because none of the other tables in that query, have an award_id column. Tables below only have the following in common.
apps.gms_resource_assignments gra, -- resource_assignment_id, budget_version_id, project_id, task_id
apps.gms_budget_lines bl --resource_assignment_id, burdened_cost
apps.pa_resource_list_members rlm, --resource_list_member_id, resource_list_id, resource_id
apps.pa_resources r, --resource_id
apps.gms_budgetary_control_award_v gbca --project_id, award_id, award_number, resource_list_id
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384679 is a reply to message #384636] |
Wed, 04 February 2009 16:12 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
Also when I run the single query for part (b), it does not show any type of duplicates or 0 columns. It shows the following:
AWARD_ID,PROJECT_ID,TASK_ID,BUDGET_VERSION_ID,BUDGET_TOTAL,INDIRECT_BUDGET,DIRECT_BUDGET,CS_BUDGET
2573,268,268,81102,10378,6904.15,14109.03,-10635.18
The award_id = 2573, which is for award 532437C15 and no data exist for 532437C14.
Anne
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384683 is a reply to message #384636] |
Wed, 04 February 2009 20:24 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| flyboy wrote on Wed, 04 February 2009 17:21 | Anyway, you should know, from which inner SELECT do columns "Total Exp." and "Dir. Charges" come from. My bet is, that it is the inner subquery aliased with B.
|
Just curious, whether this assumption was correct, as you neither confirmed nor disproved that. I would say no,as the resulting figures differ ((-257.18, 3473.85) vs. (10378, 6904.15, 14109.03, -10635.18)). Anyway, even if it is ok today, this may be problem in future. By the way, I suggested two ways how to adjust it; additional join condition was only one of them.
| flyboy wrote on Wed, 04 February 2009 17:21 | There may also be another missing join conditions or extra grouping columns, which multiply its resultset. But, as this query is very complex, you shall be able to break it to pieces, understand each its part and manage to join them together.
|
It was already suggested before me, but I seeit still as the only way. Start with the query returning those figures and add/remove tables/subqueries to see which of them multiplies data (probably because of missing join condition - also this was stated by Raj and coleing).
| flyboy wrote on Wed, 04 February 2009 17:21 | From a brief glance, you did not post it whole, as you reference rows from A, however it is not present in the FROM clause.
|
Just curious, whether I am blind, but I still think that the query you posted is incomplete. Anyway, posting it here would probably not help - it is too complex to debug it just from a look at it without knowing all the underlying information.
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384780 is a reply to message #384391] |
Thu, 05 February 2009 03:10 |
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Presumably gms_budgetary_control_award_v is a view that joins gms_awards_all to other tables. Have a look how it does it.
There has to be a join you can use after all or there'd be no point selecting award_id in the query.
Flyboy - A is in the original, this bit shortly above the final where clause:
(SELECT pf.project_id, pf.task_id,
SUM (pf.funding_amount) funding
FROM apps.gms_project_fundings pf
GROUP BY pf.project_id, pf.task_id) f,
apps.gms_awards_all a,
apps.pa_projects_all p,
Took me a while to find.
On which note, Anne - might I recommend slightly longer table aliases. 'a' might be quick to type but is a pain to find.
|
|
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384857 is a reply to message #384780] |
Thu, 05 February 2009 08:09 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
Hi Cookiemonster,
I did see this early on, but when added the correct table to this query: gms_project_fundings_v and then added award_id it added additional lines to the output and then I ended up having 8 lines with dupes: 4 lines like
xxcc 8988
xxcc 0000
xxcc 8988
xxcc 0000
xxpp 8988
xxpp 0000
xxpp 8988
xxcc 0000
I don't know why that was...so I'm just going to piece this entire thing line by line. Thanks for all your suggestions.
Anne
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #385189 is a reply to message #384780] |
Sat, 07 February 2009 02:06 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| cookiemonster wrote on Thu, 05 February 2009 10:10 | Flyboy - A is in the original, this bit shortly above the final where clause:
|
Thank you. In fact, I was searching for "a," (as I do not know, how to search whole words in Firefox), but I stopped too early as I supposed the wrong WHERE clause to be the main one (the indenting is a little tricky).
| Anne Simms wrote on Thu, 05 February 2009 15:09 | I did see this early on, but when added the correct table to this query: gms_project_fundings_v and then added award_id it added additional lines to the output and then I ended up having 8 lines with dupes:
xxcc 8988
xxcc 0000
xxcc 8988
xxcc 0000
xxpp 8988
xxpp 0000
xxpp 8988
xxcc 0000
I don't know why that was...
|
It was already said here multiple times; but I will summarize it here for the last time:
- the lines you posted are not duplicate, as they differ in at least one column value
- if you feel that the rows are multiplicated, then you forgot some JOIN condition between those tables
By the way, using DISTINCT may return "correct" result, but it is unnecessarily slowing down the query (as those multiple rows in resultset have to be processed). So you should use it only in case when source data contain duplicates and you do not want to show them.
|
|
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #385717 is a reply to message #385706] |
Tue, 10 February 2009 14:46 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Anne Simms wrote on Tue, 10 February 2009 20:10 | How can I limit this in the where clause of the report?
Any suggestions?
|
What about a.award_short_name != 'Delete - Change to 532569K2'
I am afraid that this is not what you want. But, without defining, what "limit" (column?) exactly means in resultset (as it probably contains more columns) and what are the rules for doing it exactly the way you want.
WHERE clause filters out entire row. Which (regarding values in other columns) shall be picked out and which shall be left in the resultset? We may help you with the code, but it is your part to specify what is the exact logic behind it.
I have a strange feeling that this is still related with that "duplicate" issue. OK, I will try it again.
You know input data. You should know the rules how to transform these data into the resultset. So your task is to convert them into SQL statement. Again try to answer JRowbottom's question in this thread (message #384504). You just repeated what you want. You did not put there the most important part - the logic. Why should be the resultset the one you posted and not something else? Only you should know this. Nobody is able to write out a correct query without specifying it.
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #386315 is a reply to message #385717] |
Fri, 13 February 2009 10:07 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
Yes, you are right it basically goes back to this issue:
Award# Project # Task # Total Exp. Dir. Charges
532437C14 532437 U11 -257.18 3473.85
532437C14 532437 U11 0 0
532437C15 532437 U11 -257.18 3473.85
532437C15 532437 U11 0 0
I didn't include all the columns but I was thinking maybe i could limit the extra rows by putting in something like:
a.award_short_name != 'Delete - Change to 532569K2'
However, this will not fix my problem as you have stated.
I have found the culprit inline view that is causing the above issues. See below:
(SELECT gmsa.award_id,
gmsac.project_id,
gmsac.task_id,
SUM(DECODE (SUBSTR (gmsac.expenditure_type, 1, 3),
'IDC', 0,
NVL (GMSAC.BURDENED_COST, 0)))
+ SUM(DECODE (SUBSTR (et.expenditure_category, 1, 10),
'F & A Cost', NVL (GMSAC.BURDENED_COST, 0),
0))
+ SUM(DECODE (SUBSTR (et.expenditure_category, 1, 20),
'Cost Share', NVL (GMSAC.BURDENED_COST, 0),
'Cost Share-Direct', NVL (GMSAC.BURDENED_COST, 0),
'Cost Share-Indirect', NVL (GMSAC.BURDENED_COST, 0),
'Research Enhancement', NVL (GMSAC.BURDENED_COST, 0),
0))
+ SUM(DECODE (SUBSTR (et.expenditure_category, 1, 16),
'Program Income', NVL (GMSAC.BURDENED_COST, 0),
'Program - Income', NVL (GMSAC.BURDENED_COST, 0),
0))
TOTAL_EXP,
SUM(Decode(substr(gmsac.expenditure_type,1,3), 'IDC', 0, NVL(GMSAC.BURDENED_COST,0))) DIR_EXP,
SUM(DECODE(SUBSTR(et.expenditure_category,1,10),'F & A Cost',NVL(GMSAC.BURDENED_COST,0), 0)) IND_EXP,
SUM(DECODE(SUBSTR(et.expenditure_category, 1, 20),
'Cost Share', NVL(GMSAC.BURDENED_COST, 0),
'Cost Share-Direct', NVL(GMSAC.BURDENED_COST, 0),
'Cost Share-Indirect', NVL(GMSAC.BURDENED_COST, 0),
'Research Enhancement', NVL(GMSAC.BURDENED_COST, 0),0)) CS_EXP,
SUM(DECODE(SUBSTR(et.expenditure_category,1,16),'Program Income',NVL(GMSAC.BURDENED_COST,0),'Program - Income',NVL(GMSAC.BURDENED_COST, 0),0)) PROGRAM_INCOME,
SUM(NVL(gmsac.BILLED_AMOUNT,0)) billed,
SUM(NVL(gmsac.REVENUE_AMOUNT,0)) rev
FROM pams.pams_gms_status_actuals gmsac,
apps.pa_expenditure_types et,
apps.gms_awards_all gmsa,
apps.gl_code_combinations gcc,
apps.PA_COST_DISTRIBUTION_LINES_ALL cdl
WHERE
cdl.EXPENDITURE_ITEM_ID = gmsac.expenditure_item_id --removed left outer join
and gmsac.award_id = gmsa.AWARD_ID
and gmsac.pa_date BETWEEN to_date(substr('##1',1,10),'YYYY/MM/DD') AND to_date(substr('##2',1,10),'YYYY/MM/DD') --removed left outer join
and gcc.CODE_COMBINATION_ID = cdl.dr_CODE_COMBINATION_ID
and cdl.line_num ='1'
and gcc.segment3 between nvl('##7',gcc.segment3) and nvl('##8',gcc.segment3)
and et.EXPENDITURE_TYPE = gmsac.expenditure_type --removed left outer join
group by gmsa.award_id,gmsac.project_id,
gmsac.task_id) e,
So I am still trying to figure out what additional condition needs to be added.
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #386349 is a reply to message #386315] |
Fri, 13 February 2009 21:14 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Anne Simms wrote on Fri, 13 February 2009 17:07 | I have found the culprit inline view that is causing the above issues. See below:
|
I see a large query on 5 tables containing columns corresponding to the resultset above.
If I would assume, that this is output of that standalone query, it is strange as the columns in GROUP BY clause (AWARD_ID, PROJECT_ID, TASK_ID) shall be unique in resultset. However, they may just look so; they may differ in e.g. trailing whitespaces.
What are datatypes of that columns?
What is their real content (e.g. LENGTH for VARCHAR2s or DUMP for all types)?
What happens when you remove all tables (within their columns) except GMSA and GMSAC?
What happens after re-adding the removed tables?
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #387262 is a reply to message #386349] |
Wed, 18 February 2009 10:15 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
Hi correction,
The output for the standalone query is like the following (*NOTE COULDN'T FIT ALL THE COLUMNS IN MY EXAMPLE BELOW):
AWARD_ID PROJECT_ID TASK_ID TOTAL_EXP DIR_EXP IND_EXP CS_EXP
21 41 41 1035.61 1035.61 0 0
22 41 41 0 0 0 0
As you will notice the awards that share the same project will show a 0 for the remaining lines, which is correct. That is the output from the one inline view that I identified as the culprit.
The reason I identified it as the culprit was because I blocked every inline view separately from this huge report to see what the final output would be for the entire report and this was the inline view that showed the lines as such; what I indicated previously.
To your questions:
1. What are datatypes of that columns?
2. What is their real content (e.g. LENGTH for VARCHAR2s or DUMP for all types)?
3. What happens when you remove all tables (within their columns) except GMSA and GMSAC?
4. What happens after re-adding the removed tables?
Answer:
1. award_id=datatype of number (15) can be null, project_id =datatype of number(15) can be null, and task id=data type of number (15) can be null.
2. I answered in number 1.
3. When I remove all the tables, except gmsa and gmsac, I still get the same type of output as I indicated before just a little different. See below:
Award# Project # Task # Total Exp. Dir. Charges
532437C14 532437 U11 0 0
532437C14 532437 U11 -257.18 3473.85
532437C15 532437 U11 0 0
532437C15 532437 U11 -257.18 3473.85
4. When I re-join all the tables I removed it goes back to the following below:
Award# Project # Task # Total Exp. Dir. Charges
532437C14 532437 U11 -257.18 3473.85
532437C14 532437 U11 0 0
532437C15 532437 U11 -257.18 3473.85
532437C15 532437 U11 0 0
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #387265 is a reply to message #384391] |
Wed, 18 February 2009 10:42 |
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
We seem to be getting confused here.
You appear to be mixing and matching data.
This:
AWARD_ID PROJECT_ID TASK_ID TOTAL_EXP DIR_EXP IND_EXP CS_EXP
21 41 41 1035.61 1035.61 0 0
22 41 41 0 0 0 0
Is presumably not from the same set of columns as:
Award# Project # Task # Total Exp. Dir. Charges
532437C14 532437 U11 0 0
532437C14 532437 U11 -257.18 3473.85
532437C15 532437 U11 0 0
532437C15 532437 U11 -257.18 3473.85
I assumed that Award# = Award_id and task# = task_id, but if the id's are numbers that can't be the case as both award# and task# are showing character data.
You need to explain how you got from one to the other.
More generally you need to streamline this query down to the smallest possible version that still replicates the problem.
By the sound of it you should only need to select the 5 columns above and it should need to reference only GMSA and GMSAC.
Do that, post the query here along with definitions of the GMSA and GMSAC tables and we should be able to resolve this.
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #387293 is a reply to message #387265] |
Wed, 18 February 2009 14:57 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
All I am doing is taking a portion of the huge query and narrowing it down to the culprit inline view (as I showed before). The main issue is when I run the entire report the output looks like the following (see below):
Award# Project # Task # Total Exp. Dir. Charges
532437C14 532437 U11 -257.18 3473.85
532437C14 532437 U11 0 0
532437C15 532437 U11 -257.18 3473.85
532437C15 532437 U11 0 0
Remember the other inline views are creating some of these columns above. I discovered inline view (e) is the culprit of my problem as shown above.
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #387297 is a reply to message #387265] |
Wed, 18 February 2009 15:32 |
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
|
Senior Member |
|
|
I narrowed the query down using only GMSAC and GMSA tables:
(SELECT gmsa.award_id,
gmsac.project_id,
gmsac.task_id,
SUM(DECODE (SUBSTR (gmsac.expenditure_type, 1, 3),
'IDC', 0,
NVL (GMSAC.BURDENED_COST, 0))) TOTAL_EXP,
SUM(Decode(substr(gmsac.expenditure_type,1,3), 'IDC', 0, NVL(GMSAC.BURDENED_COST,0))) DIR_EXP,
SUM(NVL(gmsac.BILLED_AMOUNT,0)) billed,
SUM(NVL(gmsac.REVENUE_AMOUNT,0)) rev
FROM tams.tams_gms_status_actuals gmsac,
apps.gms_awards_all gmsa
WHERE gmsac.award_id = gmsa.AWARD_ID
group by gmsa.award_id,gmsac.project_id,
gmsac.task_id) e,
See uploaded definitions of both tables.
|
|
|
|
|
|
|
|
|
|
| Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #387321 is a reply to message #384391] |
Wed, 18 February 2009 17:54 |
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You seem to have completely bypassed the first part of my post.
You are still mixing and matching - the outputs you have provided CANNOT have come from the inline view (e) you posted before (post #387297).
I know this because acording to the table definitions you have posted all the columns selected by that query are numeric and yet your outputs contain character data.
So I ask again, how do you get from that query to this output:
Award# Project # Task # Total Exp. Dir. Charges
532437C14 532437 U11 -257.18 3473.85
532437C14 532437 U11 0 0
532437C15 532437 U11 -257.18 3473.85
532437C15 532437 U11 0 0
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Dec 07 04:37:25 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
