Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00933 error message (sql plus 8.0)
ORA-00933 error message [message #377395] Mon, 22 December 2008 13:50 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Hi,

When I run the following sqlplus script below I get the following error message below. Any assistance to resolve this issue will help. I checked to make sure all the ) were in place, not sure I'm understanding the issue.

gmsac.task_id) e,
*
ERROR at line 25:
ORA-00933: SQL command not properly ended


Thanks
Anne




/* Formatted on 2008/12/19 14:48 (Formatter Plus v4.8.8) */
/*   
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

SELECT   'Expenditure report 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^Responsible Accountant^Task Number^'
         || 'Task Start Date^Task Completion Date^Task Manager^Award Number^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^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
                || '^'
                ||
                   -- t.task_number||'^'||
                   t.start_date
                || '^'
                || t.completion_date
                || '^'
                || tm.full_name
                || '^'
                ||
                   --a.AWARD_NUMBER||'^'||
                   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
                || '^'
                || 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
                    WHERE gbca.project_id = pct.project_id
                 GROUP BY gbca.award_id, pct.project_id, pct.task_id) ct,
                
--Breakdown for tot exp, dir exp, ind exp, cs exp, billed, rev
                (SELECT gmsac.award_id, gmsac.project_id,
		                             gmsac.task_id,
		                             SUM(cdl.PROJECT_BURDENED_COST) total_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(cdl.PROJECT_BURDENED_COST,0),0)) ind_exp,
		                             SUM(DECODE(SUBSTR(et.expenditure_category,1,10),'Cost Share',NVL(cdl.PROJECT_BURDENED_COST,0),0)) cs_exp,
    	                             SUM(NVL(gmsac.BILLED_AMOUNT,0)) billed,
   		                             SUM(NVL(gmsac.REVENUE_AMOUNT,0)) rev
                                FROM
hams.hams_gms_status_actuals  		 gmsac,
		                             apps.gl_code_combinations            gcc,
		                             apps.PA_COST_DISTRIBUTION_LINES_ALL  cdl,
		                             apps.pa_expenditure_types 			  et
                               WHERE cdl.EXPENDITURE_ITEM_ID (+) = gmsac.expenditure_item_id  	
                        --         and cdl.pa_date(+) BETWEEN to_date('##s_date','DD-MON-YYYY')  AND to_date('##e_date','DD-MON-YYYY')  --changed Nov 5, 2008
                        --         and cdl.pa_date(+) BETWEEN nvl(to_date('##s_date','DD-MON-YYYY'),to_date(substr('##1',1,10),'YYYY/MM/DD'))
                        --                                AND nvl(to_date('##e_date','DD-MON-YYYY'),to_date(substr('##2',1,10),'YYYY/MM/DD'))
                                 and cdl.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 gcc.segment3 between nvl('##7',gcc.segment3) and nvl('##8',gcc.segment3)
                                 and et.EXPENDITURE_TYPE (+) = gmsac.expenditure_type
                                group by gmsac.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,
                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
            AND NVL (pp2.end_date_active(+), SYSDATE) >= SYSDATE
            AND tm.person_id(+) = t.task_manager_person_id
            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 b.project_id(+) = t.project_id
            AND b.task_id(+) = t.task_id
            AND pf.project_id(+) = t.project_id
            AND pf.task_id(+) = t.task_id
            AND gi.installment_id(+) = pf.installment_id
            AND a.award_id(+) = gi.award_id
            AND ct.project_id(+) = t.project_id
            AND ct.task_id(+) = t.task_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
       ORDER BY 2;



Re: ORA-00933 error message [message #377398 is a reply to message #377395] Mon, 22 December 2008 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure we have a SQL syntax analyzer transplanted in our brain?

Remove all and add bit per bit until you find the error.
Or remove bit per bit until you no more have the error.

Regards
Michel
Re: ORA-00933 error message [message #377403 is a reply to message #377398] Mon, 22 December 2008 16:01 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Thanks I tried that and it still didn't work. Any other suggestions?

Anne
Re: ORA-00933 error message [message #377405 is a reply to message #377403] Mon, 22 December 2008 16:30 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I tried that and it still didn't work. Any other suggestions?



I don't think you really tried that.

If you really tried that, you would have at least posted a simplified query in a state where it is still running, and with a little bit added in a state where it isn't running any more.

Then we, and perhaps even you would see be able to see where the problem is.

To spot the problem now in this complex query is basically impossible. You have to break in down into smaller parts for troubleshooting.

Re: ORA-00933 error message [message #377431 is a reply to message #377403] Tue, 23 December 2008 00:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Anne Simms wrote on Mon, 22 December 2008 23:01
Thanks I tried that and it still didn't work. Any other suggestions?

Anne

Start by removing the empty lines
Re: ORA-00933 error message [message #377601 is a reply to message #377431] Tue, 23 December 2008 16:24 Go to previous message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Hi Frank, that worked thanks. I thought I did that before, but I guess I didn't.

Thanks again!

Anne
Previous Topic: DBMS_UTILITY.FORMAT_CALL_STACK length
Next Topic: WITH clause | How to use it?
Goto Forum:
  


Current Time: Sat Dec 03 01:35:01 CST 2016

Total time taken to generate the page: 0.08829 seconds