Tackle Table:
| TackleID | TackleName |
|---|---|
| 5e8ef3ed-d02a-4582-85d3-4a993521ed0f | Name1 |
| f08c1041-e5b8-4f25-b69b-90d6a59d937e | Name2 |
TackleResult Table:
| TackleResultID | TackleID | RDI |
|---|---|---|
| 91f50120-dd46-4f10-900d-42e2338dd649 | 5e8ef3ed-d02a-4582-85d3-4a993521ed0f | [{“Name”:”SeIds”,”Value”:”Data”},{“Name”:”ISD”,”Value”:”{“SID”:{“column”:{“City”:”Himachal”,”Depth”:”5mts”}},”Canded”:false}”}] |
| 949fc210-7349-4dcf-9a64-08c1259cb3c6 | f08c1041-e5b8-4f25-b69b-90d6a59d937e | [{“Name”:”SeIds”,”Value”:”Data”},{“Name”:”ISD”,”Value”:”{“SID”:{“column”:{“City”:”Delhi”,”Depth”:”15mts”}},”Canded”:false}”}] |
Main Table
| MainID | TackleID |
|---|---|
| 45e0fa71-5091-4523-87f1-4f04bdd98cd7 | 5e8ef3ed-d02a-4582-85d3-4a993521ed0f |
Based on the flag @showDelhi =1 I need to show the records with City = Delhi and @showDelhi=0
I need to display records with City=Himachal
I have written query like below:
Declare @MainId uniqueidentifier='45e0fa71-5091-4523-87f1-4f04bdd98cd7'
Declare @showDelhi bit = 0;
SELECT TG.TackleID
FROM Main P
INNER JOIN Tackle TG ON TG.TackleID = P.TackleID
INNER JOIN TackleResult TRD on TRD.TackleID = TG.TackleID
WHERE P.MainId = @MainId
AND (1 = @showDelhi OR ((SELECT City FROM OPENJSON((SELECT VALUE FROM OPENJSON (CONVERT (VARCHAR(MAX),TRD.RDI))with( Name nvarchar(100), Value nvarchar(max)) where Name='ISD')
) WITH (City NVARCHAR(100) '$.SID.column.City')) not like '%Himachal%'))
Above query is working fine when @showDelhi = 1 but When @showDelhi = 0, it is throwing below error:
JSON text is not properly formatted. Unexpected character ‘t’ is found at position 2.
Didn’t get what was the issue