Left Join clause on JD Edwards

[Daniel Prado]

Hello!

I'm a developer working in a client with JDE 811.

I'm having a problem with a SQL clause, I have to transform a query into a report. It's easy, right? Right! BUT, I have this clause to transform in a report:

[...]
FROM PRODDTA.f4801 LEFT JOIN proddta.f3111 ON WMDOCO=wadoco AND WMITC='U' AND WMqnta<>999999999999999
LEFT JOIN proddta.f3112 ON WlDOCO=wadoco
WHERE WASRST<>91
AND WAtrdj between 113182 AND 113212
[...]

The problem is with the 'AND WMITC='U' AND WMqnta<>999999999999999'. I don't have a way to put this into the business view, it would have to be in the data selection, right? But, when in the data selection, it don't enter in the left join clause, the report doesn't bring the information about order number that are in the 3 tables, but don't enter in the condition 'AND WMITC='U' AND WMqnta<>999999999999999', for the table F3111, or isn't in the 3 tables, and it must be in the report.

Another way that I was thinking about, is to remake all the report, all the business views and search the informations on F3111 and F3112 with select/fetch next.

Someone have this little issue and know how to solve this?

Thanks!

Daniel.

 

[Adrian_Chimirel]

Daniel,
I suggest you create a business view with table F4801 left joined with F3111 on DOCO. Use this BSVW in your report.
Next, create a Version and specify its Data Selection on the ITC, QNTA, SRST, TRDJ and any other field you may feel you need.
Keep it simple.

 

[Daniel Prado]

Adrian, actually my report is exactly as you said, but with a view made with a join between F4801, F3111 and F3112, left joined by DOCO.

The problem is the left join clause with a join that is not a column of F4801.

The select that I have to transform into a report:

SELECT wamcu, wadoco, dbo.DateJ2G(WAtrdj, 103) AS DT, WASRST, walitm, max(wauorg/10000) AS QTD_SOLIC, wauom, max(WASOQS/10000) AS QTD_REPORT, max(WASOQS/10000)/max(wauorg/10000) AS Prop, sum(WMUORG/10000) AS QTD_BAIXAR, wmum, sum(WMUORG/10000)*max(WASOQS/10000)/max(wauorg/10000) AS QTD_Prop, sum(WMTRQT/10000) AS QTD_BAIXADA, sum(WMUORG/10000)*max(WASOQS/10000)/max(wauorg/10000)-sum(WMTRQT/10000) AS Dif, wmcpil, wmdsc1, WMLOCN, WMLOTN, WAUSER
<font color="red">FROM PRODDTA.f4801 LEFT JOIN proddta.f3111 ON WMDOCO=wadoco AND WMITC='U' AND WMqnta<>999999999999999</font>
LEFT JOIN proddta.f3112 ON WlDOCO=wadoco
WHERE WASRST<>91
AND WAtrdj between 113182 AND 113212
GROUP BY wamcu, wadoco, WAtrdj, WASRST, walitm, wauom, wmum, wmcpil, wmdsc1, WAUSER, WMLOCN, WMLOTN

With the left join with the 3 tables joined by the DOCO column, the query that return to me with the JDEDEBUG.log is this:

SELECT T0.WADOCO, T0.WACO, T0.WAMCU, T0.WAMMCU, T0.WASRST, T0.WATRDJ, T0.WAITM, T0.WALITM, T0.WAUORG, T0.WASOQS,
T0.WAUOM, T0.WAUSER, T1.WMDOCO, T1.WMITC, T1.WMCPIL, T1.WMDSC1, T1.WMLOCN, T1.WMLOTN, T1.WMUORG, T1.WMTRQT, T1.WMQNTA,
T1.WMUM, T1.WMUKID, T2.WLDOCO, T2.WLMCU, T2.WLOPSQ, T2.WLOPSC
<font color="red">FROM ( ( PS_DEV.TESTDTA.F4801 T0 LEFT OUTER JOIN PS_DEV.TESTDTA.F3111 T1 ON T0.WADOCO = T1.WMDOCO ) </font>
LEFT OUTER JOIN PS_DEV.TESTDTA.F3112 T2 ON T0.WADOCO = T2.WLDOCO ) WHERE ( ( T0.WASRST <> '91'
AND T0.WATRDJ BETWEEN 111001 AND 111031 ) AND <font color="red">( T1.WMITC = 'U' AND T1.WMQNTA <> 9999999999999989800.000000 ) )
</font>ORDER BY T0.WAMCU ASC,T0.WADOCO ASC,T0.WATRDJ ASC,T0.WASRST ASC,T0.WALITM ASC,T0.WAUOM ASC,T1.WMUM ASC,T1.WMCPIL ASC,
T1.WMDSC1 ASC,T0.WAUSER ASC,T1.WMLOCN ASC,T1.WMLOTN ASC

