Foutmelding SQL script ORA-01722

[CeesKarsten]

Hallo,

Ik heb een probleem met een SQL script in een tijdschrijfprogramma. Als ik de sqlscript uitvoer op de hele database, ontstaat de foutmelding ORA-01722. Dit terwijl de het probleem niet ontstaat als ik de bewerking in stukken knip.
Onderstaand de script die ik probeer te draaien:

-- BUDGETREGELS DEBET

select distinct
o.tt_account as REKENING,
p.tt_code as PRODUCT,
o.tt_code as BUDGETHOUDER,
( sum(plc2.tt_costs*100) - sum(plc1.tt_costs*100) ) as BEDRAG,
' ' as BBV,
o.tt_name as OMSCHRIJVING

from
tt_plan pl1,
tt_plan pl2,
tt_plan_cells plc1,
tt_plan_cells plc2,
tt_org o,
tt_prj p

where
o.tt_fromdate <= pl1.tt_fromdate
and o.tt_todate >= pl1.tt_todate
and pl1.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl1.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl1.tt_status >= 2
and pl1.tt_type = 5
and pl1.tt_nr = 1
and plc1.tt_prj_id = p.tt_prj_id
and pl1.tt_plan_id = plc1.tt_plan_id
and pl1.tt_dim_id = o.tt_org_id
and o.tt_fromdate <= pl2.tt_fromdate
and o.tt_todate >= pl2.tt_todate
and pl2.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl2.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl2.tt_status >= 2
and pl2.tt_type = 5
and pl2.tt_nr = 2
and plc2.tt_prj_id = p.tt_prj_id
and pl2.tt_plan_id = plc2.tt_plan_id
and pl2.tt_dim_id = o.tt_org_id
and o.tt_org_id in [PLAN_ORG;<list>;"";"Organisatie";<select_organisation>;<all>;""]
and (plc1.tt_costs*100) - (plc2.tt_costs*100) <> 0
and p.tt_code is not null
and plc1.tt_emp_id = plc2.tt_emp_id

group by
o.tt_account,
p.tt_code,
o.tt_code,
o.tt_name

union all

-- BUDGETREGELS CREDIT SALARISKOSTEN

select distinct
'8813' as REKENING,
o.tt_externkey as PRODUCT,
o.tt_code as BUDGETHOUDER,
( ( sum(plc2.tt_costs*100) - sum(plc1.tt_costs*100) ) * -1 ) as BEDRAG,
' ' as BBV,
o.tt_name as OMSCHRIJVING

from
tt_plan pl1,
tt_plan pl2,
tt_plan_cells plc1,
tt_plan_cells plc2,
tt_org o,
tt_prj p

where
o.tt_fromdate <= pl1.tt_fromdate
and o.tt_todate >= pl1.tt_todate
and pl1.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl1.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl1.tt_status >= 2
and pl1.tt_type = 5
and pl1.tt_nr = 1
and plc1.tt_prj_id = p.tt_prj_id
and pl1.tt_plan_id = plc1.tt_plan_id
and pl1.tt_dim_id = o.tt_org_id
and o.tt_fromdate <= pl2.tt_fromdate
and o.tt_todate >= pl2.tt_todate
and pl2.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl2.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl2.tt_status >= 2
and pl2.tt_type = 5
and pl2.tt_nr = 2
and plc2.tt_prj_id = p.tt_prj_id
and pl2.tt_plan_id = plc2.tt_plan_id
and pl2.tt_dim_id = o.tt_org_id
and o.tt_org_id in [PLAN_ORG;<list>;"";"Organisatie";<select_organisation>;<all>;""]
and (plc1.tt_costs*100) - (plc2.tt_costs*100) <> 0
and p.tt_code is not null
and plc1.tt_emp_id = plc2.tt_emp_id

group by
o.tt_externkey,
o.tt_code,
o.tt_name

union all

-- BUDGETREGELS CREDIT WEL VERDELING INTERNE TAKEN

select distinct
rhit.rekening as REKENING,
rhit.product_code as PRODUCT,
rhit.budget as BUDGETHOUDER,
( ( sum(plc2.tt_costs*100) - sum(plc1.tt_costs*100) ) * ( (rhit.sleutel/10000) * -1 ) ) as BEDRAG,
' ' as BBV,
rhit.product_naam as OMSCHRIJVING

