Business View Question

[sbecker]

I am trying to pull all of the bills that have been rebilled. I can do this in sql but I do not know enough about Business Views to port the sql or if it is even possible. The way I would do it in sql is:

select rpvr01, count(*)
from proddta.f03b11
group by rpvr01
having count(*) > 1

Any ideas or is it even possible? Thanks

 

[Adrian_Chimirel]

sbecker,
I would:
- create a table in PEO, using OMW, and give it a fancy name such as F5503B11D (from duplicate bills in F03B11), and add your fields
Do NOT generate this table.
- create the DB View (based on your SQL Statement), and give it the same name F5503B11D - this is how we fool PEO, make it believe that your DB View is a "Real PEO" table.
- next, create the PEO BSVW V5503B11DA, seeing your table, with ALL its fields,
- finally create the UBE, or APPL, whichever makes you comfortable, or you may create them BOTH, ... and voila, it's done.
Warm regards,

 

[sbecker]

That is a genius idea. I would have never thought of that. I still have a lot to learn about PSF development. Thanks a lot.

 

[Adrian_Chimirel]

Yes it is - but I am NOT that genius; I've got it from The JDEList, sooooooo Long Live The JDEList!

 

[Gergely_Pongracz]

Hi Adrian,
This is a really good idea but do you happen to know if it is DB-system dependent? Once I tried something similar with MS-SQL and it didn't work... I thought (maybe wrongly) that an Open Table operation by JDE recognized that it was just a view...
Thanks!
Gergely Pongrácz
e-Best, Hungary

 

[Adrian_Chimirel]

I have personally applied it on both MSSQL & Oracle (not on DB2/UDB as of yet), but I would think it is DB-INdependent.
PS1 You may do table I/O with business views, too.
PS2 I have attached Saad Mian's original document - enjoy.
Warm regards,