E9.2 Data Request : possible to have a calculated returned columne

[bjouzier]

Hello,

Does anyone know if it is possible to have a calculated retunred column in an Orchestrator DataRequest component ?

I want to create a data request that produce the equivalent of SQL Query below :

Select sum(SDSOQS+SDSOBK) from F4211 where sddcto= 'SE' and sdnxtr between 530 and 575 group by SDITM,SDMCU,SDPDDJ

Of course with data aggregation I can obtain a result set with two separate columns sum(SOQS) and sum(SOBK) and sum columns in subsequent components. I should have preferred to do it in the Data Request source component, to avoid making it in several subsequent components.

Is it a functionnality available, I didn't found it (I am in tools 9.2.6.3) ? Perhaps is it available in newer release ?

 

[Kevin Long]

I am not aware of any functionality in a data request to sum two columns into a single result. In 9.2.7 you can use a Logic Extension to do what you want. Essentially, you can call a data request from within the LEX, manipulate the output, and return an array.

 

[jolly]

Hello,

Does anyone know if it is possible to have a calculated retunred column in an Orchestrator DataRequest component ?

I want to create a data request that produce the equivalent of SQL Query below :

Select sum(SDSOQS+SDSOBK) from F4211 where sddcto= 'SE' and sdnxtr between 530 and 575 group by SDITM,SDMCU,SDPDDJ

Of course with data aggregation I can obtain a result set with two separate columns sum(SOQS) and sum(SOBK) and sum columns in subsequent components. I should have preferred to do it in the Data Request source component, to avoid making it in several subsequent components.

Is it a functionnality available, I didn't found it (I am in tools 9.2.6.3) ? Perhaps is it available in newer release ?

I don’t believe so. Could be post processed into the result with a few lines of custom script.

 

[bjouzier]

Thanks a lot for these answer. I can manage it with the proposed solutions.

 

[JohnDanter2]

If you created a dropped SQL view and did an data request ver that it would 100% work.

So copy F4211 call it F554211D and add an extra column for your SUM and the drop it in in SQL and use the create as to replace the table on the database.
Each time it's run it will do th SQL for you and the exra column is your SUM.

bit OTT but I do this kind of stuff all the time for counting stuff

Example

drop table F554801V;
/
drop view F554801V;

create or replace VIEW F554801V
as


SELECT
wadoco AS pwdoco,
wadcto AS pwdcto,
wawr02 AS pwwr02,
wasrst AS pwsrst,
ibsrp7 AS pwsrp7,
ibprp4 AS pwprp4,
wawr01 AS pwwr01,
watyps AS pwtyps,
waitm AS pwitm,
wadl01 AS pwdl01,
wauorg AS pwuorg,
lpnqty AS pwy55cqty,
( wauorg - lpnqty ) AS pwuopn,
waansa AS pwansa,
lssrst AS pwa1st,
ibanpl AS pwanpl,
wamcu AS pwmcu,
wammcu AS pwmmcu,
waan8 AS pwan8
FROM
(
SELECT
wadoco,
wasrst,
wauorg,
SUM(lcqty) AS lpnqty,
wawr01,
wawr02,
ibsrp7,
ibprp4,
wadl01,
watyps,
waitm,
waansa,
lssrst,
ibanpl,
wamcu,
wammcu,
wadcto,
waan8
FROM
f4801
LEFT OUTER JOIN f55lpnc ON lcdoco = wadoco
AND lcdcto = wadcto
INNER JOIN f55lpns ON lcy55lpnid = lsy55lpnid
INNER JOIN f4102 ON ibitm = waitm
AND ibmcu = wammcu
GROUP BY
wadoco,
wasrst,
wauorg,
wawr01,
wawr02,
ibsrp7,
ibprp4,
wadl01,
watyps,
waitm,
waansa,
lssrst,
ibanpl,
wamcu,
wammcu,
wadcto,
waan8
ORDER BY
wadoco,
wasrst,
wauorg,
wawr01,
wawr02,
ibsrp7,
ibprp4,
wadl01,
watyps,
waitm,
waansa,
lssrst,
ibanpl,
wamcu,
wammcu,
wadcto,
waan8
)
WHERE
wauorg <> lpnqty
OR lpnqty IS NULL;

 

[DaveWagoner]

I call that "virtual table", if you spin up a table spec for the view it behaves as a true JDE table. Really useful for lots of stuff!

I was hoping the advanced query connector in Orch would let me do stuff like this but it's too limited. Doesn't allow things like derived tables, etc.

 

[JohnDanter2]

I like that term Dave and promise to start using it a lot more

 

[DaveWagoner]

I think I got the term (and the technique) from Kevin Courtney if he's on here Brilliant dev in the JDE space.

he is: @KevinCourtney

 

[bjouzier]

Hello,

Just to be sure to understand well, the proposed solution should be :

1 - create a view named F55xxxx in the database, at SQL level
2 - create an OMW object of type TBLE, matching the same name F55xxx and the same columns list
3 - create an orchestrator Data Request component based on pseudo-table F55xxx.

If so I am very please with the proposed technique. I used to make some complex views/procedures (partitioned aggregations , recursive cte to explore trees like multilevel Bill of materials, etc.) and it is a very interesting way to simply use these kind of views.

Thanks a lot.

 

[DaveWagoner]

That's it! I've used it for proper recursion of BOM, derived tables, and everything else.

Important to honor the datatypes and decimal values of the DD's you use in your "Virtual Table".

Also both shops I've been working at who use this method use another indicator in the name to denote that it's a virtual file. One shop used VF55***** and the other used F55****V