Alternative to try_convert for lower compatibility level on SQL Server 2008

Question

I am trying to run my query on a SQL Server 2008 where the compatibility level doesn't support try_convert().

SELECT TOP 1
    A.PTNTIDNO,
    A.LR,
    CASE WHEN ISNULL(A.ITEM1, '') = ''
        THEN (select ROUND(AVG(TRY_CONVERT(float, ITEM1)),3) from INFLABD)
        ELSE TRY_CONVERT(float,A.ITEM1, 3)
    FROM INFLABD AS A
    WHERE A.LR = 'R'
    AND A.PTNTIDNO = %s
    ORDER BY A.REGDATE DESC

Error message

File "src\pymssql_pymssql.pyx", line 478, in pymssql._pymssql.Cursor.execute pymssql._pymssql.OperationalError: (195, b"'TRY_CONVERT' is not a recognized built-in function name.DB-Lib error message 20018

Therefore, is there another alternative?

Follow your comment

cast('' as xml).value('sql:column("A.RIGHT01") cast as xs:decimal ?', 'decimal(28,10)')

It worked well

but add aggregate function 'AVG'

THEN (select ROUND(AVG(cast('' as xml).value('sql:column("A.RIGHT01") cast as xs:decimal ?', 'decimal(28,10)')),3) from OPERATIONDATA)

Error message

Column 'OPERATIONDATA.RIGHT01' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.DB-Lib error message 20018

Add I solve the problem just change 'A.RIGHT01' to 'RIGHT01'

And now I'm curious about the query

THEN (select ROUND(AVG(cast('' as xml).value('sql:column("RIGHT01") cast as xs:decimal ?', 'decimal(28,10)')),3) from OPERATIONDATA)

Can explain this query..? I don't know why this is working well

Is this only used as a alternative for when try_convert is not possible?
I learned sql but this code is never seen before. So can explain?

cast('' as xml).value('sql:column("RIGHT01") cast as xs:decimal ?', 'decimal(28,10)'

Thank you for your support.

Other companies are managing db, but compatibility is not solved. be looking for an alternative to — Tae In Kim, Jun 23 '22 at 01:55
do you mean the SQL Server that you are using does not support `TRY_CONVERT()`? — Squirrel, Jun 23 '22 at 02:00
yes, other db company work well but the other company not work this query. after searching google i found the problem is compatibility, but i can't request that company solve the problem — Tae In Kim, Jun 23 '22 at 02:02
So something like [this](https://stackoverflow.com/questions/17941035/try-convert-for-sql-server-2008-r2) then? — Dale K, Jun 23 '22 at 02:04
[Simulating TRY_CONVERT() in SQL Server 2008](https://www.mssqltips.com/sqlservertip/5411/simulating-tryconvert-in-sql-server-2008/) — Squirrel, Jun 23 '22 at 02:18
I'm trying your comment but xml? is working? Incorrect syntax near the keyword 'as' — Tae In Kim, Jun 23 '22 at 03:32

score 0 · Accepted Answer · answered Jun 23 '22 at 08:58

The problem is that A refers to the outer reference of INFLABD but you want to refer to the inner table. Effectively, what you need is sql:column("od.RIGHT01")

SELECT TOP 1
    A.PTNTIDNO,
    A.LR,
    ROUND(
        CASE WHEN ISNULL(A.ITEM1, '') = ''
        THEN (select AVG(cast('' as xml).value('sql:column("od.RIGHT01") cast as xs:decimal ?', 'decimal(28,10)') from OPERATIONDATA od)
        ELSE cast('' as xml).value('sql:column("A.ITEM1") cast as xs:decimal ?', 'decimal(28,10)')
      END, 3)
    FROM INFLABD AS A
    WHERE A.LR = 'R'
    AND A.PTNTIDNO = %s
    ORDER BY A.REGDATE DESC

Alternative to try_convert for lower compatibility level on SQL Server 2008

1 Answers1