ODA on Xe WQuestion

altquark

altquark

Legendary Poster
We're having an issue with OneWorld ODA

Version : B7333XU7SP22Q1

Issue : Trying to run UNION in a select statement through OneWorld ODA - receiving a syntax error.

Following is the SQL Statement :

I tried the statement in case #200918397 as follows (modifed for Xe) and I still receive an error through ODA.

SELECT
F4211.SDKCOO,
F4211.SDDOCO,
F4211.SDDCTO,
F4211.SDLNID,
F4211.SDSFXO,
F4211.SDMCU,
F4211.SDCO,
F4211.SDOKCO,
F4211.SDOORN,
F4211.SDOCTO,
F4211.SDOGNO,
F4211.SDRKCO,
F4211.SDRORN,
F4211.SDRCTO,
F4211.SDRLLN,
F4211.SDDMCT,
F4211.SDDMCS,
F4211.SDAN8,
F4211.SDSHAN,
F4211.SDPA8,
F4211.SDDRQJ,
F4211.SDTRDJ,
F4211.SDPDDJ,
F4211.SDADDJ,
F4211.SDIVD,
F4211.SDCNDJ,
F4211.SDDGL,
F4211.SDRSDJ,
F4211.SDPEFJ,
F4211.SDPPDJ,
F4211.SDVR01,
F4211.SDVR02,
F4211.SDITM,
F4211.SDLITM,
F4211.SDAITM,
F4211.SDLOCN,
F4211.SDLOTN,
F4211.SDFRGD,
F4211.SDTHGD,
F4211.SDFRMP,
F4211.SDTHRP,
F4211.SDEXDP,
F4211.SDDSC1,
F4211.SDDSC2,
F4211.SDLNTY,
F4211.SDNXTR,
F4211.SDLTTR,
F4211.SDEMCU,
F4211.SDRLIT,
F4211.SDKTLN,
F4211.SDCPNT,
F4211.SDRKIT,
F4211.SDKTP,
F4211.SDSRP1,
F4211.SDSRP2,
F4211.SDSRP3,
F4211.SDSRP4,
F4211.SDSRP5,
F4211.SDPRP1,
F4211.SDPRP2,
F4211.SDPRP3,
F4211.SDPRP4,
F4211.SDPRP5,
F4211.SDUOM,
F4211.SDUORG,
F4211.SDSOQS,
F4211.SDSOBK,
F4211.SDSOCN,
F4211.SDSONE,
F4211.SDUOPN,
F4211.SDQTYT,
F4211.SDQRLV,
F4211.SDCOMM,
F4211.SDOTQY,
F4211.SDUPRC,
F4211.SDAEXP,
F4211.SDAOPN,
F4211.SDPROV,
F4211.SDTPC,
F4211.SDAPUM,
F4211.SDLPRC,
F4211.SDUNCS,
F4211.SDECST,
F4211.SDCSTO,
F4211.SDTCST,
F4211.SDINMG,
F4211.SDPTC,
F4211.SDRYIN,
F4211.SDDTBS,
F4211.SDTRDC,
F4211.SDFUN2,
F4211.SDASN,
F4211.SDPRGR,
F4211.SDCLVL,
F4211.SDCADC,
F4211.SDKCO,
F4211.SDDOC,
F4211.SDDCT,
F4211.SDODOC,
F4211.SDODCT,
F4211.SDOKC,
F4211.SDPSN,
F4211.SDDELN,
F4211.SDTAX1,
F4211.SDTXA1,
F4211.SDEXR1,
F4211.SDATXT,
F4211.SDPRIO,
F4211.SDRESL,
F4211.SDBACK,
F4211.SDSBAL,
F4211.SDAPTS,
F4211.SDLOB,
F4211.SDEUSE,
F4211.SDDTYS,
F4211.SDNTR,
F4211.SDVEND,
F4211.SDCARS,
F4211.SDMOT,
F4211.SDROUT,
F4211.SDSTOP,
F4211.SDZON,
F4211.SDCNID,
F4211.SDFRTH,
F4211.SDSHCM,
F4211.SDSHCN,
F4211.SDSERN,
F4211.SDUOM1,
F4211.SDPQOR,
F4211.SDUOM2,
F4211.SDSQOR,
F4211.SDUOM4,
F4211.SDITWT,
F4211.SDWTUM,
F4211.SDITVL,
F4211.SDVLUM,
F4211.SDRPRC,
F4211.SDORPR,
F4211.SDORP,
F4211.SDCMGP,
F4211.SDGLC,
F4211.SDCTRY,
F4211.SDFY,
F4211.SDSO01,
F4211.SDSO02,
F4211.SDSO03,
F4211.SDSO04,
F4211.SDSO05,
F4211.SDSO06,
F4211.SDSO07,
F4211.SDSO08,
F4211.SDSO09,
F4211.SDSO10,
F4211.SDSO11,
F4211.SDSO12,
F4211.SDSO13,
F4211.SDSO14,
F4211.SDSO15,
F4211.SDACOM,
F4211.SDCMCG,
F4211.SDRCD,
F4211.SDGRWT,
F4211.SDGWUM,
F4211.SDSBL,
F4211.SDSBLT,
F4211.SDLCOD,
F4211.SDUPC1,
F4211.SDUPC2,
F4211.SDUPC3,
F4211.SDSWMS,
F4211.SDUNCD,
F4211.SDCRMD,
F4211.SDCRCD,
F4211.SDCRR,
F4211.SDFPRC,
F4211.SDFUP,
F4211.SDFEA,
F4211.SDFUC,
F4211.SDFEC,
F4211.SDURCD,
F4211.SDURDT,
F4211.SDURAT,
F4211.SDURAB,
F4211.SDURRF,
F4211.SDTORG,
F4211.SDUSER,
F4211.SDPID,
F4211.SDJOBN,
F4211.SDUPMJ,
F4211.SDTDAY
FROM
F4211 F4211
WHERE
( F4211.SDLITM < '500')

