E9.2 BSVW left outer join behaviour

JohnDanter2

JohnDanter2

VIP Member
Hi folks

I have created a new custom view in JDE V554611B to help with a watchlist I need to make.
But when people are testing this in SQL they get totally different results and I've worked out why

There is a left outer join from F4611 to F00092 and if use SQL to replicate the BSVW and place the select values in the left outer join it works, if I place the join in the overall final WHERE statement it doesn't (in this case it matches the watchlist count linked to the APPL)
So any ideas (other than a dropped view in SQL) to help E1 force the values to select on in the left outer command not the overall where

This brings back the expected results as the 'where' is in the left outer join specifics

Code: 
select --* Watchlist for work order pick errors
 ibanpl, count(*)
from crpdta.f4611
inner join crpdta.f4102 on ibitm = r2itm and ibmcu = r2mcu
left outer join crpdta.f0101 on ibanpl = aban8
left outer join crpdta.f00092 on t3mcu = r2mcu and t3rmk2 = r2aisl and  t3sdb = 'BU' and t3tydt ='WA'

where r2tyfl = '2' and r2mcu = '         245' and r2pstb = '300' and r2ocde = 'WO'

group by ibanpl
order by ibanpl ;

vs this that only brings ONE record (F00092 matches) if the where is in the overall.

Code: 
select --* Watchlist for work order pick errors
 ibanpl, count(*)
from crpdta.f4611
inner join crpdta.f4102 on ibitm = r2itm and ibmcu = r2mcu
left outer join crpdta.f0101 on ibanpl = aban8
 left outer join crpdta.f00092 on t3mcu = r2mcu and t3rmk2 = r2aisl
where r2tyfl = '2' and r2mcu = '         245' and r2pstb = '300' and r2ocde = 'WO' and  t3sdb = 'BU' and t3tydt ='WA'

group by ibanpl
order by ibanpl ;

But we have no way to do that in E1. The values used in the select will always be added to the final where

Code: 
VIEW:    V554611B                
SYSTEM CODE:    55                
DESCRIPTION:    RRD JET BSVW for WO suggestions                                              
TYPE    Standard                
                     
TABLES:                    
F4611    Warehouse Suggestions                
F4801    Work Order Master File                
F41021    Item Location File                
F4102    Item Branch File                
F00092    Supplemental Data                
F0101    Address Book Master                
                     
ALIAS    DESCRIPTION    TYPE    SIZE    DECIMALS    TABLE    KEY
DOCO    Document (Order No, Invoice, etc.)    Math Numeric    8    0    F4611(0)
DOCO    Document (Order No, Invoice, etc.)    Math Numeric    8    0    F4801(0)
DCTO    Order Type    String    2    0    F4611(0)
DCTO    Order Type    String    2    0    F4801(0)
ITM    Item Number - Short    Math Numeric    8    0    F4611(0)
ITM    Item Number - Short    Math Numeric    8    0    F41021(0)
MCU    Business Unit    String    12    0    F4611(0)
MCU    Business Unit    String    12    0    F41021(0)
FLOC    Location - From    String    20    0    F4611(0)
LOCN    Location    String    20    0    F41021(0)
FLOT    Lot - From    String    30    0    F4611(0)
LOTN    Lot/Serial Number    String    30    0    F41021(0)
ANPL    Customer Number    Math Numeric    8    0    F4102(0)
AN8    Address Number    Math Numeric    8    0    F0101(0)
MCU    Business Unit    String    12    0    F00092(0)
AISL    Aisle    String    8    0    F4611(0)
RMK2    Name - Remark    String    30    0    F00092(0)
ITM    Item Number - Short    Math Numeric    8    0    F4102(0)
MCU    Business Unit    String    12    0    F4102(0)
SGBT    Batch Number - Suggestions    Math Numeric    8    0    F4611(0)    X
SGSQ    Sequence Number - Suggestion    Math Numeric    7    0    F4611(0)    X
SDB    Supplemental Database Code    String    4    0    F00092(0)
TYDT    Type Data    String    2    0    F00092(0)
SBA1    Supplemental Data Alpha Key 1    String    10    0    F00092(0)
KCO    Document Company    String    5    0    F00092(0)
SBA2    Supplemental Data Alpha Key 2    String    10    0    F00092(0)
SBN1    Supplemental Data Numeric Key 1    Math Numeric    8    0    F00092(0)
SBN2    Supplementat Data Numeric Key 2    Math Numeric    8    0    F00092(0)
UKID    Unique Key ID (Internal)    Math Numeric    15    0    F00092(0)
OCDE    Code - Origin    String    2    0    F4611(0)
RQBT    Batch Number - Requests    Math Numeric    8    0    F4611(0)
RQSQ    Sequence Number - Request    Math Numeric    7    0    F4611(0)
PHAS    Phase    Char    1    0    F4611(0)
TQTY    Quantity -Total    Math Numeric    15    0    F4611(0)
UOM1    Unit of Measure - Primary    String    2    0    F4611(0)
DSC1    Description    String    30    0    F4611(0)
DSC2    Description - Line 2    String    30    0    F4611(0)
DTCR    Date - Created (Julian)    Date    6    0    F4611(0)
EKEY    Error Message Key    String    4    0    F4611(0)
RCDS    Status - Record    Char    1    0    F4611(0)
PSTB    Status - Task / Trip    String    3    0    F4611(0)
LNTY    Line Type    String    2    0    F4611(0)
USER    User ID    String    10    0    F4611(0)
UPMJ    Date - Updated    Date    6    0    F4611(0)
TDAY    Time of Day    Math Numeric    6    0    F4611(0)
URAB    User Reserved Number    Math Numeric    8    0    F4611(0)
SRST    Status Code W.O.    String    2    0    F4801(0)
ANSA    Address Number - Manager    Math Numeric    8    0    F4801(0)
UORG    Units - Order/Transaction Quantity    Math Numeric    15    0    F4801(0)
URAB    User Reserved Number    Math Numeric    8    0    F4801(0)
WR01    Mfg Line    String    4    0    F4801(0)
TYPS    Type - W.O.    Char    1    0    F4801(0)
STRT    Date - Start (Julian)    Date    6    0    F4801(0)
WR02    Priority    String    3    0    F4801(0)
ITM    Item Number - Short    Math Numeric    8    0    F4801(0)
RMK    Name - Remark    String    30    0    F00092(0)
LOTS    Lot Status Code    Char    1    0    F41021(0)
PQOH    Quantity on Hand - Primary units    Math Numeric    15    0    F41021(0)
ALPH    Name - Alpha    String    40    0    F0101(0)
TYFL    Code - Warehouse    Char    1    0    F4611(0)
ZONR    Zone - Replenishment    String    6    0    F4611(0)
KZON    Zone - Picking    String    6    0    F4611(0)
PZON    Zone - Putaway    String    6    0    F4611(0)
PACK    Code - Packing    String    4    0    F4611(0)
URCD    User Reserved Code    String    2    0    F4801(0)
                     
