Transformation Of SQL Queries in JDE

MumbaiBlues

Active Member
Hi all,
I'm having a Functional Specs containing all SQL Queries
which is transformed into d JDE Code...
like 4 example following are the Queries....

1.Query for Priority WO # 1 generated
SELECT F4801.WAPRTS AS Priority, F1217.WRZE01, Count(F4801.WADOCO) AS [# Work Order]
FROM F4801 INNER JOIN F1217 ON F4801.WANUMB = F1217.WRNUMB
WHERE (((F4801.WATRDJ) Between#08/01/2005# and #08/31/2005#) AND ((F4801.WASRST) Between "05" And "85"))
GROUP BY F4801.WAPRTS, F1217.WRZE01
HAVING (((F4801.WAPRTS)="1"));

2.Query for Priority WO # 2 generated
SELECT F4801.WAPRTS AS Priority, F1217.WRZE01, Count(F4801.WADOCO) AS [# Work Order]
FROM F4801 INNER JOIN F1217 ON F4801.WANUMB = F1217.WRNUMB
WHERE (((F4801.WATRDJ) Between#08/01/2005# and #08/31/2005#) AND ((F4801.WASRST) Between "05" And "85"))
GROUP BY F4801.WAPRTS, F1217.WRZE01
HAVING (((F4801.WAPRTS)="2"));

which in Transformed into the Piece of Code as:
Event:Initialize Section

Set Selection Append Flag(<Yes>)
Set User Selection(BC Date-Order/Transaction(F4801), <Greater than or equal to>,POFromDate<And>)
Set User Selection(BC Date-Order/Transaction(F4801), <less than or equal to>,POToDate<And>)
Set User Selection(BC Status Code W.O (F4801), <Greater than or equal to>,05<And>)
Set User Selection(BC Status Code W.O (F4801), <less than or equal to>,85<And>)
Set User Selection(BC Asset Item Number (F4801), <equal to>,BC Asset Item Number (F1217)<And>)

Event:Do Section
If BC Priority-W.O(F4801) is equal to “1”
RV Priority_1= [RV Priority_1+1]
End If

If BC Priority-W.O(F4801) is equal to “2”
RV Priority_2= [RV Priority_2+1]
End If

My Doubt is that how do we Convert the Having,Order n Group By Clause and the Operators like In and Between...
the above piece of code is not mine...
Can anybdy tell me how do we n on wat basis do we convert the Query...
wat are the thing that we should keep in Mind...
also can anybdy help me out with the Query which is:
1.Cost on HP
SELECT F1217.WRZE01, (Sum([F0911].[GLAA])/100) AS [Total WO Cost], F0911.GLLT FROM (F4801 INNER JOIN F0911 ON F4801.WAPARS = F0911.GLSBL) INNER JOIN F1217 ON F4801.WANUMB = F1217.WRNUMB
WHERE (((F4801.WASRST) Between "10" And "90") AND ((F1217.WRPRODM) In ('CAD',' CAE','CGD',' CGE',' CGT',' FNB',",' GND'",' GNS',' GNT',' HEP',' HEW',' HRSG',' PDG','PEL',' PPU',' PTR',' QAB')) AND ((F0911.GLDGJ) Between (#08/01/2006#) And (#08/31/2006#)))GROUP BY F1217.WRZE01, F0911.GLLT HAVING (((F0911.GLLT)="XA"));
be transformed into a code....

Thanks in advance!!!
 
Some principles :

A/ join are treated in views objects and not by "Set user Selection"
--> "Set User Selection(BC Asset Item Number (F4801), <equal to>,BC Asset Item Number (F1217)<And>)" is not mandatory

B/ HAVING is like WHERE, so to be treat in a "Set User Selection" (or "section selection")

C/ "Group BY" are "Level Break Footer" sections

D/ "LIKE" can only be treated in section selection with "range" option

E/ "IN" is an option in section selection or withh "OR" is "Set User Selection" but it will be hard to code

F/ BETWEEN is an option in section selection or "greater than" and "less than" in "Set User Selection" as you did with dates in your example

good luck
 
Hi Charles,
Thanks a lot....bt have a doubt that u have mentioned section selection...does it mean the User Data Selection at the Run time....
 
yes, it is.

I have some difficulties to image what is the purpose of your converter and I think you must use Sections too and not only Event rules.
 
Back
Top