UNION SELECT
F42119.SDKCOO,
F42119.SDDOCO,
F42119.SDDCTO,
F42119.SDLNID,
F42119.SDSFXO,
F42119.SDMCU,
F42119.SDCO,
F42119.SDOKCO,
F42119.SDOORN,
F42119.SDOCTO,
F42119.SDOGNO,
F42119.SDRKCO,
F42119.SDRORN,
F42119.SDRCTO,
F42119.SDRLLN,
F42119.SDDMCT,
F42119.SDDMCS,
F42119.SDAN8,
F42119.SDSHAN,
F42119.SDPA8,
F42119.SDDRQJ,
F42119.SDTRDJ,
F42119.SDPDDJ,
F42119.SDADDJ,
F42119.SDIVD,
F42119.SDCNDJ,
F42119.SDDGL,
F42119.SDRSDJ,
F42119.SDPEFJ,
F42119.SDPPDJ,
F42119.SDVR01,
F42119.SDVR02,
F42119.SDITM,
F42119.SDLITM,
F42119.SDAITM,
F42119.SDLOCN,
F42119.SDLOTN,
F42119.SDFRGD,
F42119.SDTHGD,
F42119.SDFRMP,
F42119.SDTHRP,
F42119.SDEXDP,
F42119.SDDSC1,
F42119.SDDSC2,
F42119.SDLNTY,
F42119.SDNXTR,
F42119.SDLTTR,
F42119.SDEMCU,
F42119.SDRLIT,
F42119.SDKTLN,
F42119.SDCPNT,
F42119.SDRKIT,
F42119.SDKTP,
F42119.SDSRP1,
F42119.SDSRP2,
F42119.SDSRP3,
F42119.SDSRP4,
F42119.SDSRP5,
F42119.SDPRP1,
F42119.SDPRP2,
F42119.SDPRP3,
F42119.SDPRP4,
F42119.SDPRP5,
F42119.SDUOM,
F42119.SDUORG,
F42119.SDSOQS,
F42119.SDSOBK,
F42119.SDSOCN,
F42119.SDSONE,
F42119.SDUOPN,
F42119.SDQTYT,
F42119.SDQRLV,
F42119.SDCOMM,
F42119.SDOTQY,
F42119.SDUPRC,
F42119.SDAEXP,
F42119.SDAOPN,
F42119.SDPROV,
F42119.SDTPC,
F42119.SDAPUM,
F42119.SDLPRC,
F42119.SDUNCS,
F42119.SDECST,
F42119.SDCSTO,
F42119.SDTCST,
F42119.SDINMG,
F42119.SDPTC,
F42119.SDRYIN,
F42119.SDDTBS,
F42119.SDTRDC,
F42119.SDFUN2,
F42119.SDASN,
F42119.SDPRGR,
F42119.SDCLVL,
F42119.SDCADC,
F42119.SDKCO,
F42119.SDDOC,
F42119.SDDCT,
F42119.SDODOC,
F42119.SDODCT,
F42119.SDOKC,
F42119.SDPSN,
F42119.SDDELN,
F42119.SDTAX1,
F42119.SDTXA1,
F42119.SDEXR1,
F42119.SDATXT,
F42119.SDPRIO,
F42119.SDRESL,
F42119.SDBACK,
F42119.SDSBAL,
F42119.SDAPTS,
F42119.SDLOB,
F42119.SDEUSE,
F42119.SDDTYS,
F42119.SDNTR,
F42119.SDVEND,
F42119.SDCARS,
F42119.SDMOT,
F42119.SDROUT,
F42119.SDSTOP,
F42119.SDZON,
F42119.SDCNID,
F42119.SDFRTH,
F42119.SDSHCM,
F42119.SDSHCN,
F42119.SDSERN,
F42119.SDUOM1,
F42119.SDPQOR,
F42119.SDUOM2,
F42119.SDSQOR,
F42119.SDUOM4,
F42119.SDITWT,
F42119.SDWTUM,
F42119.SDITVL,
F42119.SDVLUM,
F42119.SDRPRC,
F42119.SDORPR,
F42119.SDORP,
F42119.SDCMGP,
F42119.SDGLC,
F42119.SDCTRY,
F42119.SDFY,
F42119.SDSO01,
F42119.SDSO02,
F42119.SDSO03,
F42119.SDSO04,
F42119.SDSO05,
F42119.SDSO06,
F42119.SDSO07,
F42119.SDSO08,
F42119.SDSO09,
F42119.SDSO10,
F42119.SDSO11,
F42119.SDSO12,
F42119.SDSO13,
F42119.SDSO14,
F42119.SDSO15,
F42119.SDACOM,
F42119.SDCMCG,
F42119.SDRCD,
F42119.SDGRWT,
F42119.SDGWUM,
F42119.SDSBL,
F42119.SDSBLT,
F42119.SDLCOD,
F42119.SDUPC1,
F42119.SDUPC2,
F42119.SDUPC3,
F42119.SDSWMS,
F42119.SDUNCD,
F42119.SDCRMD,
F42119.SDCRCD,
F42119.SDCRR,
F42119.SDFPRC,
F42119.SDFUP,
F42119.SDFEA,
F42119.SDFUC,
F42119.SDFEC,
F42119.SDURCD,
F42119.SDURDT,
F42119.SDURAT,
F42119.SDURAB,
F42119.SDURRF,
F42119.SDTORG,
F42119.SDUSER,
F42119.SDPID,
F42119.SDJOBN,
F42119.SDUPMJ,
F42119.SDTDAY
FROM
F42119 F42119
WHERE
( F42119.SDLITM < '500')
ORDER BY
1,
2,
3,
4
 