JOINED COLUMNS:                    
F4611(0)    DOCO    =Left Outer(*=)    F4801(0)    DOCO    
F4611(0)    DCTO    =Left Outer(*=)    F4801(0)    DCTO    
F4611(0)    ITM    =Left Outer(*=)    F41021(0)    ITM    
F4611(0)    MCU    =Left Outer(*=)    F41021(0)    MCU    
F4611(0)    FLOC    =Left Outer(*=)    F41021(0)    LOCN    
F4611(0)    FLOT    =Left Outer(*=)    F41021(0)    LOTN    
F4102(0)    ANPL    =Simple(==)    F0101(0)    AN8    
F4611(0)    MCU    =Left Outer(*=)    F00092(0)    MCU    
F4611(0)    AISL    =Left Outer(*=)    F00092(0)    RMK2    
F4611(0)    ITM    =Simple(==)    F4102(0)    ITM    
F4611(0)    MCU    =Simple(==)    F4102(0)    MCU
 
Last edited:
Good info! We are forced to use "advanced sql" to get what we want-- in pre-release 23 we are forced to use the sql-view-as-jde-table hack (or sql in a groovy script step, very yuck), and in r23 we are supposedly given the ability to bring our own sql to a standard orch component. If you're like me you are maybe holding off on going all in on the sql-as-table trick because of a hope of sooner than later upgrade :)
 
Try changing your join from a "SQL 92 Left Outer" to a "Left Outer" (or vice-versa) in the BSVW design aid, use it in an APPL or something where you can capture the debug log or JAS debug log and look at the generated SQL. One of those options will add a "OR ISNULL" clause to the WHERE clause for the joined table for any filter criteria so you should effectively get:

Code: 
select --* Watchlist for work order pick errors
 ibanpl, count(*)
from crpdta.f4611
inner join crpdta.f4102 on ibitm = r2itm and ibmcu = r2mcu
left outer join crpdta.f0101 on ibanpl = aban8
 left outer join crpdta.f00092 on t3mcu = r2mcu and t3rmk2 = r2aisl
where r2tyfl = '2' and r2mcu = '         245' and r2pstb = '300' and r2ocde = 'WO'
and  (t3sdb = 'BU' OR t3sdb ISNULL)
and (t3tydt ='WA' OR  t3tydt ISNULL)

group by ibanpl
order by ibanpl ;

...or something to that effect. That may get you what you want in that it will return everything from F4611 and only matching records from F00092 where sdb="BU" and ydt='WA' which is what I think you are trying to accomplish.