from
tt_plan pl1,
tt_plan pl2,
tt_plan_cells plc1,
tt_plan_cells plc2,
tt_org o,
tt_prj p,
rh_internetaken rhit

where
o.tt_fromdate <= pl1.tt_fromdate
and o.tt_todate >= pl1.tt_todate
and pl1.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl1.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl1.tt_status >= 2
and pl1.tt_type = 5
and pl1.tt_nr = 1
and plc1.tt_prj_id = p.tt_prj_id
and pl1.tt_plan_id = plc1.tt_plan_id
and pl1.tt_dim_id = o.tt_org_id
and o.tt_fromdate <= pl2.tt_fromdate
and o.tt_todate >= pl2.tt_todate
and pl2.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl2.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl2.tt_status >= 2
and pl2.tt_type = 5
and pl2.tt_nr = 2
and plc2.tt_prj_id = p.tt_prj_id
and pl2.tt_plan_id = plc2.tt_plan_id
and pl2.tt_dim_id = o.tt_org_id
and o.tt_org_id in [PLAN_ORG;<list>;"";"Organisatie";<select_organisation>;<all>;""]
and (plc1.tt_costs*100) - (plc2.tt_costs*100) <> 0
and p.tt_code is not null
and rhit.product_code = p.tt_code
and plc1.tt_emp_id = plc2.tt_emp_id
and p.tt_code in (select product_code from rh_internetaken where product_code = p.tt_code)

group by
rhit.rekening,
rhit.product_code,
rhit.budget,
rhit.product_naam,
rhit.sleutel

union all

-- BUDGETREGELS DEBET WEL VERDELING INTERNE TAKEN

select distinct
rhit.tegenRekening as REKENING,
rhit.tegenProduct_code as PRODUCT,
rhit.tegenBudget as BUDGETHOUDER,
( ( sum(plc2.tt_costs*100) - sum(plc1.tt_costs*100) ) * (rhit.sleutel/10000) ) as BEDRAG,
' ' as BBV,
rhit.tegenProduct_naam as OMSCHRIJVING

from
tt_plan pl1,
tt_plan pl2,
tt_plan_cells plc1,
tt_plan_cells plc2,
tt_org o,
tt_prj p,
rh_internetaken rhit

where
o.tt_fromdate <= pl1.tt_fromdate
and o.tt_todate >= pl1.tt_todate
and pl1.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl1.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl1.tt_status >= 2
and pl1.tt_type = 5
and pl1.tt_nr = 1
and plc1.tt_prj_id = p.tt_prj_id
and pl1.tt_plan_id = plc1.tt_plan_id
and pl1.tt_dim_id = o.tt_org_id
and o.tt_fromdate <= pl2.tt_fromdate
and o.tt_todate >= pl2.tt_todate
and pl2.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl2.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl2.tt_status >= 2
and pl2.tt_type = 5
and pl2.tt_nr = 2
and plc2.tt_prj_id = p.tt_prj_id
and pl2.tt_plan_id = plc2.tt_plan_id
and pl2.tt_dim_id = o.tt_org_id
and o.tt_org_id in [PLAN_ORG;<list>;"";"Organisatie";<select_organisation>;<all>;""]
and (plc1.tt_costs*100) - (plc2.tt_costs*100) <> 0
and p.tt_code is not null
and rhit.product_code = p.tt_code
and plc1.tt_emp_id = plc2.tt_emp_id
and p.tt_code in (select product_code from rh_internetaken where product_code = p.tt_code)

group by
rhit.tegenRekening,
rhit.tegenProduct_code,
rhit.tegenBudget,
rhit.tegenProduct_naam,
rhit.sleutel

union all

-- BUDGETREGELS CREDIT TECHNISCHE AFWIKKELING BEHEERSTAKEN (0900%)

select distinct
rhit.Rekening as REKENING,
rhit.tegenProduct_code as PRODUCT,
rhit.budget as BUDGETHOUDER,
( ( sum(plc2.tt_costs*100) - sum(plc1.tt_costs*100) ) * ( (rhit.sleutel/10000) * -1 ) ) as BEDRAG,
' ' as BBV,
rhit.product_naam as OMSCHRIJVING

