new problem with oracle 8

[reivi]

Hi there,

Got a problem that returns an ora-00900 error code.and i dont see the problem

the query is :

SELECT
aff_type,
aide_aff.fon_refn,
fon_iden,
aide_regl.act_code,
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

and i do get an error on the aide_regl.act_code line with toad

the same without the act_code lines works fine

Act_code is a table from aide_regl (shared with aide_act wich isnt used here, if i declare aide_act, i do get the same errors)

do you see where the error can be ?

thx

 

[Dagon]

ORA-900 is "Invalid SQL statement", which is pretty basic. Make sure you don't have any blanks lines in your SQL. TOAD will execute a contiguous block of code as a single SQL statement. If you have a blank line half way through, it will try to execute everything after the blank line as the statement. Alternatively, select the whole statement and then press execute query.

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

 

[reivi]

already tested that ( first things i did test .... )

when i had a 4th table i do get many error stops in toad. (random ones...) as if toad cant manage 4th tables at a time.

or maybe my server cant ? (memory or cache or such thing ?? )

 

[Dagon]

All TOAD does is pass the query to Oracle, so there are no limitations on how many tables or how complicated the statement can be. If the statement is crashing, it's because there is something wrong with your statement, not because of TOAD and probably not because of Oracle.

If you are in doubt, try running it somewhere else like SQL*Plus.



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

 

[fredericofonseca]

your problem is that aide_regl.act_code is referenced on the select but not on the group by.

Either include it on the group by or use one of the possible functions on the select side

SELECT
aff_type,
aide_aff.fon_refn,
fon_iden,
aide_regl.act_code,
sum( AFF_MNT_FF)
.....
GROUP BY Aide_Aff.Aff_type, aide_aff.fon_refn, fon_iden

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[reivi]

that was the first thing we tried..doesnt solve the problem

But , having discussed the issue with the previous IT manager of the entreprise it would seem that the problem is either with Aide_fon or Aide_act tables, wich were wrongly built (The DBA was managed for years by someone with no IT background and only small oracle formation)

 

[Dagon]

I'd be fascinated to know how you "wrongly build" a table.

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

 

[reivi]

i wonder too

but I did find a solution (partial one)

If i dont call upon Act and Fon tables, the query is working

even though they define the fon_refn and act_code lines....

strange behavior in this DBA ..

the working query :

SELECT aff_type, fon_refn, act_code, sum( AFF_MNT_FF)
FROM AIDE_AFF, AIDE_REGL
WHERE AIDE_AFF.REGL_NUM = AIDE_REGL.REGL_NUM
--and aide_fon.FON_REFN = aide_aff.FON_REFN
--and aid_act.act_code = aide_regl=act_code
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, fon_refn, act_code