Results 1 to 6 of 6

Thread: ODA on Xe WQuestion

  1. #1
    Senior Member altquark's Avatar
    Join Date
    Oct 2000
    Location
    Kansas City, MO
    Posts
    2,641

    ODA on Xe WQuestion

    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
    Jon Steel
    EnterpriseOne/SOA Technical Architect
    erpSOURCING LLC
    http://www.erpsourcing.com
    cto@spla.sh
    24/7 Assistance - (904) 382 5701

  2. #2
    Senior Member
    Join Date
    Jul 2001
    Location
    Colorado
    Posts
    216

    RE: ODA on Xe WQuestion

    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?

    Darrell Allison
    IT Operations Mgr/JDE CNC
    i5 9406/520 V5R4M0 2-Way/OneWorld 8.12 TR 8.96.2.3

  3. #3
    Senior Member
    Join Date
    Nov 2000
    Location
    Phoenix, Arizona 85016-4668
    Posts
    266

    Re: ODA on Xe WQuestion

    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
    Ray Justus
    Kitchell Corporation
    Phoenix, AZ 85016
    (602) 631-6157
    E1 8.12 TR 8.98.1.3
    Servers: W2K3
    Workstations: Windows Xp, Windows 7
    Database: SQL Server 2008

  4. #4
    Senior Member altquark's Avatar
    Join Date
    Oct 2000
    Location
    Kansas City, MO
    Posts
    2,641

    Re: ODA on Xe WQuestion

    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 Steel
    EnterpriseOne/SOA Technical Architect
    erpSOURCING LLC
    http://www.erpsourcing.com
    cto@spla.sh
    24/7 Assistance - (904) 382 5701

  5. #5
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,250

    Re: ODA on Xe WQuestion

    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 ...
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  6. #6
    Senior Member altquark's Avatar
    Join Date
    Oct 2000
    Location
    Kansas City, MO
    Posts
    2,641

    Re: ODA on Xe WQuestion

    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.
    Jon Steel
    EnterpriseOne/SOA Technical Architect
    erpSOURCING LLC
    http://www.erpsourcing.com
    cto@spla.sh
    24/7 Assistance - (904) 382 5701

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.