Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01790
ORA-01790 [message #273541] |
Wed, 10 October 2007 15:23 |
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 |
skooman
Messages: 913 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 |
|
Michel Cadot
Messages: 68711 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 |
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 #273733 is a reply to message #273714] |
Thu, 11 October 2007 09:22 |
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 |
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 |
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 #273780 is a reply to message #273749] |
Thu, 11 October 2007 12:15 |
joy_division
Messages: 4963 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 |
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 |
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
|
|
|
Goto Forum:
Current Time: Sun Nov 10 06:20:49 CST 2024
|