Incorrect syntax near the keyword 'FROM' when using MS SQL Server 2012

koki · July 26, 2021, 11:39am

I’ve a simple transformation with AS:LookupValueEx function, but it ends with error. I’m not sure if it is implemented in HTML5 version. It works well in flash version. The concerned transformation part looks like this:

	<xsl:template match="ROOT">
		<ROOT>
			<xsl:variable name="productId" select="'371ab9a1-a07f-4a7e-908b-3cb17ba11966'"/>
			<xsl:variable name="discountProfile1Id" select="''"/>
			<xsl:variable name="siteId" select="'0cccfab0-b2f5-45c2-b4e3-fa4b3f91e606'"/>
			<xsl:variable name="date" select="'2021-07-01'"/>
			<xsl:variable name="isSpecialOffer" select="'false'"/>
			<xsl:variable name="producerId" select="'83ac9df9-3538-4207-af1e-9c987c74fef5'"/>
		
			<xsl:variable name="params1">
					<parameter key="PriceDiscountProfileDetail_parDiscountProfileId" value="{$discountProfile1Id}"/>
					<parameter key="PriceDiscountProfileDetail_parSiteId" value="{$siteId}"/>
					<parameter key="PriceDiscountProfileDetail_parDate" value="{$date}"/>
					<parameter key="PriceDiscountProfileDetail_parProductId" value="{$productId}"/>
					<parameter key="PriceDiscountProfileDetail_parProducerBusinessPartnerId" value="{$producerId}"/>
			</xsl:variable>

			<xsl:variable name="discountPct1">
				<xsl:choose>
					<xsl:when test="$isSpecialOffer = 'true'">
						<xsl:value-of select="AS:isnull(AS:LookupValueEx('f9f810aa-f157-415f-ac89-49091970b5bb', $params1), 0)"/>
					</xsl:when>
					<xsl:otherwise>
						<xsl:value-of select="AS:isnull(AS:LookupValueEx('07766b7c-adf6-4ad8-ab90-fcee7e4b0274', $params1), 0)"/>
					</xsl:otherwise>
				</xsl:choose>
			</xsl:variable>
		
			<xsl:attribute name="dp1">
				<xsl:value-of select="$discountPct1" />
			</xsl:attribute>
		</ROOT>

error message is:

Failed getting lookup text for lookup: 07766b7c-adf6-4ad8-ab90-fcee7e4b0274
Transformation result invalid.
Exception happened during transformation. See inner exception for details:

An error occurred during a call to extension function 'LookupValueEx'. See InnerException for a complete description of the error.
Failed getting lookup text for lookup: 07766b7c-adf6-4ad8-ab90-fcee7e4b0274
Exception was encountered while getting scalar value.  
Incorrect syntax near the keyword 'FROM'.
Incorrect syntax near the keyword 'FROM'.
Incorrect syntax near the keyword 'DESC'.
Incorrect syntax near the keyword 'FROM'.
Incorrect syntax near the keyword 'FROM'.
Incorrect syntax near the keyword 'DESC'.

The Lookup Value method expects 4 parameter. When I run the query in SQL console (query generated from Architect), there is no issue.

DECLARE @PriceDiscountProfileDetail_parDate AS DateTime;
DECLARE @PriceDiscountProfileDetail_parDiscountProfileId AS UniqueIdentifier;
DECLARE @PriceDiscountProfileDetail_parProducerBusinessPartnerId AS UniqueIdentifier;
DECLARE @PriceDiscountProfileDetail_parProductId AS UniqueIdentifier;
DECLARE @PriceDiscountProfileDetail_parSiteId AS UniqueIdentifier;

SET @PriceDiscountProfileDetail_parProducerBusinessPartnerId = '83ac9df9-3538-4207-af1e-9c987c74fef5'
SET @PriceDiscountProfileDetail_parProductId = '371ab9a1-a07f-4a7e-908b-3cb17ba11966'
SET @PriceDiscountProfileDetail_parDate = '2021-07-01'
SET @PriceDiscountProfileDetail_parSiteId = '0cccfab0-b2f5-45c2-b4e3-fa4b3f91e606'
SET @PriceDiscountProfileDetail_parDiscountProfileId = NULL


-----------------------------------------------------------------
-- PriceDiscountProfileDetail
-----------------------------------------------------------------
SELECT
	[PriceDiscountProfileDetail].[refProductId] AS [refProductId],
	[PriceDiscountProfileDetail].[refSiteId] AS [refSiteId],
	[PriceDiscountProfileDetail].[DiscountPct] AS [DiscountPct],
	[PriceDiscountProfileDetail].[Priority] AS [Priority],
	[PriceDiscountProfileDetail].[Id] AS [Id],
	[PriceDiscountProfileDetail].[refProducerBusinessPartnerId] AS [refProducerBusinessPartnerId]
