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 Go to next message
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 Go to previous messageGo to next message
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 #384396 is a reply to message #384391] Tue, 03 February 2009 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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.

Cartesian join?
But without any query nor information it is hard to say.

Regards
Michel
Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384407 is a reply to message #384391] Tue, 03 February 2009 12:34 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Wrap the query and select a distinct on it.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #384504 is a reply to message #384391] Wed, 04 February 2009 02:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
xxxx 8989
xxxx 0000
xxcc 8989
xxcc 0000

It should be just:

xxxx 0000
xxcc 8989


How do you pick which of the rows you want to keep?
Why isn't the required output
xxxx 8989
xxcc 0000

or
xxxx 8989
xxxx 0000

or
xxcc 8989
xxcc 0000
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 Go to previous messageGo to next message
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 #384606 is a reply to message #384430] Wed, 04 February 2009 07:24 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Hi,

I will try Union All, but I thought Union All would give me everything even duplicats, when that is what I am trying to avoid?
Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384607 is a reply to message #384391] Wed, 04 February 2009 07:29 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Oracle only deems two rows as being duplicates if every column has the same value.
Your sample output suggests this not the problem. So UNION ALL will make no difference
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 Go to previous messageGo to next message
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 #384613 is a reply to message #384391] Wed, 04 February 2009 08:21 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Those rows ARE distinct.
They would only be considered as duplicates if you removed Award#.
As stated earlier you are presumably missing a join from gms_awards_all (a) to one or more of the other tables.
Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384616 is a reply to message #384613] Wed, 04 February 2009 08:42 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
cookiemonster,

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?

532437C15	532437	U11	0	         0

532437C14	532437	U11	0	         0



Anne
Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384619 is a reply to message #384570] Wed, 04 February 2009 08:57 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Hi Raj,

There is no way to print an explain plan from Toad, at least that I know of. I tried to upload the screen shots of the explain plan, but it was too big. Unless you want me to send it to your email? Let me know.

Anne
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 Go to previous messageGo to next message
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 #384628 is a reply to message #384616] Wed, 04 February 2009 09:19 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Anne Simms wrote on Wed, 04 February 2009 14:42
cookiemonster,

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?

532437C15	532437	U11	0	         0

532437C14	532437	U11	0	         0



Anne


Seeing as those are summary columns the simplest way is a having clause.
ie
HAVING total_exp > 0
Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #384629 is a reply to message #384624] Wed, 04 February 2009 09:32 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Hi JRowBottom,

I'm not sure I follow you in terms of picking one of my queries? Give me an example of how I would run this with one of my queries in the report.

Anne
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 Go to previous messageGo to next message
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 type
EXPLAIN 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 #384633 is a reply to message #384630] Wed, 04 February 2009 09:59 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
See attached explain plan.

Thanks

Anne
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #384856 is a reply to message #384683] Thu, 05 February 2009 08:05 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Hi Flyboy,

Thanks for all your suggestions. I will continue to debug what you and everyone else has stated. Thank you.


Anne
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #385706 is a reply to message #385189] Tue, 10 February 2009 13:10 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
OK,

I have one last question related to this jigantor report. I want to limit on the award_short_name column that returns columns like the following:


Delete - Change to 532569K2
Delete - Change to 532569K2





How can I limit this in the where clause of the report? I imagine starting it with something like:


AND a.award_short_name 



Any suggestions?

Anne
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #387298 is a reply to message #387297] Wed, 18 February 2009 15:34 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
See attached second table defintion for gmsac.

Anne


Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #387300 is a reply to message #387298] Wed, 18 February 2009 15:40 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Also see output from current inline view (e).


Anne
  • Attachment: Output.txt
    (Size: 12.04KB, Downloaded 126 times)
Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #387302 is a reply to message #387300] Wed, 18 February 2009 15:52 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
See output again. This one has data.

Anne
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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


Re: Need suggestions on Debugging a huge Union query with inline views (merged) [message #387487 is a reply to message #387321] Thu, 19 February 2009 08:12 Go to previous messageGo to previous message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
See correct output uploaded. Don't know why I keep doing that .....LOL.


Anne
Previous Topic: ORA-01843: not a valid month
Next Topic: CLOB access ORA-06550
Goto Forum:
  


Current Time: Sun Dec 11 04:19:24 CST 2016

Total time taken to generate the page: 0.08409 seconds