Custom Business View Problem

[MagarG]

Trying to duplicate a World report for Time Entry for Work Orders.

Three tables: F0618 (A-Employee Time Entry), F060116 (B-Emp Master), F4801 (C-Work Orders). In file A, the Subledger field contains the Work Order# as Alpha. In file C, it's the Doc# Numeric. In World they joined A to B on Supplier# then used a Where clause, Where digits(subledger#) in file A = Doc# (in File C) and also Service Group in File C equals 'IT'.

I created a Business View in OneWorld, A to B then just had C sitting there. I'm trying first to get the Service Group = 'IT' to work. The report will preview fine if no data selection is in there. If I try to add data selection in the new report, it won't run. Added:

Where Service Group (File C) eq 'IT'

Get the error:
Unable to locate table in specified datasource for section: Join F0618-F060116-F4801

SQL in the log looks like this:
SELECT T0.YTAN8, T0.YTPRTR, T0.YTSBL, T0.YTWR01, T0.YTPHRW, T0.YTRCPY, T0.YTDWK, T1.YAAN8, T2.WADOCO, T2.WADL01, T2.WASTRX, T2.WAWR02 FROM MHSWRK/F0618 T0,MHSWRK/F060116 T1,MHSWRK/F4801 T2 WHERE ( T2.WAWR02 = 'IT' ) AND ( T0.YTAN8=T1.YAAN8 ) ORDER BY T0.YTAN8 ASC,T0.YTDWK ASC,T0.YTPRTR ASC

How can I join the F4801 if the fields are not the same type? Work arounds? I need to sort by fields in File C also, I found some posts where Table I/O won't help because I can't use the fields to sort. Help, thanks.

Grant.

 

[rexgregbr]

I don't think you'll get to join table C with the others, because the Business View Designer won't allow this join due to different datatypes. There's no easy workaround here.

Since you need to sort by the columns in table C, you're right, you can't use Table I/O.

I've once stomped into the same issue and my solution was: create a new table to be a temporary one, then load my data into this table (and in this process I would get the data selection I needed and use code to fetch the appropriate records and insert into the temp table), then display it in a grid and allow for it to be sorted. Problem with that is that it's not instant (I could only sort to refine my recordset, not to get a new recordset from a different data selection - for that I would have to reload the data). But in my case, it was fine.

Not the best way, but a way.

 

[MagarG]

The more I get into this, I'm finding you're right in there's no easy work around.

Creating another table might be an option. I know on the AS400 we can also have logical files that join two or more files but I may run into the same problem on the mismatched data type.

Thanks for the suggestions.

 

[BBritain]

You can also create a database view (see archives) where the SQL statement performs the conversion. Name this database view the same name you will use for a custom table. Create a custom table in - uh is it EnterpriseOne now? - with the exact same fields and the same name. Then build a business view on the table. I'm sure somebody can fill in the blanks as I'm going offline for awhile.

Ben again,

 

[Sef]

Hi Grant

A possible alternative, be it a bit out of left field, is to create a new table with two columns: doco and sbl. You either populate the table with all values from 1 - 00000001 to 99999999 - 99999999 or if feasible a smaller range
or
create an F4801 table trigger which populates this table every time a new WO is raised.

You can now use this table in your business view to join F0616 to the new table and the new table to F4801

Worth a try?

 

[Ian_Simmons]

Ben's idea is the one I would have suggested. You can create a virtual table by defining a View (CREATE VIEW) in SQL, and using a select within it that joins two tables on a type conversion. I can't remember the syntax as I don't have access to a machine right now, but to create a view:

CREATE VIEW TESTDTA/Fxxxxx AS
SELECT Field1, Field2 FROM TESTDTA/FILE1 A, TESTDTA FILE2 B
WHERE A.Field1 = B.Field1
AND CHAR(A.NumField2) = B.ChrField2

You may have to rename the result fields, but I know that if they are the same as the prefix + alias in your table specs, the middleware won't be any the wiser and will assume that it is a table.

Again, I haven't tried the character conversion in this scenario. Would be interested to know if it works.