Then the report brings me only results that enter in this condition, and I need to recover DOCOs that are in the F4801 and enter in the condition of the left join clause with the columns requested of F3111 and F3112, and recover DOCOs that aren't in the condition of F3111 and F3112, but exists in the F4801 table. This is confused, I think that I shall create a view with only one table and the make some selects to recover the values that I need... I can't see other way for this issue...

 

[JMR]

SQL View will work for you. Search the List for SQL View or DB View and you should find many examples explaining the concept.

In a nutshell:
1) you create a database view in your DB using the CREATE VIEW command
2) give the view a name that follows E1 table naming convention (ex: F551234)
3) create a table object in OMW with the same name as the view
4) add all the necessary data items to the table in table design aid
5) NEVER generate the table...
6) create a BSVW object in OMW and add the new table as the only table in the view...select all columns

At this point your BSVW object will return the results as if the DB View query is executed.

NOTE!!!: When creating your DB View:
1) your SELECT columns must adhere to E1 column naming conventions for tables. That is, a 2-character prefix (defined in OMW for your E1 table) followed by a valid data dictionary item
2) your SELECT columns must be in the same order as the columns are defined in the E1 table design

For instance, let's say your E1 table is defined with a prefix of: TT.

You SELECT statement should do something like:
SELECT wamcu as TTMCU, wadoco as TTDOCO, etc, etc, etc...

EVERY column must be named with the table prefix followed by a valid and unique data dictionary item and the column order must match between the SQL statement and the table design.

You can consider leaving off the WHERE clause in your DB view...you can use data selection for any WHERE clause selection you need.

Good luck

 

[Daniel Prado]

Jeremy, thanks!

This works fine!

The only thing that I have to do that isn't equal what you said, is that I have to creat de table in the JDE, then delete the table through SQL and then create the SQL view. And then this works fine!

Another question involved with this: the CNC says that with this way we can't up the package, because the table in the JDE does not 'exists' really. Do you know how to do the package? We will have do to this for every environment?

Thanks!

Daniel

 

[JMR]

Daniel,

If you refer to #5 in my original post, you would know that you do NOT generate the table in E1. Ever. As you found out, it replaces the DB view with the E1 table.

Tell CNC that it is not necessary to generate the table in order to package the table object. All this technique is doing is providing E1 with the specifications of the table so it can be used with BSVW's and Fetch's, etc. A package does not require that you generate the E1 table. A package only holds the table specifications which are created when you add columns in table design.

I usually put the following text in the description of all my SQL view E1 tables:
DO NOT GENERATE!

And, yes, you will have to manually create the SQL view in each environment. But you can treat the E1 table object just as any other table object with respect to promotion, package building, and deployment.

Also, you have to make sure that the table owner matches the other tables in the DB. For instance, if you have a CRP environment and the DB owner for those tables is "crpdta", you should create view as crpdta.f55xxxxx

Also make sure you set permissions on the view to match permissions of all the other E1 tables.

 

[Scott Beebe]

As a rule I always generate the table on initial creation of the table spec that will be used as a virtual table. This creates the .H file that is handy sometimes. From OMW I REMOVE the table from the database, check it in. Then I create the SQL view in the DB.

After the initial generate and check in, the table should never be generated again.

 

[JMR]

That's not a bad idea to ensure you get the header file. I always do a manual header file generation as a habit.

 

[Daniel Prado]

Yes, this is what I have done. And it's works fine!

Thanks Jeremy and the other that helped me!