Troubleshooting Issue with Where Clause

[CathyC]

I have a scenario which has a few things I need help with.

I have a GetAvailability method within GetItemDetails and GetAvailability has an exception:
Name cannot begin with the '=' character, hexadecimal value 0x3D. Line 2, position 22.

AW GetAvailability From GetItemDetailsLogs.PNG (150.22 KiB) Viewed 661 times

AW GetAvailability From GetItemDetailsLogs.PNG (150.22 KiB) Viewed 661 times

Viewing from within the GetAvailability tab, it shows no services ran

AW GetAvailability Logs.PNG (83.74 KiB) Viewed 661 times

AW GetAvailability Logs.PNG (83.74 KiB) Viewed 661 times

And I get the same error working in the method canvas trying to "run" or "run in step".
QUESTION 1: Is there a way to point at the specific service that is the source of the issue in the logs?

To troubleshoot, I opened another method canvas and tried to copy all the services to the other canvas so I could delete the services one by one until it ran. Then I would know which one had the incorrect syntax causing the issue.
BUG 1: it looks like I'm only able to copy 1 service at a time. If I try more, the source canvas still has the loading wheel and the place I try to paste it says "Failed to paste"

I went through copying one by one and seeing if it still runs each time
BUG 2: I noticed binary decisions past with their Yes/No locations reset

AW Binary Decisions YesNo Swapped.png (26.03 KiB) Viewed 661 times

AW Binary Decisions YesNo Swapped.png (26.03 KiB) Viewed 661 times

I found the service that had the issue. It is called GetAllBranchPlantQuantities and it has an issue with this where clause:

AW GetAllBranchPlantQuantities Where Clause.PNG (94.01 KiB) Viewed 661 times

AW GetAllBranchPlantQuantities Where Clause.PNG (94.01 KiB) Viewed 661 times

Here is the text of the where Clause
QUESTION 2: From the screenshot, is it telling me what is wrong with it and I am just missing it? I can put this in the where clause using blocks and filters, but I would also expect to be able to paste it into the where clause and have that work

Code: Select all

Where (
	( 
		((COALESCE(IOMMEJ, 0) >= <xsl:value-of select="WorkData/GetCurrentDateTime/Output/Result/JDEDate"/> AND
		COALESCE(IODLEJ, 0) <= <xsl:value-of select="WorkData/GetCurrentDateTime/Output/Result/JDEDate"/>)
		OR
		(COALESCE(IOMMEJ, 0) = 0 AND
		COALESCE(IODLEJ, 0) = 0))
		AND
		(<xsl:for-each select="WorkData/Items/Output/Result">
		    <xsl:variable name="Itm" select="ItemNumber" />
		    
			(IBLITM = '<xsl:value-of select="ItemNumber"/>' 
		    <xsl:choose>
		        <xsl:when test="/WorkData/Prevalues/Output/Result/BranchPlantFilterMode='One'"> and LTRIM(LIMCU) = '<xsl:value-of select="BranchPlant"/>'</xsl:when>
		        <xsl:when test="/WorkData/Prevalues/Output/Result/BranchPlantFilterMode='Many'"> and LTRIM(LIMCU) IN (<xsl:value-of select="/WorkData/ApprovedBPList/Output/Result[ItemNumber=$Itm]/ApprovedBPList"/>) </xsl:when>
		        <xsl:otherwise></xsl:otherwise>
		    </xsl:choose>)
		    <xsl:if test = "position() != last()"> OR </xsl:if>
		</xsl:for-each>) 
	)
)

BUG 3: not a big deal, but when I drag over the recommended value and have something highlighted I want it to replace, it just concats it instead

AW Dragging Recommended Values.png (58.16 KiB) Viewed 661 times

AW Dragging Recommended Values.png (58.16 KiB) Viewed 661 times

[CathyC]

So once I opened the bad service once, after that it did show with an "!" on the canvas:

AW Bad Service Showing Exclamation Point.PNG (21.23 KiB) Viewed 658 times

