BSVW with UDC - does not sequence

[Sef]

This issue is driving me insane and I wonder if I am missing something or if this is an undocumented (or poorly documented) feature.

I am writing a UBE with a BSVW on a custom table and a custom UDC. The join is NOT defined via KY (because of the issue of spaces in front of these values in UDC table F0005). Instead the UBE contains a section where the trimmed KY field is copied into the program ID. The custom table is joined to this PID field in the BSVW.

Using this BSVW in the UBE is successfull and all records are retrieved correctly. The reason for the join is that I wish to sequence the report by the special handling code of the UDC. However which ever way I try, the sequencing is fully ignored!?!?. I have tried to use other sequences (eg TDAY). I have also tried to make F0005 the primary table, but all to no avail.

Is there a restriction I am not aware of. Eg that you can't sequence by an element of a control table?
Is this a tools bug?
Has anybody else seen this issue?

Any help in this area is greatly appriciated.

System specs: ERP8.0 Update 1, SP 23 on NT/Sequel

 

[Zoltan_Gyimesi]

Hi Sef,

I just try to guess.
Maybe it is a bug - and my assumption about the reason of it is, that the too table are in different data sources.

To demonstrate it make a experiment on your test / development environment.

Copy F0005 to the Business Data OR copy your custom table to the Control Tables data source temporarily and change the OCM settings of the table accordingly.
Log off, log on OW and check, whether does the sequencing work or not.

I am eager to know your results.

If this way the sequencing works, then consider to use an other custom rable instead of F0005 and create a simple application to maintain it.

Read you later & Regards,

Zoltán

 

[Sef]

Hi Zoltán

Thank you for your suggestion. My only plausible explanation was the same as yours. A BSVW over tables in multiple sources may have issues. However I would have expected some documentation to exist or to have heard of fellow developers that have seen this issue before.
Nevertheless I might try and run a few additional tests (on Friday when I am back at this particular client).

Btw are you keeping cool in Budapest? I hear from my brother in Buda there is a heatwave. Can you please keep some of the warmner weather for December when I will be visiting

 

[GSI Inc.]

Have you turned on logging to see how the SQL statement is formatted? Curious ~ Angelis

 

[BBritain]

This is a very, very longshot. I had a sequence problem, long long time ago because one of the files was being cached. I can't remember what the program name is of the application which identifies the files being cached, but I do remember that UDC tables are usually cached.

You might try uncaching the UDC tables - keeping in mind that they are cached for speed purposes, and see if that helps.

again, this is a longshot.
Ben again,

 

[Bill Dotson]

The application is P98613, and the UDC tables are cached by default, but that still seems like a longshot. If it's possible to simulate the BSVW with SQL statements, you might gain some insight into the problem.

 

[Sef]

Thanks guys for your suggestions. I tried our Hungarian Friend's suggestion, but to no avail. I'll have a closer look at caching and SQL statements on Friday.

 

[Null Value]

Maybe it is a control table field issue.

The only JDE report that I found which comes remotely close to sequencing on F0005 is R79802 - "Check Status with Text Records".

The section "F00051 Left Join F0005 All Fields" (2nd from top to save you the trouble) uses Business View V00051LJ which is a join between F00051 and F0005. However it uses the SY, RT and KY fields of F00051 and not F0005.

F0005 is being used as a join with another table in only two other views - V0004A and V9647J1. And these views are not used in any report. An unreported (but known within JDE) issue ?

Regards,

Shailesh

 

[WhippingBoy]

I once did something similar to this, but I went at it from another angle. Copy the custom UDC from F0005 to a new custom F55005 (which is just a copy of F0005) and strip those blanks out of KY. Now you can do a normal join to the KY value.

Each time the program starts it runs through the appropriate F0005 records and looks for matches in F55005 (blanks taken into account). If a value isn't found, it's moved over. Likewise, a search is made going the other way, so the custom table doesn't load up with fictitious data. Certainly, a hack job of sorts, but the trade-offs were well worth it. And, it worked

 

[Sef]

I finally got around to looking at the SQL and much to my amazement the initial SQL only takes the Custom Table into account!

SELECT * FROM PRODDTA.F5542PDM WHERE ( PDEFTJ <= 107222 AND PDEXDJ >= 107215 ) ORDER BY PDCLV ASC,PDDEAL ASC,PDAN8 ASC,PDAC12 ASC,PDAC13 ASC