Hi Jon,

I did a reduced select and nothing wrong with your syntax. Can I safely assume that the "!" I found scattered in are a result of saving the code to text and not part of your original coding?
 
You have no owners in your select clauses. Try this.

SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU, SDCO, SDOKCO, SDOORN, SDOCTO, SDOGNO, SDRKCO, SDRORN, SDRCTO, SDRLLN,
SDDMCT, SDDMCS, SDAN8, SDSHAN, SDPA8, SDDRQJ, SDTRDJ, SDPDDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDPPDJ, SDVR01,
SDVR02, SDITM, SDLITM, SDAITM, SDLOCN, SDLOTN, SDFRGD, SDTHGD, SDFRMP, SDTHRP, SDEXDP, SDDSC1, SDDSC2, SDLNTY, SDNXTR,
SDLTTR, SDEMCU, SDRLIT, SDKTLN, SDCPNT, SDRKIT, SDKTP, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDPRP1, SDPRP2, SDPRP3,
SDPRP4, SDPRP5, SDUOM, SDUORG, SDSOQS, SDSOBK, SDSOCN, SDSONE, SDUOPN, SDQTYT, SDQRLV, SDCOMM, SDOTQY, SDUPRC, SDAEXP,
SDAOPN, SDPROV, SDTPC, SDAPUM, SDLPRC, SDUNCS, SDECST, SDCSTO, SDTCST, SDINMG, SDPTC, SDRYIN, SDDTBS, SDTRDC, SDFUN2, SDASN,
SDPRGR, SDCLVL, SDCADC, SDKCO, SDDOC, SDDCT, SDODOC, SDODCT, SDOKC, SDPSN, SDDELN, SDTAX1, SDTXA1, SDEXR1, SDATXT, SDPRIO,
SDRESL, SDBACK, SDSBAL, SDAPTS, SDLOB, SDEUSE, SDDTYS, SDNTR, SDVEND, SDCARS, SDMOT, SDROUT, SDSTOP, SDZON, SDCNID, SDFRTH,
SDSHCM, SDSHCN, SDSERN, SDUOM1, SDPQOR, SDUOM2, SDSQOR, SDUOM4, SDITWT, SDWTUM, SDITVL, SDVLUM, SDRPRC, SDORPR, SDORP,
SDCMGP, SDGLC, SDCTRY, SDFY, SDSO01, SDSO02, SDSO03, SDSO04, SDSO05, SDSO06, SDSO07, SDSO08, SDSO09, SDSO10, SDSO11, SDSO12,
SDSO13, SDSO14, SDSO15, SDACOM, SDCMCG, SDRCD, SDGRWT, SDGWUM, SDSBL, SDSBLT, SDLCOD, SDUPC1, SDUPC2, SDUPC3, SDSWMS,
SDUNCD, SDCRMD, SDCRCD, SDCRR, SDFPRC, SDFUP, SDFEA, SDFUC, SDFEC, SDURCD, SDURDT, SDURAT, SDURAB, SDURRF, SDTORG, SDUSER,
SDPID, SDJOBN, SDUPMJ, SDTDAY
FROM PRODDTA.F4211
WHERE (SDLITM < '500')
UNION
SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU, SDCO, SDOKCO, SDOORN, SDOCTO, SDOGNO, SDRKCO, SDRORN, SDRCTO, SDRLLN,
SDDMCT, SDDMCS, SDAN8, SDSHAN, SDPA8, SDDRQJ, SDTRDJ, SDPDDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDPPDJ, SDVR01,
SDVR02, SDITM, SDLITM, SDAITM, SDLOCN, SDLOTN, SDFRGD, SDTHGD, SDFRMP, SDTHRP, SDEXDP, SDDSC1, SDDSC2, SDLNTY, SDNXTR,
SDLTTR, SDEMCU, SDRLIT, SDKTLN, SDCPNT, SDRKIT, SDKTP, SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDPRP1, SDPRP2, SDPRP3,
SDPRP4, SDPRP5, SDUOM, SDUORG, SDSOQS, SDSOBK, SDSOCN, SDSONE, SDUOPN, SDQTYT, SDQRLV, SDCOMM, SDOTQY, SDUPRC, SDAEXP,
SDAOPN, SDPROV, SDTPC, SDAPUM, SDLPRC, SDUNCS, SDECST, SDCSTO, SDTCST, SDINMG, SDPTC, SDRYIN, SDDTBS, SDTRDC, SDFUN2, SDASN,
SDPRGR, SDCLVL, SDCADC, SDKCO, SDDOC, SDDCT, SDODOC, SDODCT, SDOKC, SDPSN, SDDELN, SDTAX1, SDTXA1, SDEXR1, SDATXT, SDPRIO,
SDRESL, SDBACK, SDSBAL, SDAPTS, SDLOB, SDEUSE, SDDTYS, SDNTR, SDVEND, SDCARS, SDMOT, SDROUT, SDSTOP, SDZON, SDCNID, SDFRTH,
SDSHCM, SDSHCN, SDSERN, SDUOM1, SDPQOR, SDUOM2, SDSQOR, SDUOM4, SDITWT, SDWTUM, SDITVL, SDVLUM, SDRPRC, SDORPR, SDORP,
SDCMGP, SDGLC, SDCTRY, SDFY, SDSO01, SDSO02, SDSO03, SDSO04, SDSO05, SDSO06, SDSO07, SDSO08, SDSO09, SDSO10, SDSO11, SDSO12,
SDSO13, SDSO14, SDSO15, SDACOM, SDCMCG, SDRCD, SDGRWT, SDGWUM, SDSBL, SDSBLT, SDLCOD, SDUPC1, SDUPC2, SDUPC3, SDSWMS,
SDUNCD, SDCRMD, SDCRCD, SDCRR, SDFPRC, SDFUP, SDFEA, SDFUC, SDFEC, SDURCD, SDURDT, SDURAT, SDURAB, SDURRF, SDTORG, SDUSER,
SDPID, SDJOBN, SDUPMJ, SDTDAY
FROM PRODDTA.F42119
WHERE (SDLITM < '500')
ORDER BY 1, 2, 3, 4
 
let me reiterate

I am NOT trying to do a SQL query using generic SQL - this is for ODA (which therefore doesn't need an ownerID since it passes the SQL into JDE for reparsing as a SQL statement !).

However, I got a reply from Oracle that stated that ODA does not support UNION unfortunately.

JDEDirect does, but its not working the way the users want it to work. >sigh<
 
Jon,

what if your source table (in E1) is actually a business view that has the table join in it. Shouldn't it appear to ODA as a simple table then?

Of course ODA probably still can't handle it, but if you want to grasp at a straw ...
 
yes. That is kind of one way of doing it - but the union that was being proposed to me was a little strange - they were trying to replicate what the "case" statement does by using Union (it wasn't going to work in my opinion !)

JDEDirect does support Union. We're playing with that as well - but I was wondering why JDE supports union but ODA doesn't. I thought that was strange.
 
Back
Top