Solved

(SQL) ERROR: Argument data type sql_variant is invalid for argument 1 of like function.

Posted on 2016-10-13
9
25 Views
Last Modified: 2016-10-13
Hi Guys,

I getting Error: Argument data type sql_variant is invalid for argument 1 of like function.


DECLARE @Tbl1 as TABLE ([VALUE] int)	
BEGIN
	INSERT into @Tbl1 Select 

// how to fix this check if transformId is int and value is string
	[VALUE]	= CASE WHEN d.transformId = 22 AND f.value like 'Test_file%' then d.transformId END	

FROM documents d left join fValues f on f.docid = d.doctId
END
SELECT count([VALUE]) FROM @Tbl1

Open in new window


Thank You.
0
Comment
Question by:Darius
  • 5
  • 3
9 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41841713
Can you post your table structure of documents and fValues.
0
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
ID: 41841717
SQL_Variants won’t work with LIKE in a WHERE clause. Try.

1.

DECLARE @Tbl1 AS TABLE ([VALUE] INT)	

	INSERT INTO @Tbl1 
	Select CASE WHEN d.transformId = 22 AND CAST(f.value AS VARCHAR(1000)) like 'Test_file%' THEN d.transformId END
	FROM documents d left join fValues f ON f.docid = d.doctId

SELECT count([VALUE]) FROM @Tbl1

Open in new window


2.

--

DECLARE @Tbl1 as TABLE ([VALUE] INT)	

INSERT into @Tbl1 	
SELECT CASE WHEN d.transformId = 22 AND CHARINDEX(CAST(f.value AS VARCHAR(1000)), 'Test_file' , 0) > 0 THEN d.transformId END	
FROM documents d LEFT JOIN fValues f on f.docid = d.doctId

SELECT COUNT([VALUE]) FROM @Tbl1
--

Open in new window

0
 

Author Comment

by:Darius
ID: 41841728
Pawan,
both queries return 0 values. Definitely I have many transformId(22) and values (Test_file%)on this transform.

Documents table:
docId(PK,bigint,not null)
dateAdded(datetime, not null)
transformId(int, not null)

fValues Table:
fieldValuesId(PK,bigint, not null)
docId(bigint, not null)
values(sql_variant, null)

Thanks
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41841729
Okies, let me work on this..
1
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 500 total points
ID: 41841735
Here is the complete script.


--


CREATE TABLE Documents
(
	 docId bigint not null
	,dateAdded datetime not null
	,transformId int not null
)
GO

CREATE TABLE fValues
(
	 fieldValuesId bigint Not null
	,docId bigint not null
	,[values] sql_variant
)
GO

INSERT INTO Documents VALUES (1,GETDATE(),22)
GO

INSERT INTO fValues VALUES (101,1,'Test_file_Pawan')
GO

--

Open in new window


1. Solution 1

DECLARE @Tbl1 as TABLE ([VALUE] INT)	

INSERT into @Tbl1 	
SELECT CASE WHEN d.transformId = 22 AND CHARINDEX('Test_file' , CAST(f.[values] AS VARCHAR(1000)), 0) > 0 THEN d.transformId END Vals
FROM documents d LEFT JOIN fValues f on f.docid = d.docId

SELECT COUNT([VALUE]) FROM @Tbl1

GO

Open in new window


2. Solution

--

DECLARE @Tbl1 as TABLE ([VALUE] INT)	

INSERT into @Tbl1 
SELECT CASE WHEN d.transformId = 22 AND CAST(f.[values] AS VARCHAR(1000)) LIKE 'Test_file%' THEN d.transformId END Vals
FROM documents d LEFT JOIN fValues f on f.docid = d.docId

SELECT COUNT([VALUE]) FROM @Tbl1
--

Open in new window


Output

--------------

Vals
22
0
 

Author Comment

by:Darius
ID: 41841743
Thanks!

Second solution works for me.
And it works previous second solution as well. Sorry I did mistake by providing wrong transform number.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41841745
Great that it works for you !! Enjoy ..
0
 

Author Comment

by:Darius
ID: 41841746
Dhanyawad
1
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41841751
values(sql_variant, null)
Why are you using sql_variant? This means that values than store any kind of data type so when is not char the like operation will fail.

For your issue you'll need to only execute the LIKE operation for varchar data type so you'll need to add the following filter:
DECLARE @Tbl1 as TABLE ([VALUE] int)	
BEGIN
	INSERT into @Tbl1 
        Select  [VALUE] = CASE 
                      WHEN d.transformId = 22 AND f.value like 'Test_file%' then d.transformId 
                      END	
        FROM documents d 
                  left join fValues f on f.docid = d.doctId
        WHERE SQL_VARIANT_PROPERTY(f.value,'BaseType') = 'VARCHAR'
END
SELECT count([VALUE]) FROM @Tbl1

Open in new window

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Convert column to int 13 27
Shop sales Actual vs Forecast 2 26
SQL Server memory Issue 7 74
Slow SQL query 12 21
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now