SOLVED - Test of SQL Server query works in test but not code WD 2024

I have a complex sql code in a Query that I can test with parameters and it works fine. When I make the same call from code, it does not produce the same results. The problem seems to be with passing lists for sustition for "IN" commands. I have a procedure that translates the list from xxx;xxx to 'xxx';'xxx' which copied to the test parameters of the query gives the correct results. I have tried using a comma instead semicolon burt it still gets the wrong results.I am using the native SQL Server connection. If I don't use the list parameters, it works.

My WD project is ANSI and the SQL Server database I am accessing is UNICODE. I am not an SQL expert.

For reference, here is my SQL code:

SELECT DISTINCT
	PURCHASE_ORDER.ID AS ID,
	PURCHASE_ORDER.VENDOR_ID AS VENDOR_ID,
	PURCHASE_ORDER.SHIPTO_ADDR_NO AS SHIPTO_ADDR_NO,
	PURCHASE_ORDER.ORDER_DATE AS ORDER_DATE,
	PURCHASE_ORDER.SHIP_VIA AS SHIP_VIA,
	PURCHASE_ORDER.BUYER AS BUYER,
	PURCHASE_ORDER.BACK_ORDER AS BACK_ORDER,
	PURCHASE_ORDER.TOTAL_AMT_ORDERED AS PO_AMT_ORDERED,
	PURC_ORDER_LINE.ROWID AS ROWID_POL,
	PURC_ORDER_LINE.LINE_NO AS LINE_NO,
	PURC_ORDER_LINE.PART_ID AS PART_ID,
	PURC_ORDER_LINE.ORDER_QTY AS ORDER_QTY,
	PURC_ORDER_LINE.PURCHASE_UM AS PURCHASE_UM,
	PURC_ORDER_LINE.unit_price AS unit_price,
	PURC_ORDER_LINE.TOTAL_AMT_ORDERED AS POL_AMT_ORDERED,
	PURC_ORDER_LINE.VENDOR_PART_ID AS VENDOR_PART_ID,
	PURC_ORDER_LINE.SERVICE_ID AS SERVICE_ID,
	SERVICE.Description AS SERVICE_DESCRIPTION,
	PURC_ORDER_LINE.LINE_STATUS AS LINE_STATUS,
	PURC_ORDER_LINE.LAST_RECEIVED_DATE AS LAST_RECEIVED_DATE,
	PURC_ORDER_LINE.DESIRED_RECV_DATE AS DESIRED_RECV_DATE,
	PURC_LINE_BINARY.ROWID AS ROWID_PLB,
	DEMAND_SUPPLY_LINK.DEMAND_TYPE AS DEMAND_TYPE,
	DEMAND_SUPPLY_LINK.DEMAND_BASE_ID AS DEMAND_BASE_ID,
	DEMAND_SUPPLY_LINK.DEMAND_SEQ_NO AS DEMAND_SEQ_NO,
	DEMAND_SUPPLY_LINK.SUPPLY_TYPE AS SUPPLY_TYPE,
	DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID AS SUPPLY_BASE_ID,
	DEMAND_SUPPLY_LINK.SUPPLY_SEQ_NO AS SUPPLY_SEQ_NO,
	DEMAND_SUPPLY_LINK.DEMAND_PART_ID AS DEMAND_PART_ID,
	DEMAND_SUPPLY_LINK.SUPPLY_PART_ID AS SUPPLY_PART_ID,
	DEMAND_SUPPLY_LINK.ALLOCATED_QTY AS ALLOCATED_QTY,
	DEMAND_SUPPLY_LINK.RECEIVED_QTY AS RECEIVED_QTY,
	DEMAND_SUPPLY_LINK.ISSUED_QTY AS ISSUED_QTY,
	DEMAND_SUPPLY_LINK.USER_ID AS USER_ID,
	OPERATION.OPERATION_TYPE AS OPERATION_TYPE,
	OPERATION.WORKORDER_BASE_ID AS WORKORDER_BASE_ID
FROM
	(
		(
			(
				PURCHASE_ORDER
				INNER JOIN
				PURC_ORDER_LINE
				ON PURCHASE_ORDER.ID = PURC_ORDER_LINE.PURC_ORDER_ID
			)
			LEFT OUTER JOIN
			PURC_LINE_BINARY
			ON PURC_LINE_BINARY.PURC_ORDER_ID = PURC_ORDER_LINE.PURC_ORDER_ID
			AND
			PURC_LINE_BINARY.PURC_ORDER_LINE_NO = PURC_ORDER_LINE.LINE_NO
			LEFT OUTER JOIN
			SERVICE
			ON SERVICE.ID	= PURC_ORDER_LINE.SERVICE_ID
		)
		LEFT OUTER JOIN
		DEMAND_SUPPLY_LINK
		ON PURC_ORDER_LINE.PURC_ORDER_ID = DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID
		AND
		PURC_ORDER_LINE.LINE_NO = DEMAND_SUPPLY_LINK.DEMAND_SEQ_NO
		AND
		DEMAND_SUPPLY_LINK.SUPPLY_TYPE = 'PO'
	)
	LEFT OUTER JOIN
	OPERATION
	ON DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID = OPERATION.WORKORDER_BASE_ID
WHERE 
	(
	PURCHASE_ORDER.VENDOR_ID IN ({pVendorIDList}) 
	AND	PURCHASE_ORDER.VENDOR_ID IN ({pNamesIDList}) 
	AND	PURCHASE_ORDER.ID = {pPurchaseOrderID}
	AND	PURCHASE_ORDER.ORDER_DATE BETWEEN {pOrderDateFrom} AND {pOrderDateTo}
	AND	PURC_ORDER_LINE.LINE_STATUS = {pStatus}
	AND	PURCHASE_ORDER.BUYER = {pBuyerID}
	AND	PURC_ORDER_LINE.PART_ID = {pPartID}
	AND	PURC_ORDER_LINE.LAST_RECEIVED_DATE BETWEEN {pLastReceivedDateFrom} AND {pLastReceivedDateTo}
	AND	PURC_ORDER_LINE.DESIRED_RECV_DATE BETWEEN {pDesiredDateFrom} AND {pDesiredDateTo}
	AND	PURCHASE_ORDER.BACK_ORDER = {pBackOrder}
	AND	PURC_ORDER_LINE.SERVICE_ID = {pServiceID}
	AND	PURC_LINE_BINARY.ROWID IN ({p_PLB_ROWID}) 
	AND	DEMAND_SUPPLY_LINK.DEMAND_TYPE = {pDEMAND_TYPE}
	AND	OPERATION.WORKORDER_BASE_ID IN ({pWORKORDER_BASE_ID_List}) 
	AND	DEMAND_SUPPLY_LINK.SUPPLY_TYPE = 'PO'
)
ORDER BY 
	ORDER_DATE DESC,	
	ID ASC,	
	LINE_NO ASC,	
	VENDOR_ID ASC

Any suggestions?

Jeff Graham
Cascade Consulting



Edited 2 time(s). Last edit at 01/13/2025 10:32PM by Jeff Graham.

Why the two lists of VENDOR_ID?
PURCHASE_ORDER.VENDOR_ID IN ({pVendorIDList})
AND PURCHASE_ORDER.VENDOR_ID IN ({pNamesIDList})

Regards

Paulo Oliveira

There was a reason for two initially because they serve two different purposes and if the user adds IDs it is one parameter. Then other parameter was from a search which is the allowed IDs.So the user entered ones must also be in the other parameter. But you are right on as that turned out to be the cause of the problem. So I moved that logic to WinDev code so there was only one parameter and it then worked.

Thanks for responding and my appologies for not repling sooner.