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>)
Evento 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!!!
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>)
Evento 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!!!