Hello,

My issue is the good old ORA02070 and I've found different workarounds but none of them fits my particular problem. It happens when I try to do a group by on remote database query.

I need to compare data between the local and remote database, so I need to make it work. (I don't have issue with the local part.) Ultimately I need to do it using ODBC (read only access) so I don't have the luxury to create temp tables, procedures or complex scripts suggested in the workarounds I could locate.

My ultimate goal is to have the menu structure (F9001 file contains Parent Child relations, F9000 the menu item names in E1) into the MENU column and LVBOTTOM would hold the bottom item in the hierarchy. I write the purpose only if there is a better way to achieve the same goal that can be a workaround also.

From what I read my understanding at this point is that the issue with my query is the LONG datatype but I can't find any way to enforce the type VARCAR2(4000) on my query. (This attempt did not work, but not sure if I even succeded with the type conversion...)

Maybe this query could be fetched to a variable forcing the column format, but frankly I am unable to do it myself.

Either way, I wanted to do the conversion to VARCHAR2 straight away, or any other method which would make the group by working. (Practically I'd need the same "incorrect conversion" like 10.2 HSODBC did in this: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=364710980156851&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=1675758.1&_afrWindowMode=0&_adf.ctrl-state=qdrmglpxv_4 )

My query:
with temp1 as (
SELECT distinct (select TMTASKNM from QACTL.F9000@EJDEQ12 where TMTASKID=TRCHILDTSK) LVBOTTOM,(select TMTASKNM from QACTL.F9000@EJDEQ12 where TMTASKID=TRPARNTTSK) PARENT, LEVEL LVL, SUBSTR(SYS_CONNECT_BY_PATH((select TRIM(TMTASKNM) from QACTL.F9000@EJDEQ12 where TMTASKID=TRCHILDTSK), '|'),2,4000) MENU
FROM (select TRPARNTTSK,TRCHILDTSK FROM QActl.F9001@EJDEQ12 where TRRLTYPE='110')
CONNECT BY PRIOR trim(TRCHILDTSK) = trim(TRPARNTTSK))
select * from temp1
where LVL||LVBOTTOM in (select MAX(LVL)||LVBOTTOM from temp1 group by LVBOTTOM)

Many thanks for help in advance:
Tamas