Sorry for the error, I forgot that IN works on sets, not individual
fields. So, change to:
SELECT Tb2._ID, Tb1.DET_ID, COUNT(Tb3._Name) NAME_COUNT
FROM TABLE_1 Tb1
JOIN TABLE_2 Tb2 ON Tb2.DET_ID = Tb1.DET_ID
JOIN Table_3 Tb3 ON Tb3._ID = Tb1._ID
AND CAST(Tb3.Time AS DATE) = CURRENT_DATE
AND position(','||Tb2.NAME_ID||',' in ','||Tb3.NAME_IDS||',') > 0
GROUP BY Tb2._ID, Tb1.DET_ID
The reason for the syntax is as follows:
|| is the SQL way of string concatenation. You want to join those with a
Tb2.NAME_ID in the list that's part of Tb3.NAME_IDS. Single quotes is
the most common way to encapsulate strings in SQL (though some tools may
differ and want you to use double qoutes).
Directly comparing the NAME_ID to NAME_IDS will often work, but will
fail in cases like:
position('3' in ‘1,2,23,15’) will return 6
To avoid this, use ',' to add a comma to the front and back of 3.
position(',3,' in '1,2,23,15') returns 0, so the example above is fixed.
However:
position(',15,' in '1,2,23,15') also returns 0, so there's still a minor
problem.
Hence, add comma also before and after the NAME_IDS.
position(',15,' in ',1,2,23,15,') returns 9 and your problem is solved.
HTH,
Set
Den 16.09.2016 20:38, skrev 'Joje' [email protected]
[firebird-support]:
Hi Setysvar,
Thank you for helping out. But I am unable to run it on my “SQL
manager” on executing query I am getting ‘Invalid Token’ error. Below
is error that is shown
/Invalid token./
/Dynamic SQL Error./
/SQL error code = -104./
/Token unknown - line 5, column 61./
/','./
Also, could you tell me how does ‘,’||ColName ||’,’ syntax works. I am
seeing it for the first time.
Thank you.
*From:*[email protected]
[mailto:[email protected]]
*Sent:* 16 September 2016 22:23
*To:* [email protected]
*Subject:* Re: [firebird-support] Problem in executing query using
'IN' statement.
Den 16.09.2016 15:34, skrev 'Joje' [email protected]
<mailto:[email protected]> [firebird-support]:
I have a query in which I check whether an NAME _IDS of type
varchar is in another table with NAME _ID column having data type
as int. Also, ID_1 have ID’s separated by comma’s example
‘1,2,3,4,5’.
Below is the query which I am trying to execute
*SELECT *Tb2. _ID,Tb1.DET_ID,COUNT(Tb3. _/Name) NAME/_COUNT
*FROM* TABLE_1 Tb1
*JOIN* TABLE_2 Tb2 *ON* Tb2.DET_ID= Tb1.DET_ID
*JOIN* Table_3 Tb3 *ON* Tb3. _ID= Tb1. _ID
*AND* CAST(Tb3.Time AS DATE)=CURRENT_DATE
*AND*Tb3.NAME _IDS *IN* (*CAST*(Tb2.NAME_ID AS VARCHAR(250)))
/*Here Tb3.NAME _IDS is varchar & Tb2.NAME _IDS is int so casted
into varchar. This line gives wrong output */
* GROUP BY*Tb1.DET_ID, Tb1. NAME _ID, Tb1. _/ID, Tb3./_NAME
_NOTE_: Tb3.NAME _IDS could have multiple ids in form ‘1,2,23,15’
Problem I am facing is that when Tb3.NAME _IDS contains multiple
IDs in it then result is not shown leading to wrong result. Is
there any solution to solve this query. I tried using ‘STARTING
WITH’, ‘CONTAINING’,’LIKE’ but no output.
SELECT Tb2._ID, Tb1.DET_ID, COUNT(Tb3._Name) NAME_COUNT
/* It always confuses me when I see things like COUNT(Tb3._Name),
myself I always use either COUNT(*) or COUNT(DISTINCT Tb3._Name),
though I think your syntax is correct if you want to do the equivalent
of COUNT(*), but skip nulls */
FROM TABLE_1 Tb1
JOIN TABLE_2 Tb2 ON Tb2.DET_ID = Tb1.DET_ID
JOIN Table_3 Tb3 ON Tb3._ID = Tb1._ID
AND CAST(Tb3.Time AS DATE) = CURRENT_DATE
AND ','||Tb2.NAME_ID||',' in ','||Tb3.NAME_IDS||','
/* Tb2 and Tb3 the other way around, and add commas to make sure
things matches exactly (i.e. match even if the first or last in
NAME_IDS and not match 1 to 11 etc) */
GROUP BY Tb2._ID, Tb1.DET_ID
/*It makes no sense to group by anything but the non-grouped fields
that you select, most likely you would get an error with your original
GROUP BY*/
HTH,
Set
