It's working now.
Thank you everyone for helping me.
Get Outlook for Android
On Sat, Sep 17, 2016 at 1:29 AM +0530, "setysvar setys...@gmail.com
[firebird-support]" <firebird-support@yahoogroups.com> wrote:
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' j...@codework-solutions.com
[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: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: 16 September 2016 22:23
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Problem in
executing query using 'IN' statement.
Den 16.09.2016 15:34, skrev 'Joje'
j...@codework-solutions.com
[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