from
tt_plan pl1,
tt_plan pl2,
tt_plan_cells plc1,
tt_plan_cells plc2,
tt_org o,
tt_prj p,
rh_internetaken rhit

where
o.tt_fromdate <= pl1.tt_fromdate
and o.tt_todate >= pl1.tt_todate
and pl1.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl1.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl1.tt_status >= 2
and pl1.tt_type = 5
and pl1.tt_nr = 1
and plc1.tt_prj_id = p.tt_prj_id
and pl1.tt_plan_id = plc1.tt_plan_id
and pl1.tt_dim_id = o.tt_org_id
and o.tt_fromdate <= pl2.tt_fromdate
and o.tt_todate >= pl2.tt_todate
and pl2.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl2.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl2.tt_status >= 2
and pl2.tt_type = 5
and pl2.tt_nr = 2
and plc2.tt_prj_id = p.tt_prj_id
and pl2.tt_plan_id = plc2.tt_plan_id
and pl2.tt_dim_id = o.tt_org_id
and o.tt_org_id in [PLAN_ORG;<list>;"";"Organisatie";<select_organisation>;<all>;""]
and (plc1.tt_costs*100) - (plc2.tt_costs*100) <> 0
and p.tt_code is not null
and rhit.product_code = p.tt_code
and rhit.tegenProduct_code like '900%'
and plc1.tt_emp_id = plc2.tt_emp_id
and p.tt_code in (select product_code from rh_internetaken where product_code = p.tt_code)

group by
rhit.rekening,
rhit.tegenProduct_code,
rhit.budget,
rhit.product_naam,
rhit.sleutel

union all

-- BUDGETREGELS DEBET TECHNISCHE AFWIKKELING BEHEERSTAKEN (0900%)

select distinct
rhit.tegenRekening as REKENING,
'3570000' as PRODUCT,
rhit.tegenBudget as BUDGETHOUDER,
( ( sum(plc2.tt_costs*100) - sum(plc1.tt_costs*100) ) * (rhit.sleutel/10000) ) as BEDRAG,
' ' as BBV,
'Saldi kostenplaatsen' as OMSCHRIJVING

from
tt_plan pl1,
tt_plan pl2,
tt_plan_cells plc1,
tt_plan_cells plc2,
tt_org o,
tt_prj p,
rh_internetaken rhit

where
o.tt_fromdate <= pl1.tt_fromdate
and o.tt_todate >= pl1.tt_todate
and pl1.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl1.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl1.tt_status >= 2
and pl1.tt_type = 5
and pl1.tt_nr = 1
and plc1.tt_prj_id = p.tt_prj_id
and pl1.tt_plan_id = plc1.tt_plan_id
and pl1.tt_dim_id = o.tt_org_id
and o.tt_fromdate <= pl2.tt_fromdate
and o.tt_todate >= pl2.tt_todate
and pl2.tt_fromdate >= [VAN_DATUM;<date>;"<yearstart>";"Periode vanaf:";<default>;""]
and pl2.tt_todate <= [TOT_DATUM;<date>;"<yearend>";"Periode t/m:";<default>;""]
and pl2.tt_status >= 2
and pl2.tt_type = 5
and pl2.tt_nr = 2
and plc2.tt_prj_id = p.tt_prj_id
and pl2.tt_plan_id = plc2.tt_plan_id
and pl2.tt_dim_id = o.tt_org_id
and o.tt_org_id in [PLAN_ORG;<list>;"";"Organisatie";<select_organisation>;<all>;""]
and (plc1.tt_costs*100) - (plc2.tt_costs*100) <> 0
and p.tt_code is not null
and rhit.product_code = p.tt_code
and rhit.tegenProduct_code like '900%'
and plc1.tt_emp_id = plc2.tt_emp_id
and p.tt_code in (select product_code from rh_internetaken where product_code = p.tt_code)

group by
rhit.tegenRekening,
rhit.tegenBudget,
rhit.sleutel

Heeft iemand enig idee waarom deze foutmelding kan ontstaan en hoe ik deze kan oplossen?

Alvast bedankt!

 

[mitedo]

als ik de foutmelding op google zoek, dan het eerste resultaat.