E9.2 Access SQL View as JDE Table

[osterman]

I'm trying to create a custom view between 2 Object Library tables (F9860 and F00165) following these instructions here. I created the SQL view and the JDE table, and set the permissions to the view to match the joined tables. I can even open the JDE table in UTB. But when I do a find, nothing gets returned. Below is the view's associated query which works correctly. I've attached a picture of the JDE table columns too.

I think I'm missing one small piece to get this working correctly.

SELECT OL920.F9860.SIOBNM AS DSON, OL920.F9860.SIMD, OL920.F9860.SISY, OL920.F9860.SISYR, OL920.F9860.SIFUNO, OL920.F00165.GDOBNM, OL920.F00165.GDTXKY, OL920.F00165.GDMOSEQN,
OL920.F00165.GDGTMOTYPE, OL920.F00165.GDUSER, OL920.F00165.GDUPMJ, OL920.F00165.GDTDAY, OL920.F00165.GDGTITNM, OL920.F00165.GDGTFILENM, OL920.F00165.GDTXFT, OL920.F00165.GDGTFUTM1,
OL920.F00165.GDGTFUTM2
FROM OL920.F9860 INNER JOIN
OL920.F00165 ON OL920.F9860.SIOBNM = OL920.F00165.GDTXKY
WHERE (OL920.F00165.GDOBNM = 'GT9860A')

 

[peterbruce]

Hi Osterman,

Should the inner join be as below?

FROM OL920.F9860 INNER JOIN
OL920.F00165 ON OL920.F9860.SIOBNM = OL920.F00165.GDOBNM

 

[osterman]

Hi Peterbruce,

No you don't want to join on the OBNM columns from each table because they don't match. See attached screenshot.

 

[Stank1964]

So the SQL view and the JDE table have exactly the same name in the same database where tables are normally created by E1, right? I assume the view when executed in SQL works based on what you want?

 

[osterman]

The SQL View is in JDE920.OL920 (Object Library) because that's where F9860 and F00165 are located. But the JDE Table is in our DEVELOPMENT.TESTDTA Database and Schema.

Both the Table and View are named S55OBJL. And yes I can run the above SQL that defines the SQL view and it pulls data successfully.

I think the possible issue is that from our JDE Development client, we can access F9860 without any issues (even though its in a Object Library) but we cannot access the Object Library version of F00165, we can only access the TESTDTA version of F00165.

I wonder why I'm able to access certain Object Library tables, but not others? Or if there's a way to specify exactly what table in what schema I want to access?

 

[Kim Schmidt]

Maybe you have OCM mappings that are influencing this depending on which environment you're executing from?

 

[BOster]

Could be permissions or OCMs, DB owner/schema or any number of things. One thing it looks like from your SQL is to make sure you alias all the fields in the SQL view result set with the same table prefix as your JDE virtual table. So for example you may define VT as the table prefix for the table FV564211 then the SQL for your SQL view would be:

select
shkcoo as VTKCOO
, shdoco as VTDOCO
, shdcto as VTDCTO
, sdlnid as VTLNID
, sditm as VTITM
from F4201 inner join F4211 on....

This and other issues can usually be found by turning on the debug log and grabbing the generated SQL.

 

[osterman]

Thanks for the responses. It looks like I have a few things to investigate and try out. I'll also try updating the field aliases to see if that works.

If I make any progress, I'll update the post.

 

[alfredorz]

Hi @osterman ,

Do you need to doing some things: in you sql view you need put the same alias to columns that JDE view defined, and very important in view database put SELECT to *PUBLIC role permissions. Sometimes, If doesn't work, I had to delete database view, create table from JDE, drop table by database, and create view again (and give view database permissions again, even exit and login again of FAT).

Good luck!

 

[osterman]

Thank you all for the responses.

After updating the SQL aliases to match the JDE Table columns with the prefix, I'm able to query the table in UTB and create a BSVW on the JDE table. This works correctly now.

I'm still trying to figure out how to use this table/view in a test application.

 

[JohnDanter2]

Thank you all for the responses.

After updating the SQL aliases to match the JDE Table columns with the prefix, I'm able to query the table in UTB and create a BSVW on the JDE table. This works correctly now.

I'm still trying to figure out how to use this table/view in a test application.

I do this all the time. Glad you have it working, but for me it's usually an external table column isn't 0 and some E1 ALIASes default to 0 or date and time columns, so for that I use UTIME and silly things like that.
Also clesr DDDICT and DDTBLE and the other 4 files often.
I look at the traget table and mimick the column lengths and type as best I can. If I can't find a match I make a new DD. As long as the E1 DDs are longer than the target table it's OK.

For your next step, just make a BSVW as normal and all should be fine.

I would have asked this in the developers section, I may have seen it sooner

drop table F554801V;
/
drop view F554801V;

create or replace VIEW F554801V
as