No mention of the UDC table and as a result no sequencing by any element in F0005.

It must be because of seperate data sources, but once again I am puzzled why this has not been documented/discussed before

The same report has a different section. Again with a joined BSVW and sequencing by elements out of both tables. Its SQL is correct:

SELECT T0.SDKCOO, T0.SDDOCO, T0.SDDCTO, T0.SDLNID, T0.SDAN8, T0.SDSHAN, T0.SDDRQJ, T0.SDIVD, T0.SDDGL, T0.SDVR01, T0.SDITM, T0.SDLITM, T0.SDLNTY, T0.SDEMCU, T0.SDSRP5, T0.SDUOM, T0.SDUORG, T0.SDSOQS, T0.SDAEXP, T0.SDPTC, T0.SDDOC, T0.SDDCT, T0.SDTAX1, T0.SDTXA1, T0.SDEXR1, T0.SDPQOR, T0.SDSLSM, T0.SDURAT, T1.CMAN8, T1.CMCCTY, T1.CMCEFJ, T1.CMCXPJ, T1.CMPWRD, T1.CMDCTO, T1.CMCPCT FROM PRODDTA.F42119 T0,PRODDTA.F42004 T1 WHERE ( ( T0.SDSLSM <> 0.000000 AND T0.SDIVD >= 107182 AND T0.SDIVD <= 107212 AND T1.CMPWRD <> ' ' AND T1.CMCEFJ <= 107212 AND T1.CMCXPJ >= 107212 ) ) AND ( T0.SDSLSM=T1.CMAN8 ) ORDER BY T1.CMPWRD ASC,T0.SDSLSM ASC ,T0.SDSRP5 ASC,T0.SDDOC ASC,T0.SDDCT ASC,T0.SDVR01 ASC,T0.SDIVD ASC,T0.SDSHAN ASC

I might try and run a similar test at a different client in 8.11. The issue may be tools/release dependent?

 

[JEMILLER]

Sef,

The issue could very well be tools release dependent. In earlier versions/release I know that the middleware treated cross-datasouce joins as "distributed" whether or not the schemas referenced lived within the same database instance. If the query is being treated as a distributed join then the tables may be read in separately and joined by the middleware. In 8.94 tools and later I thought that some intelligence had been implemented so that a cross-datasource join would be implemented as a single SQL statement when it was detected that the two datasource lived within the same database instance. Whether that has made it in to SP23 or not I don't know.

As mentioned by others, another possible factor is JDB caching. Both F0004 and F0005 are cached. However, my understanding of the caching is that it shouldn't affect joins like this. It comes into play during direct record fetches. As records are requested by key value the middleware checks the cache for a matching record. If it finds one it returns the cached contents. If a match is not found the record is retrieved from the db. If the F0005 is to be part of the query itself the cache shouldn't be involved. It may be something hardcoded when dealing with the F0005. I wouldn't put it past our grand E1 engineers. There is a fair amount of hard coding around certain tables.

When you tried Zoltan's suggestion you copied the F0005 over to Business Data and changed your OCM mapping, right? If this is related either to caching or something hard coded in the system related to UDC's I was thinking that instead of a custom table and a maintenance app you could go with the "virtual table" solution we have discussed on the list many times. Create a view in PRODDTA called something like "F550005" over the physical table PRODCTL.F0005. You could even put a TRIM function in the SQL for your view. This should then let you join by KY directly.

 

[Sef]

Great reply Justin!

I will now definitely try the same setup in 8.11 with tools release 8.96.
I'll do this on Wednesday. Keep you posted.

 

[peterbruce]

Sef,

Here's a suggestion that is a little "left field" and uses Whippingboy's custom table idea. Recently I used "virtual tables" (see thread 122695) where I used a sql to create a database view I accessed from JDE. If you do the same thing - use a sql to create a database view, stripping out the leading spaces, you should be able to do your sequencing.

 

[Aarto]

HI Sef!

I see there's been a lot of replies already but i would like to add a something.. Could the sequencing perhaps work if F0005 was the left (primary) table in your Business view

Just a thought...

 

[Sef]

The result of the test on 8.11 Sp1, 8.96 Cum1 on Oracle 10g is..........

Same Problem!

The issue still exists in the latest tools release and the selection SQL created, selects data from the custom table only!

Changing the primary table in the BSVW, as has been suggested, has no affect.

So I am back to square one.

Friday i will attempt to create a virtual table.

keep you posted