Query Issue

[JohnW]

This query runs fine in SQL Server Manager but fails in Stimulsoft. Any idea why. It gives a data mismatch error, which is not seen in SQL Server Management. I have tried numerous variations of the query. They all fail. The goal is to get a valid sort on a string of this type AGEAGE000028899, AGEAGE000028607, etc

Thanks John W
------------

Code: Select all

select OnlineRenewalBuyersCard.LicenseeName,OnlineRenewalBuyersCard.Iden,OnlineRenewalBuyersCard.PermitTypeFee,OnlineRenewalBuyersCard.permitNumber, permitTypeCode, OnlineRenewalBuyersCard.invoiceKey, Cast(transactionDate As Date) AS TransactionDate, 
	RemittanceDetail.transactionConfirmationID,RemittanceDetail.AmountDue,Cast(RemittanceDetail.PaymentEffectiveDate AS Date) AS PaymentEffectiveDate,RemittanceDetail.invoicekey as remitInvoice 
from OnlineRenewalBuyersCard 
left join RemittanceDetail on OnlineRenewalBuyersCard.invoiceKey = remittanceDetail.InvoiceKey
where 
transactionDate between @dateFrom and @dateTo
AND OnlineRenewalBuyersCard.PermitStatus <> 'Void'
ORDER BY TRY_CAST(
    RIGHT(
        remittanceDetail.transactionConfirmationID,
        LEN(remittanceDetail.transactionConfirmationID) 
        - NULLIF(PATINDEX('%[^0-9]%', remittanceDetail.transactionConfirmationID), 0) + 1
    ) AS INT
)

[Lech Kulikowski]

Hello,

Does the issue occur without @dateFrom and @dateTo parameters?

Thank you.

[JohnW]

I have been able to get it run. What I find is the order by is failing. When I view the data in Stimulsoft query editor it show the sort working. When I see the data in the report it is lost.

See image:

[Lech Kulikowski]

Hello,

Please send us a sample report with test data that reproduces the issue for analysis.

Thank you.

[JohnW]

Is there a way to include the sample data with the report. Currently the data sits in a SQL Server db without external access.

[Lech Kulikowski]

Hello,

In the Dictionary, you can use Actions - Embed all data resources option.

Thank you.