SELECT
    wadoco AS pwdoco,
    wadcto AS pwdcto,
    wawr02 AS pwwr02,
    wasrst AS pwsrst,
    ibsrp7 AS pwsrp7,
    ibprp4 AS pwprp4,
    wawr01 AS pwwr01,
    watyps AS pwtyps,
    waitm AS pwitm,
    wadl01 AS pwdl01,
    wauorg AS pwuorg,
    lpnqty AS pwy55cqty,
    ( wauorg - lpnqty ) AS pwuopn,
    waansa AS pwansa,
    lssrst AS pwa1st,
    ibanpl AS pwanpl,
    wamcu AS pwmcu,
    wammcu AS pwmmcu,
    waan8 AS pwan8
FROM
    (
        SELECT
            wadoco,
            wasrst,
            wauorg,
            SUM(lcqty) AS lpnqty,
            wawr01,
            wawr02,
            ibsrp7,
            ibprp4,
            wadl01,
            watyps,
            waitm,
            waansa,
            lssrst,
            ibanpl,
            wamcu,
            wammcu,
            wadcto,
            waan8
        FROM
            f4801
            LEFT OUTER JOIN f55lpnc ON lcdoco = wadoco
                                       AND lcdcto = wadcto
            INNER JOIN f55lpns ON lcy55lpnid = lsy55lpnid
            INNER JOIN f4102 ON ibitm = waitm
                                AND ibmcu = wammcu
        GROUP BY
            wadoco,
            wasrst,
            wauorg,
            wawr01,
            wawr02,
            ibsrp7,
            ibprp4,
            wadl01,
            watyps,
            waitm,
            waansa,
            lssrst,
            ibanpl,
            wamcu,
            wammcu,
            wadcto,
            waan8
        ORDER BY
            wadoco,
            wasrst,
            wauorg,
            wawr01,
            wawr02,
            ibsrp7,
            ibprp4,
            wadl01,
            watyps,
            waitm,
            waansa,
            lssrst,
            ibanpl,
            wamcu,
            wammcu,
            wadcto,
            waan8
    )
WHERE
    wauorg <> lpnqty
    OR lpnqty IS NULL;
  
  
grant SELECT ON F554801V to role1
/
grant SELECT ON F554801V to role2
/
grant SELECT ON F554801V to role3
/
grant SELECT ON F554801V to specificENVrole
// ***** And other ENVIRONMENTS *****
grant SELECT ON F554801V to JDEACCT;

You can get the list of roles from your DBA and CNC team

 

[Adrian_Chimirel]

As John says, once you have the E1 view created over your virtual table, you may use it into an interactive application, a Find/Browse form, and enjoy

 

[BOster]

There is one restriction with a SQL view / JDE virtual table. In probably all cases it will be read only. If you want to update any of the underlying data you will need to do so in code outside of a data bound control like a grid or form control.

 

[JohnDanter2]

There is one restriction with a SQL view / JDE virtual table. In probably all cases it will be read only. If you want to update any of the underlying data you will need to do so in code outside of a data bound control like a grid or form control.

If I understand you, thats not quite true Brian.
View mode is default yes but you just grant the other rights to update/insert/delete.

GRANT INSERT ON E1-DROPPEDVIEW TO JDE-ROLE-READING-THIS-DATA

All my dropped views enable me to do this. I can read, update insert everything all from an E1 APPL or IO just like a normal E1 TBLE just fine once the grants are added to the E1 roles

As long as E1 has specs for a given TBLE, E1 doesn't know the TBLE you are referencing lives externally or is even a SQL VIEW etc as E1 just fires off the SQL to the middlelayer and from there it's converted to the correct table to hit the database.

Works for me anyway. We have loads of external databases and I have recreated a few of these tables (mainly B2B tables) in E1, where I use my own APPLs and table IO to update said data.

 

[DaveWagoner]

wow wow wow

I'm too superstitious to attempt anything other than read only against even a JDE-native multitable view. I was burned once at some point in my history, and added a hard rule to my list of JDE superstitions that stand to this day.

 

[BOster]

If I understand you, thats not quite true Brian.
View mode is default yes but you just grant the other rights to update/insert/delete.

GRANT INSERT ON E1-DROPPEDVIEW TO JDE-ROLE-READING-THIS-DATA

All my dropped views enable me to do this. I can read, update insert everything all from an E1 APPL or IO just like a normal E1 TBLE just fine once the grants are added to the E1 roles

As long as E1 has specs for a given TBLE, E1 doesn't know the TBLE you are referencing lives externally or is even a SQL VIEW etc as E1 just fires off the SQL to the middlelayer and from there it's converted to the correct table to hit the database.

Works for me anyway. We have loads of external databases and I have recreated a few of these tables (mainly B2B tables) in E1, where I use my own APPLs and table IO to update said data.

In general if you create a SQL view / Virtual table that is updateable you probably didn't need a SQL view / virtual table.

Generally you are doing stuff with a SQL view that is not possible or easy with JDE table IO tools which usually involves complex joins, case statements, casts, sub queries, etc. and you can't issue INSERT, UPDATE or DELETE SQL statements against those.

I will concede your use case with external tables may be the exception but I would still be hesitant with those and would probably use some other method to write data to non-JDE tables. First resort would be creating some other API, second would be to use the JDE foreign table APIs.

 

[JohnDanter2]

Yeah sorry should have clarified, I didn't update as in a Select SUM type view no.
I meant it as in you can use views to lookup and update external tables outside of E1.

Why we have so many outside the E1 schemas, I don't know