Thread: how to convert a string array to a string. fct array_to_string seem to work only for INT array??

Hi all,

  I'm messing with this, I think simple, problem.  I searched the doc
and the web without success .. hum
I have a string array(Compte[]) and I need to create the following
string statement to populate a temporary table

statement := ' INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
                    SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
                    FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
CR.CRYPNUM = CS.CSYPNUM
                    WHERE CRYPNUM = ' || quote_literal(companyId) || '
                         AND CRDATE  <= CURRENT_DATE
             AND CSGLNUM IN {'  || array_to_string(Compte, ',') || '}';

    EXECUTE statement;

For now I get :

 INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
                    SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
                    FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
CR.CRYPNUM = CS.CSYPNUM
                    WHERE CRYPNUM = 'M'
                         AND CRDATE  <= CURRENT_DATE
             AND CSGLNUM IN {cpt1, cpt2}


But I want:

 INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
                    SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
                    FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
CR.CRYPNUM = CS.CSYPNUM
                    WHERE CRYPNUM = 'M'
                         AND CRDATE  <= CURRENT_DATE
             AND CSGLNUM IN {'cpt1', 'cpt2'}

How can I do that.  I expected to find a standard function in the doc to
do that ...

Thanks for your help!

Best Regards
David

On Thursday, May 10, 2007 6:07 AM David Gagnon wrote

> I have a string array(Compte[]) and I need to create the following string
> statement to populate a temporary table
>
> statement := ' INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
>                    SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
>                    FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
> CR.CRYPNUM = CS.CSYPNUM
>                    WHERE CRYPNUM = ' || quote_literal(companyId) || '
>                         AND CRDATE  <= CURRENT_DATE
>             AND CSGLNUM IN {'  || array_to_string(Compte, ',') || '}';
>
>    EXECUTE statement;
>
> For now I get :
>
> INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
>                    SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
>                    FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
> CR.CRYPNUM = CS.CSYPNUM
>                    WHERE CRYPNUM = 'M'
>                         AND CRDATE  <= CURRENT_DATE
>             AND CSGLNUM IN {cpt1, cpt2}
>
> But I want:
>
> INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
>                    SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
>                    FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
> CR.CRYPNUM = CS.CSYPNUM
>                    WHERE CRYPNUM = 'M'
>                         AND CRDATE  <= CURRENT_DATE
>             AND CSGLNUM IN {'cpt1', 'cpt2'}
>
> How can I do that.  I expected to find a standard function in the doc to
> do that ...

One way is to include the ' in with the delimiter, and start and end the
string with ' as:

     AND CSGLNUM IN {'  || '\'' || array_to_string(Compte, '\', \'') || '\''
|| '}';

Regards,
George