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

 

Hi All,

 

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.

__._,_.___

---

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 (1)

---

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

.

__,_._,___