#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15

    Convert string to set


    Hi,


    is there any possibility to use a STRING as SET in Firebird SP?

    here's an example:


    bill_list='1,2,5,11,27'

    select * from bills where id in (:bill_list);
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    One solution is to use EXECUTE STATEMENT

    EXECUTE STATEMENT 'select * from bills where id in (' ||:bill_list ||')';

    Another and more complicated is to use global temporary tables. You can insert in one temporary table the ID's you want and execute INNER JOIN with this table:

    select bills.* from bills inner join tmp_id on tmp_id.id = bills.id

    Just make sure that you've deleted the old values before inserting the new ones if you are going to execute this select many times with different set of IDs in one transaction.
    + With this method you can filter SELECTs with more than 255 values, because IN is limited to accept up to 255 values.
    - Firebird creates files on the disk with the data for the temporal table and this can slow the execution of the queries. But you can make a RAM drive for Firebird to use it for its temp files. This also helps for other things that Firebird do if you have enough free RAM.
    Last edited by mIRCata; November 24th, 2011 at 02:51 AM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15
    hi,

    thanks a lot for your reply. This global temporary thing aroused my interest.

    How can i create global temporary tables? Can i create them inside of an SP? or in the declaration part of an SP?






    Originally Posted by mIRCata
    One solution is to use EXECUTE STATEMENT

    EXECUTE STATEMENT 'select * from bills where id in (' ||:bill_list ||')';

    Another and more complicated is to use global temporary tables. You can insert in one temporary table the ID's you want and execute INNER JOIN with this table:

    select bills.* from bills inner join tmp_id on tmp_id.id = bills.id

    Just make sure that you've deleted the old values before inserting the new ones if you are going to execute this select many times with different set of IDs in one transaction.
    + With this method you can filter SELECTs with more than 255 values, because IN is limited to accept up to 255 values.
    - Firebird creates files on the disk with the data for the temporal table and this can slow the execution of the queries. But you can make a RAM drive for Firebird to use it for its temp files. This also helps for other things that Firebird do if you have enough free RAM.

IMN logo majestic logo threadwatch logo seochat tools logo