Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-10-13
9
Medium Priority
?
227 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 35

Expert Comment

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

Accepted Solution

by:
Pawan Kumar earned 2000 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41841729
Okies, let me work on this..
1
 
LVL 35

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 2000 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 35

Expert Comment

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

Author Comment

by:Darius
ID: 41841746
Dhanyawad
1
 
LVL 52

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 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