JDE SQL is limited (the BSFN C APIs in JDEBASE add myriad new options, but is still very limited). One of the biggest limitations is that you can't create subqueries... which is really what I think you want in this situation and what you effectively did with your first query. But you still may be able to accomplish what you want with JDE "out of the box" SQL.
 
Didn't work Larry, tried that too (unless CNC didn't refresh and deploy it correctly)

The issue is any where claus that comes after a view declares joins is classed as a simple join. SQL basics I was told :)

I'm, up aganst the clock so just did it using a dropped view in SQL for now. I'd like to come back to it as this will occur again no doubt in another project. I thought the 92 join would work but it doesn't
 
Update on this

We can't make a dropped view over an E1 view as the database won't ever use views in the SQL anyway. If you have a view over F4801 and F0101 called V554801A for example, you will never see an SQL expression using V554801, only a join to F4801 and F0101.

So making a dropped view over my V554611B E1 view was kind of pointless, it would never work.

So to fix this what I will do is create a new monster table, F554611B listing ALL the columns in my original V554611B view from all the tables I used.
THEN I will create a dropped view over F554611B usng the SQL left outer I wanted to achive above.

Wish me luck :)
 
This SQL to drop and replace the view works like an absolute charm.
so step 1 create the all encompassng table and note duplicate aliases need a substitute DD item (MCU and MCU1 for example)

SQL B
drop table TESTDTA.F554611B;
/
drop view TESTDTA.F554611B;

CREATE OR REPLACE VIEW TESTDTA.F554611B AS
SELECT

T3KCO as JWKCO, T3MCU as JWMCU2, T3RMK as JWRMK, T3RMK2 as JWRMK2, T3SBA1 as JWSBA1, T3SBA2 as JWSBA2, T3SBN1 as JWSBN1,
T3SBN2 as JWSBN2, T3SDB as JWSDB, T3TYDT as JWTYDT, T3UKID as JWUKID,
ABALPH as JWALPH, ABAN8 as JWAN8 ,
IBANPL as JWANPL, IBITM as JWITMM, IBMCU as JWMCU3,
LIITM as JWITML, LILOCN as JWLOCN , LILOTN as JWLOTN , LILOTS as JWLOTS , LIMCU as JWMCU1, LIPQOH as JWPQOH,
WAANSA as JWANSA , WADCTO as JWDCT4 , WADOCO as JWDOC0, WAITM as JWITMR, WASTRT as JWSTRT, WATYPS as JWTYPS, WAUORG as JWUORG, WAURCD as JWURCD,
WAWR01 as JWWR01 , WAWR02 as JWWR02, WASRST as JWSRST, WAURAB as JWUKURAB,
R2AISL as JWAISL, R2DCTO as JWDCTO, R2DOCO as JWDOCO, R2DSC1 as JWDSC1, R2DSC2 as JWDSC2,
R2DTCR AS JWDTCR, R2EKEY AS JWEKEY, R2FLOC AS JWFLOC, R2FLOT AS JWFLOT, R2ITM AS JWITM,
R2KZON AS JWKZON, R2LNTY AS JWLNTY, R2MCU AS JWMCU, R2OCDE AS JWOCDE, R2PACK AS JWPACK,
R2PHAS AS JWPHAS, R2PSTB AS JWPSTB, R2PZON AS JWPZON, R2RCDS AS JWRCDS, R2RQBT AS JWRQBT,
R2RQSQ AS JWRQSQ, R2SGBT AS JWSGBT, R2SGSQ AS JWSGSQ, R2TDAY AS JWTDAY, R2TQTY AS JWTQTY,
R2TYFL AS JWTYFL, R2UOM1 AS JWUOM1, R2UPMJ AS JWUPMJ, R2URAB AS JWURAB, R2USER AS JWUSER,
R2ZONR AS JWZONR

from TESTDTA.f4611

inner join f4102 on ibitm = r2itm and ibmcu = r2mcu
inner join f4801 on r2doco = wadoco and r2dcto = wadcto
inner join f41021 on r2flot = lilotn and r2floc = lilocn and r2itm = liitm and r2mcu = limcu
left outer join f0101 on ibanpl = aban8
left outer join f00092 on t3mcu = r2mcu and t3rmk2 = r2aisl and t3sdb = 'BU' and t3tydt ='WA';
 

Similar threads

Amith
E9.2 Filter does not work when view has left outer join
Replies
19
Views
659
Amith
Amith
WorldWriter - Data mapping error on member Fxxxx.
Replies
0
Views
3K
Frosty the Coder
Linking F4211 to F4311
Replies
5
Views
5K
SALopez
Changing Business View from Simple Join to Left Outer Join
Replies
3
Views
3K
euane
euane
SQL join to F03B11
Replies
0
Views
2K
cbudoris
Back
Top