Regroup Data in RTF Template

Antoine KAIBER

What do you call an IDE ?

I have an SQL Execution Interface in Oracle where I can try this query but it says :

java.rmi.AccessException: [EJB:010160]Security violation: User DBA.CONFIGDBA has insufficient permission to access EJB type=<ejb>, application=GC3App, module=SqlSession.jar, ejb=SqlSessionHome, method=execute, methodInterface=Remote, signature={java.lang.String,java.lang.Object[]=null}.

when I try to test it.

I could send you the XML, what is your email ? Since I can't attach any file even in advanced mode

Antoine KAIBER

My internet is very slow atm so if i can avoid downloading SQL Dev it's better. I tried to run SQL plus with a tuto but I get "'sqlplus' is not recognized as an internal or external command," in cmd, do you have an idea why ?

Edit: I created a link and I can now launch the report but it doesn't change anything, there is no grouping by.

I know this should be easier doing it directly in the RTF so if anyone has an idea about nested groups I'm all ears, thank you.

Antoine KAIBER

Well, appart from the ORA number, that was the only error message I got:

With your new SQL the Data set can be created (G_1) :

But when I run the report it says "report not valid"  and I also get this message :

ORA-01722: invalid number --

I don't know if it's not coming from the fact that I didnt use links like for other data sets (Data sets are at leats linked by Shipment_GID I think, I will try)

Antoine KAIBER

Weirdly I get "TRANSPORT_HANDLING_UNIT_XID": invalid identifier

Antoine KAIBER

Well I posted this :

select

sum(itm.ITEM_PACKAGE_COUNT)

,sum(coalesce(spu.TRANSPORT_HANDLING_UNIT_GID,'-') TRANSPORT_HANDLING_UNIT_GID)

,coalesce(substr(TRANSPORT_HANDLING_UNIT_GID, instr(TRANSPORT_HANDLING_UNIT_GID,'.', 1)+1, length(TRANSPORT_HANDLING_UNIT_GID)),'-') TRANSPORT_HANDLING_UNIT_XID

from SHIP_STOP_ITEM_BOV itm

left join s_ship_unit spu

ON itm.S_SHIP_UNIT_GID=spu.S_SHIP_UNIT_GID

left join s_ship_unit_line spl

ON itm.S_SHIP_UNIT_GID=spl.S_SHIP_UNIT_GID

group by TRANSPORT_HANDLING_UNIT_XID

but it says "missing right parenthesis".  I already asked my DBA who said it should be difficult and he was more ok with the idea of doing the group by in the RTF I think. I will see again with him when I can.