debuging help

[reivi]

Hey there,

all of you with precious infos and help .

Here i come again with a new problem for you to help me with

I do have a monthly script, wich runs nicely.

SELECT aff_type, aide_aff.fon_refn, fon_iden, sum( AFF_MNT_FF)
FROM AIDE_AFF, AIDE_REGL, aide_fon
WHERE AIDE_AFF.REGL_NUM = AIDE_REGL.REGL_NUM
and aide_fon.FON_REFN = aide_aff.FON_REFN
and ( to_char(AIDE_REGL.REGL_DATE,'yyyymmdd')
between
to_char(last_day(sysdate-15),'yyyymm') || '01'
and to_char(last_day(sysdate-15),'yyyymmdd'))
and NOT AIDE_REGL.REGL_TYPE_REGL = 'FS'
AND AIDE_AFF.AFF_TYPE = 'MI'
GROUP BY Aide_Aff.Aff_type, aide_aff.fon_refn, fon_iden
union
SELECT aff_type, aide_aff.fon_refn, fon_iden, sum( AFF_MNT_FF) * -1
FROM AIDE_AFF, AIDE_REGL, aide_fon
WHERE AIDE_AFF.REGL_NUM = AIDE_REGL.REGL_NUM
and aide_fon.FON_REFN = aide_aff.FON_REFN
and AIDE_REGL.REGL_DATE_ANN >= trunc(sysdate-15, 'MONTH')
and AIDE_REGL.REGL_DATE_ANN < add_months( trunc(sysdate-15, 'MONTH'), 1 )
and AIDE_REGL.REGL_DATE >= trunc( sysdate-15, 'YEAR')
and AIDE_REGL.REGL_DATE < add_months( trunc( sysdate-15, 'YEAR'), 12 )
and NOT AIDE_REGL.REGL_TYPE_REGL = 'FS'
AND AIDE_AFF.AFF_TYPE = 'MI'
GROUP BY AIDE_AFF.AFF_TYPE, aide_aff.fon_refn, fon_iden
union
SELECT aff_type, aide_aff.fon_refn, fon_iden, sum( AFF_MNT_FF) * -1
FROM AIDE_AFF, AIDE_REGL, aide_fon
WHERE AIDE_AFF.REGL_NUM = AIDE_REGL.REGL_NUM
and aide_fon.FON_REFN = aide_aff.FON_REFN
and AIDE_REGL.REGL_DATE_ANN >= trunc(sysdate-15, 'MONTH')
and AIDE_REGL.REGL_DATE_ANN < add_months( trunc(sysdate-15, 'MONTH'), 1 )
AND AIDE_REGL.REGL_DATE < trunc( sysdate-15, 'YEAR')
and NOT AIDE_REGL.REGL_TYPE_REGL = 'FS'
AND AIDE_AFF.AFF_TYPE = 'MI'
GROUP BY AIDE_AFF.AFF_TYPE, aide_aff.fon_refn, fon_iden

i was asked to change it, to have a second one that does increment each month

my test is here =

SELECT aff_type, aide_aff.fon_refn, fon_iden,act_code, regl_date, sum( AFF_MNT_FF)
FROM AIDE_AFF, AIDE_REGL, aide_fon
WHERE AIDE_AFF.REGL_NUM = AIDE_REGL.REGL_NUM
and aide_fon.FON_REFN = aide_aff.FON_REFN
and ( to_char(AIDE_REGL.REGL_DATE,'yyyymmdd')
between
to_char(trunc(sysdate),'yyyy') || '01'
and to_char(last_day(sysdate-15),'yyyymmdd'))
and NOT AIDE_REGL.REGL_TYPE_REGL = 'FS'
AND AIDE_AFF.AFF_TYPE = 'MI'
GROUP BY Aide_Aff.Aff_type, aide_aff.fon_refn, fon_iden, act_code, regl_date
union
(SELECT aff_type, aide_fon.fon_refn, fon_iden, aide_regl.act_code, regl_date,sum( AFF_MNT_FF) * -1
FROM AIDE_AFF, AIDE_REGL, aide_fon
WHERE AIDE_AFF.REGL_NUM = AIDE_REGL.REGL_NUM
and aide_fon.fon_refn = aide_aff.fon_refn
--and aide_act.act_code = aide_regl.act_code
and AIDE_REGL.REGL_DATE_ANN >= to_date ('20100101','yyyymmdd')
and AIDE_REGL.REGL_DATE_ANN < add_months( trunc(sysdate-15, 'MONTH'), 1 )
and AIDE_REGL.REGL_DATE >= to_date ('20100101','yyyymmdd')
and AIDE_REGL.REGL_DATE < add_months( trunc( sysdate-15, 'YEAR'), 12 )
and NOT AIDE_REGL.REGL_TYPE_REGL = 'FS'
AND AIDE_AFF.AFF_TYPE = 'MI'
GROUP BY AIDE_AFF.AFF_TYPE, aide_fon.fon_refn,fon_iden, act_code, regl_date
union
SELECT aff_type, aide_aff.fon_refn, fon_iden, act_code, regl_date,sum( AFF_MNT_FF) * -1
FROM AIDE_AFF, AIDE_REGL, aide_fon
WHERE AIDE_AFF.REGL_NUM = AIDE_REGL.REGL_NUM
and aide_fon.FON_REFN = aide_aff.FON_REFN
and AIDE_REGL.REGL_DATE_ANN >= to_date('20100101','yyyymmdd')
and AIDE_REGL.REGL_DATE_ANN < add_months( trunc(sysdate-15, 'MONTH'), 1 )
AND AIDE_REGL.REGL_DATE = trunc( sysdate-15, 'YEAR')
and NOT AIDE_REGL.REGL_TYPE_REGL = 'FS'
AND AIDE_AFF.AFF_TYPE = 'MI'
GROUP BY AIDE_AFF.AFF_TYPE, aide_aff.fon_refn, fon_iden, act_code, regl_date)

but it doesnt work (neither is the trunc (sysdate, 'YEAR') function) The script runs, but the results are different from what expected . (the script must begin jan 1st and sum up each new month to the results up to dec 31 , where the first one only does it month by month )

the results arent in par with first script adding manualy each month .


do you see where the problem can be ?

in advance, thx .

 

[Dagon]

You seem to be included regl_date and act_code in the group by of the second query. This would give you a sum for every act_code and every date, which doesn't sound like what you actually want to achieve.

For Oracle-related work, contact me through Linked-In.

 

[reivi]

Hi dagon,

if i dont ad the group by in the second query, the query doesnt work ( and if i dont list all the headers from the select list, it doesnt work either)

 

[Dagon]

To go any further with this, you will need to provide:

a) create table scripts
b) insert statements to set up some example data
c) the results you would expect your query to return if run against the example data in b).

For Oracle-related work, contact me through Linked-In.