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 
[firebird-support]" <> 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





      Den 16.09.2016 20:38, skrev 'Joje'





            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


              SQL Error.

              error code = -104.

              unknown - line 5, column 61.



            could you tell me how does ‘,’||ColName
              ||’,’ syntax works. I am seeing it for the first





                Sent: 16 September 2016 22:23


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



Den 16.09.2016 15:34, skrev 'Joje'

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 

                      _ID,Tb1.DET_ID,COUNT(Tb3. _Name) NAME_COUNT

                       TABLE_1 Tb1  

                       TABLE_2 Tb2 ON Tb2.DET_ID= Tb1.DET_ID 

                      Table_3 Tb3 ON Tb3. _ID= Tb1. _ID 

                      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 */

                      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.

                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*/









  • [firebird-support]... 'Joje' [firebird-support]
    • Re: [firebird... Virna Constantin [firebird-support]
      • [firebird... [firebird-support]
    • Re: [firebird... setysvar [firebird-support]
      • RE: [fire... 'Joje' [firebird-support]
        • Re: [... setysvar [firebird-support]
          • R... [firebird-support]

Reply via email to