FROM
	[PriceDiscountProfileDetail] AS [PriceDiscountProfileDetail]
WHERE  (
	 ([PriceDiscountProfileDetail].[IsValidForSpecialOffers] = 1) AND
	 ((([PriceDiscountProfileDetail].[refProducerBusinessPartnerId] IS NULL OR [PriceDiscountProfileDetail].[refProducerBusinessPartnerId] = @PriceDiscountProfileDetail_parProducerBusinessPartnerId) AND ([PriceDiscountProfileDetail].[refSiteId] = @PriceDiscountProfileDetail_parSiteId OR [PriceDiscountProfileDetail].[refSiteId] IS NULL) AND [PriceDiscountProfileDetail].[refDiscountProfileId] = @PriceDiscountProfileDetail_parDiscountProfileId AND @PriceDiscountProfileDetail_parDate BETWEEN [PriceDiscountProfileDetail].[ValidFrom] AND [PriceDiscountProfileDetail].[ValidUntil] AND ([PriceDiscountProfileDetail].[refProductId] = @PriceDiscountProfileDetail_parProductId OR [PriceDiscountProfileDetail].[refProductId] IS NULL))));

Any help appreciated! Thank you.

tvavrda · July 26, 2021, 12:48pm

There seem to be sorting used at the same time. Can you check how the query looks when you display SQL of the sort set? Turning on debug for data service or tracing SQL Server commands could also help to reveal how the final query looks like.

koki · July 26, 2021, 1:22pm

There is no SortSet on that Datastructure.
The DA.Service.AbstractSqlDataService debug log shows:

2021-07-26 15:17:43,649 [45] DEBUG Origam.DA.Service.AbstractSqlDataService - SQL Command; Connection ID: 63, Transaction ID: , SELECT TOP 1 [PriceDiscountProfileDetail].[DiscountPct] AS [DiscountPct] FROM [PriceDiscountProfileDetail] AS [PriceDiscountProfileDetail] WHERE  (  ((([PriceDiscountProfileDetail].[refProducerBusinessPartnerId] IS NULL OR [PriceDiscountProfileDetail].[refProducerBusinessPartnerId] = @PriceDiscountProfileDetail_parProducerBusinessPartnerId) AND ([PriceDiscountProfileDetail].[refSiteId] = @PriceDiscountProfileDetail_parSiteId OR [PriceDiscountProfileDetail].[refSiteId] IS NULL) AND [PriceDiscountProfileDetail].[refDiscountProfileId] = @PriceDiscountProfileDetail_parDiscountProfileId AND @PriceDiscountProfileDetail_parDate BETWEEN [PriceDiscountProfileDetail].[ValidFrom] AND [PriceDiscountProfileDetail].[ValidUntil] AND ([PriceDiscountProfileDetail].[refProductId] = @PriceDiscountProfileDetail_parProductId OR [PriceDiscountProfileDetail].[refProductId] IS NULL)))) ORDER BY  (SELECT TOP 1 TRIM( ', ' FROM ISNULL (CAST ([Product].[ReferenceCode] AS NVARCHAR(MAX) ), '') + ', ' + ISNULL (CAST ([Product].[Name] AS NVARCHAR(MAX) ), '')) FROM [Product] AS [Product] WHERE  (  ([PriceDiscountProfileDetail].[refProductId] = [Product].[Id])) ) DESC, (SELECT TOP 1 TRIM( ', ' FROM ISNULL (CAST ([BusinessPartner].[ReferenceCode] AS NVARCHAR(MAX) ), '') + ', ' + ISNULL (CAST ([BusinessPartner].[LookupText] AS NVARCHAR(MAX) ), '')) FROM [BusinessPartnerLookup] AS [BusinessPartner] WHERE  (  ([BusinessPartner].[Id] = [PriceDiscountProfileDetail].[refProducerBusinessPartnerId])) ) DESC, (SELECT TOP 1 [Site].[Name] AS [Name] FROM [Site] AS [Site] WHERE  (  ([PriceDiscountProfileDetail].[refSiteId] = [Site].[Id])) ) DESC, [PriceDiscountProfileDetail].[Priority] DESC
2021-07-26 15:17:43,649 [45] DEBUG Origam.DA.Service.AbstractSqlDataService - Parameter: @PriceDiscountProfileDetail_parDiscountProfileId Value: 
2021-07-26 15:17:43,649 [45] DEBUG Origam.DA.Service.AbstractSqlDataService - Parameter: @PriceDiscountProfileDetail_parProducerBusinessPartnerId Value: 
2021-07-26 15:17:43,649 [45] DEBUG Origam.DA.Service.AbstractSqlDataService - Parameter: @PriceDiscountProfileDetail_parSiteId Value: 0cccfab0-b2f5-45c2-b4e3-fa4b3f91e606
2021-07-26 15:17:43,649 [45] DEBUG Origam.DA.Service.AbstractSqlDataService - Parameter: @PriceDiscountProfileDetail_parDate Value: 1/1/1900 12:00:00 AM
2021-07-26 15:17:43,649 [45] DEBUG Origam.DA.Service.AbstractSqlDataService - Parameter: @PriceDiscountProfileDetail_parProductId Value: 371ab9a1-a07f-4a7e-908b-3cb17ba11966
2021-07-26 15:17:43,670 [Workflow eca06f1a-8710-416e-a7ff-9a6269ea6aa6] DEBUG Origam.DA.Service.AbstractSqlDataService - UpdateData; Data Structure Id: 4985a6b2-8bae-4c21-9a09-0c2480c4efe2
2021-07-26 15:17:43,670 [Workflow eca06f1a-8710-416e-a7ff-9a6269ea6aa6] DEBUG Origam.DA.Service.AbstractSqlDataService - UpdateData; <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
  <OrigamTraceWorkflowStepData>
    <OrigamTraceWorkflowStep diffgr:id="OrigamTraceWorkflowStep1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
      <refOrigamTraceWorkflowId>eca06f1a-8710-416e-a7ff-9a6269ea6aa6</refOrigamTraceWorkflowId>
      <WorkflowStepId>ec0fd40b-0a0c-4992-8510-c3defded2596</WorkflowStepId>
      <WorkflowStepPath xsi:type="xs:string" xs:xmlns="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">TTD_Billing_Finish/02_ForEachFarmer/05_GenerateInvoice</WorkflowStepPath>
      <Category>Process</Category>
      <Subcategory>Error</Subcategory>
      <Message xsi:type="xs:string" xs:xmlns="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">Merge context 'Document_Complete', Step 'TTD_Billing_Finish/02_ForEachFarmer/05_GenerateInvoice' failed. InputContextValue: . Original exception message: Rule evaluation failed. Rule: 'DocumentBillingDetail_CalculateTotals'
