RE: [firebird-support] Problem in executing query using 'IN' statement.

 

 

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' joje@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

__._,_.___

---

Posted by: "Joje" <joje@codework-solutions.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group

• New Members 3

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___