in my select query shows errors

8y1.1kLearn iOS Programming

i create one function string split. i am calling that function in below select query.

My function name is dbo.StringSplits

SELECT [dbo.StringSplits](A.farm_detailsdata),A.farmer_id,A.farmer_season,A.farm_visitno

FROM dbo.tbl_farmer_farmdetails A

INNER JOIN dbo.tbl_farmerregistration B ON B.farmerctscode = A.farmer_id

WHERE B.farmerseason = '2018'

When i execute the above query shows error as follows

Incorrect syntax near 'A'.

what is mistake in my above query. please help me.

15 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajeev Punhani8y ago
Hi Narasiman ,

You are getting this error because you are using double single quotes for the varchar field and it is clear from error that you can not apply subtraction on varchar.

Instead of using double single quotes ,using single quotes once where ever you have used them.

replace
''-''(used 3 places in your query screen shot) with '-'
'''' with ''
+1
Nitin Sontakke8y ago
I am not able to open the .rar file. I tried with 7Zip and WinZip.

You can even use 'insert image' function. Second button from right on first row of buttons in reply editor.
+1
8y ago
i send the screen shot of my error.

Please find the attachment and let me know.
+1
Nitin Sontakke8y ago
Please paste the screen shot of SQL Server Management Studio with BOTH the query and the error.
+1
8y ago
i also changed the function name as [dbo].[string_split] still i am getting error in my below query.

SELECT FARM.item,A.farmer_id,
A.farmer_season,A.farm_visitno
FROM dbo.tbl_farmer_farmdetails A
INNER JOIN dbo.tbl_farmerregistration B ON B.farmerctscode = A.farmer_id
CROSS APPLY [dbo].[StringSplits](A.farm_detailsdata,',') AS FARM
WHERE B.farmerseason = '2018'

When i run the above query shows error as follows

Operand data type varchar is invalid for subtract operator.

please let me know what is the mistake in my above sql query. i tried for several it shows the same error only. please help me.
+1
Nitin Sontakke8y ago
The error is coming from your function.

I still don't understand why you are not using function provided by Microsoft SQL Server itself.

I have already provided link to you. Just change function name to [dbo].[string_split].
+1
8y ago
SELECT FARM.item,A.farmer_id,
A.farmer_season,A.farm_visitno
FROM dbo.tbl_farmer_farmdetails A
INNER JOIN dbo.tbl_farmerregistration B ON B.farmerctscode = A.farmer_id
CROSS APPLY dbo.StringSplits(A.farm_detailsdata,',') AS FARM
WHERE B.farmerseason = '2018'

When i run the above query shows error as follows

Operand data type varchar is invalid for subtract operator.

what is mistake in my above query. please help me.
+1
Rajeev Punhani8y ago
Hi Narasiman,

What you are trying to achieve is that you have a tabled value function that return multiple rows when you call a function that splits column values seperated by a seperator.

Please try the below query.
SELECT  FARM.item ,A.farmer_id,A.farmer_season,A.farm_visitno

FROM dbo.tbl_farmer_farmdetails A

INNER JOIN dbo.tbl_farmerregistration  B ON B.farmerctscode = A.farmer_id

CROSS APPLY dbo.StringSplits(A.farm_detailsdata,',') AS FARM

WHERE B.farmerseason = '2018'
+1
Nitin Sontakke8y ago
In that case, it has both built in features, string_split and json parsing. You don't have to develop your own solutions. Please go through following links:

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017
+1
8y ago
sql server version 2017

sql server managment studio
0
Nitin Sontakke8y ago
On another thread, I had asked you, which version of SQL Server are you using? and you didn't respond.
+1
Nitin Sontakke8y ago
Two things:

First, you have made a typing mistake, you should type [dbo].[StringSplits] but you have typed [dbo.StringSplits]. Can you see the difference after the word dbo?

Second, even if you modify that, you will still not be able to execute that query because you are mixing a table returning function with another select statement which is not allowed. Just try to visualize how SQL Server should show you the result and you will know what i mean.

Are you with me?
+1
8y ago
string split is function name

String split function as follows

Create FUNCTION [dbo].[StringSplits](@input NVARCHAR(MAX), @delimiter CHAR(1)=',')

RETURNS @returnTable TABLE(item NVARCHAR(100)) AS

BEGIN

IF @input IS NULL RETURN;

DECLARE @currentStartIndex INT, @currentEndIndex INT,@length INT;

SET @length=LEN(@input);

SET @currentStartIndex=1;

SET @currentEndIndex=CHARINDEX(@delimiter,@input,@currentStartIndex);

WHILE (@currentEndIndex<>0)

BEGIN

INSERT INTO @returnTable VALUES (LTRIM(SUBSTRING(@input, @currentStartIndex, @currentEndIndex-@currentStartIndex)))

SET @currentStartIndex=@currentEndIndex+1;

SET @currentEndIndex=CHARINDEX(@delimiter,@input,@currentStartIndex);

END

IF (@currentStartIndex <= @length)

INSERT INTO @returnTable

VALUES (LTRIM(SUBSTRING(@input, @currentStartIndex, @length-@currentStartIndex+1)));

RETURN;

END;

SELECT [dbo.StringSplits](A.farm_detailsdata),A.farmer_id

FROM dbo.tbl_farmer_farmdetails_mozambique A

INNER JOIN dbo.tbl_farmerregistration_mozambique B ON B.farmerctscode = A.farmer_id

when i run shows error as follows

[dbo.StringSplits] is not recognized built in function name

please help me how to solve this error.

in my above select what is the mistake i made.
+1
Rajeev Punhani8y ago
Hi Narasiman,

I presume that you are having a function StringSplits and you have a column in tbl_farmer_farmdetails which has a column farm_detailsdata which needs to be splitted with a particular character but you are not passing any seperator .This issue is due to StringSplits only .Could you confirm what is this StringSplits.
0
Nitin Sontakke8y ago
Have you supplied all the parameters for your function?

Is your function a table returning or a scalar one?

You cannot mix table returning function and a SELECT clause.

For example, if your function is table returning, you cannot use it as above. In above select statement, only a scalar function is allowed.

If you still have questions, please do let us know.
+1