Alternativní množství není možné zadat, pokud není vybrán produkt.</Message>
      <RecordCreated>2021-07-26T15:17:43.669799+02:00</RecordCreated>
      <RecordCreatedBy>3fb325e6-76ed-4a44-84ca-84e1597ef539</RecordCreatedBy>
      <Id>5dd6d209-325f-405a-93cf-8c5201431e5f</Id>
    </OrigamTraceWorkflowStep>
  </OrigamTraceWorkflowStepData>
</diffgr:diffgram>

koki · July 26, 2021, 2:06pm

I’m sorry. There is SortSet on that Datastructure an it is used in the Lookup Value part only. When I remove it, it started to work. But the SortSert is essential for that lookup. It is not complicated SortSet - 4 field sorting. This SortSet works well in FLASH version. Any suggestion would be appreciated. Thank you!

tvavrda · July 27, 2021, 1:15pm

It works for me. What version of SQL Server are you using?

koki · July 27, 2021, 1:23pm

SQL server 11.0.6251 - SQL 2012 SP3

tvavrda · July 27, 2021, 1:34pm

The TRIM function we use in order to render multi-column lookup values in SQL is supported from Microsoft SQL Server 2017.

The only way how to make it working will be to change the lookups on the sorted columns to not to have multiple columns in the ValueDisplayMember or to remove the default lookups from those entity fields altogether.

koki · July 27, 2021, 1:46pm

There is only one column in the ValueDisplayMember and that lookup is not used as Default Lookup on Entity field, but it is not working as well.

tvavrda · July 27, 2021, 2:13pm

Business Partner and Product fields (mentioned in the sort set) have default lookups. That is why they produce the TRIM function in the resulting SQL.

koki · July 27, 2021, 2:22pm

So you mean any Default lookup on Entity filed with multiple columns in ValueDisplayMember causes error, if the AS:LookupValueEx function is calling Lookup with SortSet, which is trying to get data from that Entity?

tvavrda · July 27, 2021, 2:27pm

It does not depend on where from it is called. A sort set with columns that have a lookup with multiple columns in ValueDisplayMember produces TRIM function in the SQL, which is supported from MSSQL 2017 and up.

koki · July 27, 2021, 4:18pm

I can create UDF (user-defined function) TRIM as combination of LTRIM and RTRIM, but it has to be called as dbo.TRIM. Upgrading SQL server is not possible as version 2012 is still supported by Microsoft. Is there a way from your side to implement backward compatibility with older SQL server or add some parameter config file to set the compatibility?

tvavrda · July 28, 2021, 10:02am

I don’t think it is possible to replicate the TRIM function with UDF. The easiest would be to get rid of the lookups with multiple columns on those sorted fields.