sql_variant fields and the WHERE-query

dagviggo

I'm stuck with this anoying problem on a NAV 5.01 database in SQL server (the database has the "Case sensitive" option enabled).

The problem is that I'm unable to search for items that contain letters in the Item field "No." using the WHERE-statement. Searching for items with a numeric item no. is no problem.

This works:
SELECT [No_]
,[Description]
FROM [My Database].[dbo].[My Company$Item]
WHERE [No_] = 1000

This fails:
SELECT [No_]
,[Description]
FROM [My Database].[dbo].[My Company$Item]
WHERE [No_] = 'abc123'

I've tried all sorts for casts without any success, like this:
WHERE [No_] = cast('abc123' as varchar(20))

(I thought Navision fields of type "sql_variant" = varchar in SQL ... but I guess not ...)

It still returns 0 records :-(

DenSter

dagviggo wrote:

the database has the "Case sensitive" option enabled

try:

SELECT [No_]
      ,[Description]
  FROM [My Database].[dbo].[My Company$Item]
  WHERE [No_] = 'ABC123'

dagviggo

Sorry, really really bad example from my part, I've searched using captiols also ;-)

A better example that doesn't work either:

WHERE [No_] = '07-0101-715-5'

ara3n

try this

SELECT [No_]
      ,[Description]
  FROM [My Database].[dbo].[My Company$Item]
  WHERE [No_] = '07-0101-715-5'  
  COLLATE SQL_Latin1_General_CP1_CI_AS

dagviggo

No difference, I've also tried with "Latin1_General_100_CI_AS".

Note that I only have this problem with fields that are defined as "sql_variant" in SQL ... like the "No." and "No. 2" fields in the Item table.

Search on other fields works fine, like this one:
WHERE [Description] LIKE '%Flash%'

DenSter

I'd just open the table in SQL Server and look at how the values are formatted. If a query on 'ABC123' returns nothing, then there is no record with that value, and it must be formatted differently. If you're concerned about SQL data type, you can take a look at (but not change!!) the table design within SQL Server to see what the data type is.

dagviggo

This really puzzles me, I installed the database on a different SQL server and there it works ?!?

A search like this (on the new server) works fine:
WHERE (No_ = '01-0206-411-7')

I can't find any differences between the two SQL servers, but there must be a problem with my local server.

PS! The field type CODE(20) from Navision is shown in SQL as "sql_variant"

I'll try to reinstall my local SQL server ...


Thanks for all help so far :-)

dagviggo

FINALLY!!!

Can't believe I didn't try that before. The "cast"-statement should be on the other side ... of course:

WHERE cast([No_] as varchar(20)) = '09CARFST PORA'

Thanks Ola!

:-)

ara3n

Thanks for sharing a solution. I'm sure somebody will run into this in future.

DenSter

It always comes down to properly converting datatypes doesn't it If the [No_] field is a 'sqlvariant' datatype, then your original WHERE clause could then be:

WHERE [No_] = cast('abc123' as sql_variant)