AW Bad Service Showing Exclamation Point.PNG (21.23 KiB) Viewed 658 times

I converted the where clause as much as I can (I think) to use the blocks and filters. When I have the service open though it still says there is an xml error.

AW Where Clause Fixed But Still Has Error.PNG (64.7 KiB) Viewed 658 times

AW Where Clause Fixed But Still Has Error.PNG (64.7 KiB) Viewed 658 times

Though now that the bad service is gone, the method runs. Now its just this query that throws an exception. In the logs I can see the query it is attempting and it looks wrong compared to how the where clause is built:

Code: Select all

<Query>
	select  IBITM as ShortItemNumber, LTRIM(LIMCU) as BranchPlant, SUM(LIPQOH - LIHCOM - LIPCOM) as AvailableQty, IBLITM as ItemNumber, IBSAFE as SafetyStock from JDE_Development.TESTDTA.F41021
	LEFT JOIN JDE_Development.TESTDTA.F4102
	ON JDE_Development.TESTDTA.F41021.LIITM = JDE_Development.TESTDTA.F4102.IBITM
	AND JDE_Development.TESTDTA.F41021.LIMCU = JDE_Development.TESTDTA.F4102.IBMCU
	LEFT JOIN JDE_Development.TESTDTA.F4108
	ON JDE_Development.TESTDTA.F41021.LIITM =
	JDE_Development.TESTDTA.F4108.IOITM
	AND JDE_Development.TESTDTA.F41021.LILOTN =
	JDE_Development.TESTDTA.F4108.IOLOTN
	AND JDE_Development.TESTDTA.F41021.LIMCU =
	JDE_Development.TESTDTA.F4108.IOMCU WHERE (( (COALESCE(IOMMEJ, 0) >= 122043 AND  COALESCE(IODLEJ, 0) <= 122043) OR ( (COALESCE(IOMMEJ, 0) = 0 AND  COALESCE(IODLEJ, 0) = 0)) AND ((IBLITM = '500821' and LTRIM(LIMCU) = '1157')
	     OR (IBLITM = '501008' and LTRIM(LIMCU) = '1157')
	     OR (IBLITM = '501017' and LTRIM(LIMCU) = '1157')
	     OR (IBLITM = '507789' and LTRIM(LIMCU) = '1157')
	    ) and ((COALESCE(IOMMEJ, 0) >= 122043 AND
	COALESCE(IODLEJ, 0) <= 122043)
	OR
	(COALESCE(IOMMEJ, 0) = 0 AND
	COALESCE(IODLEJ, 0) = 0))
	AND
	(
	
		(IBLITM = '500821'
	     and LTRIM(LIMCU) = '1157')
	OR
	
		(IBLITM = '501008'
	     and LTRIM(LIMCU) = '1157')
	OR
	
		(IBLITM = '501017'
	     and LTRIM(LIMCU) = '1157')
	OR
	
		(IBLITM = '507789'
	     and LTRIM(LIMCU) = '1157')
    ) group by IBLITM, IBITM, LIMCU, IBSAFE </Query>

The query format should be:

WHERE
(
([Date logic using todays filter]
OR
[Date logic using dates set to 0])
AND
(["or" separated Branch Plant and Item filters])
)
GROUP BY IBLITM, IBITM, LIMCU, IBSAFE

[CathyC]

I talked with Justin on this

For bug #1 if you hit copy a second time with multiple services selected it should copy properly this time. We are planning to go ahead and get both of these bugs fixed and they have tasks for them.

Task 10126 was created for the where clause issue where the filters are used twice

The rest of the issues were mismatched parenthesis which had invalid syntax

[SumanPrasad]

These tasks were raised:
1. 10392 : Binary decision copy and paste bug.: On Priority
2. 10393: WhereClause running twice after upgrade.
3. 10394: Multiple copy and paste : On Priority
10126 was closed.
Suman

[JustinVanRegenmorter]

All the tasks have been resolved in previous revisions.