Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01790
ORA-01790 [message #273541] Wed, 10 October 2007 15:23 Go to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I created this sql plus report that has headers. I'm running this sql*plus report through Oracle Apps for a report. I get the following error message below from the output and log file of the report:

SELECT 2, '0', '0', '0', 0,
*
ERROR at line 10:
ORA-01790: expression must have same datatype as corresponding expression

Here is part of the code for the report. I don't understand why I am getting the following error message above.

/* Expenditure Report
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
COL rec_type noprint
COL proj_num noprint
COL task_id  noprint
COL award_id noprint
COL prog_rec_type noprint

-- Get period start and end dates

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 s.period_name(+) = '##1' AND e.period_name(+) = '##2';

SET termout on

-- Print Page Header
SELECT 0 rec_type, '0' proj_num, '0' task_id, '0' award_id, 0 prog_rec_type,
       'TAMS Project Expenditures for Dates ##s_date - ##e_date, Project Organizations ##3 - ##4, Projects ##5 - ##6 and Accounts ##7 - ##8'
  FROM DUAL
UNION
-- Display error message if problems selecting from pa_periods table
SELECT 1, '0', '0', '0', 0, '##err_message'
  FROM DUAL
UNION
-- Print column headers
SELECT 2, '0', '0', '0', 0,
          'Project 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'
  FROM DUAL
Re: ORA-01790 [message #273588 is a reply to message #273541] Thu, 11 October 2007 01:04 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Not exactly sure what you're trying to accomplish, but I do know that task_id is a number in Apps, so that might cause the error message (I suggest you check the other formats too).
Re: ORA-01790 [message #273589 is a reply to message #273541] Thu, 11 October 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT 0 rec_type, '0' proj_num, '0' task_id, '0' award_id, 0 prog_rec_type,
  2         'TAMS Project Expenditures for Dates ##s_date - ##e_date, Project Organizations ##3 - ##4, Projects ##5 - ##6 
unts ##7 - ##8'
  3    FROM DUAL
  4  UNION
  5  -- Display error message if problems selecting from pa_periods table
  6  SELECT 1, '0', '0', '0', 0, '##err_message'
  7    FROM DUAL
  8  UNION
  9  -- Print column headers
 10  SELECT 2, '0', '0', '0', 0,
 11            'Project Number^Project Start Date^Project End Date^Status^Project Manager^Responsible Accountant^Task Numb
 12         || 'Task Start Date^Task Completion Date^Task Manager^Award Number^Award Short Name^Project Organization^'
 13         || 'Task Organization^Expense Code^OMB Code^Revenue Line^Burden Rate^Burden Structure^Site^Sponsor^Type^Custom
 14         || 'Award Type^Award Purpose^Federal Flow Thru Code^IDC Schedule Name^Total Expenditure^Direct Charges^'
 15         || 'Indirect Charges^Cost Share Charges^Total Commitments^Direct Commitments^Indirect Commitments^Cost Share C
ts^'
 16         || 'Total Invoiced^Total Revenue^Total Funding^'
 17         || 'Total Budget^Direct Budget^Indirect Budget^Cost Share Budget'
 18    FROM DUAL
 19  .
SQL> SELECT 0 rec_type, '0' proj_num, '0' task_id, '0' award_id, 0 prog_rec_type,
  2         'TAMS Project Expenditures for Dates ##s_date - ##e_date, Project Organizations ##3 - ##4, Projects ##5 - ##6 
unts ##7 - ##8'
  3    FROM DUAL
  4  UNION
  5  -- Display error message if problems selecting from pa_periods table
  6  SELECT 1, '0', '0', '0', 0, '##err_message'
  7    FROM DUAL
  8  UNION
  9  -- Print column headers
 10  SELECT 2, '0', '0', '0', 0,
 11            'Project Number^Project Start Date^Project End Date^Status^Project Manager^Responsible Accountant^Task Numb
 12         || 'Task Start Date^Task Completion Date^Task Manager^Award Number^Award Short Name^Project Organization^'
 13         || 'Task Organization^Expense Code^OMB Code^Revenue Line^Burden Rate^Burden Structure^Site^Sponsor^Type^Custom
 14         || 'Award Type^Award Purpose^Federal Flow Thru Code^IDC Schedule Name^Total Expenditure^Direct Charges^'
 15         || 'Indirect Charges^Cost Share Charges^Total Commitments^Direct Commitments^Indirect Commitments^Cost Share C
ts^'
 16         || 'Total Invoiced^Total Revenue^Total Funding^'
 17         || 'Total Budget^Direct Budget^Indirect Budget^Cost Share Budget'
 18    FROM DUAL
 19  /
  REC_TYPE P T A PROG_REC_TYPE
---------- - - - -------------
'TAMSPROJECTEXPENDITURESFORDATES##S_DATE-##E_DATE,PROJECTORGANIZATIONS##3-##4,PROJECTS##5-##6ANDACCOUNTS##7-##8'
------------------------------------------------------------------------------------------------------------------------
         0 0 0 0             0
TAMS Project Expenditures for Dates ##s_date - ##e_date, Project Organizations ##3 - ##4, Projects ##5 - ##6 and Account
s ##7 - ##8
         1 0 0 0             0
##err_message
         2 0 0 0             0
Project 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 Thr
u Code^IDC Schedule Name^Total Expenditure^Direct Charges^Indirect Charges^Cost Share Charges^Total Commitments^Direct C
ommitments^Indirect Commitments^Cost Share Commitments^Total Invoiced^Total Revenue^Total Funding^Total Budget^Direct Bu
dget^Indirect Budget^Cost Share Budget

3 rows selected.

I don't think this is the query that raised the error.

By the way, always mention your Oracle version (with 4 decimals).

And also, use UNION ALL and a final ORDER BY.

Regards
Michel

[Updated on: Thu, 11 October 2007 01:05]

Report message to a moderator

Re: ORA-01790 [message #273713 is a reply to message #273589] Thu, 11 October 2007 08:24 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
We are on oracle database version 9.2.0.4 and apps version 11.5.8. The final order by is used in the very last query of this report. This report has about 6 Unions. I don't want to use the union all becasue I don't want to return every row or duplicates.
Re: ORA-01790 [message #273714 is a reply to message #273713] Thu, 11 October 2007 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, I replied following what you posted, I can't reply on what you didn't post.
From what you posted, there is no problem.

Regards
Michel
Re: ORA-01790 [message #273733 is a reply to message #273714] Thu, 11 October 2007 09:22 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Here is the log file of the error message:

PAMSPAXP module: PAMS Project Expenditures Report
+---------------------------------------------------------------------------+

Current system time is 10-OCT-2007 15:03:57

+---------------------------------------------------------------------------+


+-----------------------------
| Starting concurrent program execution...
+-----------------------------

Arguments
------------
MAY-07
MAY-07






------------
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
SELECT 2, '0', '0', '0', 0,
*
ERROR at line 10:
ORA-01790: expression must have same datatype as corresponding expression

Re: ORA-01790 [message #273735 is a reply to message #273733] Thu, 11 October 2007 09:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If, as you (the OP) said, the query has 6 union clauses in it, and the query you posted here has 3 union clauses, and the query you posted here seems to work (as far as we can test it), the obvious conclusion you should draw is that the problem is in the last 3 union clauses.

How about you post them here, and we'll see if we can spot the problem....
Re: ORA-01790 [message #273749 is a reply to message #273735] Thu, 11 October 2007 10:07 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
OK, you asked for it..here is the large Union report. Notice in the report I have the summary details and then the line details to follow. I have not added the additional columns in the column headers of the report yet for the line details after the summary portion of the report.


/*  Project Expenditures Report 
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
COL rec_type noprint
COL proj_num noprint
COL task_id  noprint
COL award_id noprint
COL prog_rec_type noprint

-- Get period start and end dates

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 s.period_name(+) = '##1' AND e.period_name(+) = '##2';

SET termout on

-- Print Page Header
SELECT 0 rec_type, '0' proj_num, '0' task_id, '0' award_id, 0 prog_rec_type,
       'PAMS Project Expenditures for Dates ##s_date - ##e_date, Project Organizations ##3 - ##4, Projects ##5 - ##6 and Accounts ##7 - ##8'
  FROM DUAL
UNION
-- Display error message if problems selecting from pa_periods table
SELECT 1 rec_type, '0' proj_num, '0'task_id, '0'award_id, 0 prog_rec_type, '##err_message'
  FROM DUAL
UNION
-- Print column headers
SELECT 2 rec_type, '0'proj_num, '0'task_id, '0'award_id, 0 prog_rec_type,
          'Project 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'
  FROM DUAL
UNION
-- Print project summary lines
SELECT DISTINCT 3, p.segment1, t.task_id, a.award_id, 0,
                   p.segment1
                || '^'
                || 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
           FROM (SELECT   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
                    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 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 gra.project_id, gra.task_id, gra.budget_version_id) b,
                (SELECT   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
                 GROUP BY pct.project_id, pct.task_id) ct,
                (SELECT   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 tams.tams_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 '##s_date'  AND '##e_date'                                                
                      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.project_id, gmsac.task_id) e,
                (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.pa_projects_all p,
                apps.pa_tasks t,
                apps.gms_awards_all a,
                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
UNION
-- Print commitment detail lines
SELECT DISTINCT 3, pa.segment1, pct.task_id, a.award_id, 1,
                   pa.segment1||'^'|| pct.project_id
                || '^'
                || pct.task_id
                || '^'
                ||
                   -- total commitments
                   pct.tot_cmt_burdened_cost
                || '^'
                ||
                   -- direct commitments
                   DECODE (pct.expenditure_category,
                           'F & A Costs', 0,
                           'Cost Share', 0,
                           NVL (pct.tot_cmt_burdened_cost, 0)
                          )
                || '^'
                ||
                   -- indirect commitments
                   DECODE (pct.expenditure_category,
                           'F & A Costs', NVL (pct.tot_cmt_burdened_cost, 0),
                           0
                          )
                || '^'
                ||
                   -- cost share commitments
                   DECODE (pct.expenditure_category,
                           'Cost Share', NVL (pct.tot_cmt_burdened_cost, 0),
                           0
                          )
           FROM apps.pa_commitment_txns pct,
                apps.pa_projects_all pa,
                apps.gms_awards_all a
         WHERE pa.project_type = 'Sponsored Program'
            AND pa.segment1 BETWEEN '##5'  AND '##6' --'540001' AND '540034'
            AND pa.project_id = pct.project_id
            AND pa.project_id = a.award_project_id(+)        
UNION
-- Print Budget detail lines
SELECT DISTINCT 3,
                pa.segment1,
                gra.task_id,
                a.award_id,
                2,
                gra.task_id||'^'||
                gra.budget_version_id||'^'||bl.burdened_cost||'^'|| --budget_total
                DECODE (SUBSTR (r.NAME, 1, 10),
                        'Award Reve', NVL (bl.burdened_cost, 0),
                        'F & A Cost', NVL (bl.burdened_cost, 0),
                        0
                       )||'^'|| --indirect_budget,
                DECODE (SUBSTR (r.NAME, 1, 10),
                        'Award Reve', 0,
                        'F & A Cost', 0,
                        'Cost Share', 0,
                        NVL (bl.burdened_cost, 0)
                       )||'^'|| --direct_budget,
                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.pa_projects_all pa,
                apps.gms_awards_all a
          WHERE pa.project_id = a.award_project_id(+)
            AND pa.project_id = gra.project_id(+)
            AND gra.resource_assignment_id = bl.resource_assignment_id
            AND gra.resource_list_member_id = rlm.resource_list_member_id
            AND rlm.resource_id = r.resource_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)
UNION
-- Print Expenditure information,billed and revenue amounts
SELECT DISTINCT 
       3, 
       pa.segment1,
       gmsac.task_id,
       a.award_id,
       3,
       gmsac.project_id||'^'||
       gmsac.task_id||'^'||
       cdl.project_burdened_cost||'^'|| --total_exp
       DECODE (SUBSTR (et.expenditure_category, 1, 10),
               'F & A Cost', 0,
               'Cost Share', 0,
               NVL (gmsac.burdened_cost, 0)
              )||'^'|| --dir_exp,
       DECODE (SUBSTR (et.expenditure_category, 1, 10),
               'F & A Cost', NVL (cdl.project_burdened_cost, 0),
               0
              )||'^'||-- ind_exp,
       DECODE (SUBSTR (et.expenditure_category, 1, 10),
               'Cost Share', NVL (cdl.project_burdened_cost, 0),
               0
              )||'^'|| --cs_exp,
       NVL (gmsac.billed_amount, 0)||'^'|| -- billed,
       NVL (gmsac.revenue_amount, 0) --rev
  FROM tams.tams_gms_status_actuals gmsac,
       apps.gl_code_combinations gcc,
       apps.pa_cost_distribution_lines_all cdl,
       apps.pa_expenditure_types et,
       apps.pa_projects_all pa,
       apps.gms_awards_all a
 WHERE pa.project_id = a.award_project_id(+)
   AND pa.project_id = gmsac.project_id (+)
   AND cdl.expenditure_item_id(+) = gmsac.expenditure_item_id
   AND cdl.pa_date(+) BETWEEN '##s_date'  AND '##e_date'    
   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
UNION
--Funding Information   
SELECT DISTINCT 3,
                pa.segment1,
                pf.task_id,
                a.award_id,
                4,
                pf.project_id||'^'|| --project
                pf.task_id||'^'|| --task
                pf.funding_amount -- funding
           FROM apps.gms_project_fundings pf, apps.pa_projects_all pa,
           apps.gms_awards_all a
           WHERE pa.project_id = pf.project_id (+)
           AND pa.project_id = a.award_project_id(+)
           order by 1,2,3;
Re: ORA-01790 [message #273750 is a reply to message #273749] Thu, 11 October 2007 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check all datatypes, something we can't do as we don't have your knowledge on the tables.

Regards
Michel
Re: ORA-01790 [message #273780 is a reply to message #273749] Thu, 11 October 2007 12:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
californiagirl wrote on Thu, 11 October 2007 11:07


AND cdl.pa_date(+) BETWEEN '##s_date' AND '##e_date'
[/code]


For the last time, would you please learn some SQL and how to compare DATEs the proper way? Look at previous responses to your posts.
Re: ORA-01790 [message #273796 is a reply to message #273780] Thu, 11 October 2007 13:02 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Correction, I know how to compare dates the proper way in SQL, I'm just modifying someone else's jacked up coding style...Thank you very much!!!
Re: ORA-01790 [message #273879 is a reply to message #273749] Fri, 12 October 2007 01:37 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So, what are the datatypes of the following columns:

p.segment1, t.task_id, a.award_id from the 'Print Project Summary lines' query

pa.segment1, pct.task_id, a.award_id from the 'Print commitment detail lines' query, and

pa.segment1, pf.task_id, a.award_id, from the 'Funding Information' query

I reckon that one or more of those will be a number.
If so, your options are
1) Wrap the offending columns in a to_char
2) If all of them are numbers, then change the first 3 parts of the query to return 0 rather than '0' for proj_num, task_id and award_id
Previous Topic: Timestamp
Next Topic: Diffrence in number records while using subquery
Goto Forum:
  


Current Time: Sun Dec 04 02:41:02 CST 2016

Total time taken to generate the page: 0.06001 seconds