Received: from qmsql.netservers.net (hpmail3.netservers.net [209.196.128.174]) by postgresql.org (Postfix) with SMTP id 5A9DD4759F5 for ; Tue, 19 Mar 2002 15:27:36 -0500 (EST) Received: (qmail 64544 invoked from network); 19 Mar 2002 20:27:36 -0000 Received: from unknown (HELO AARON) (66.26.63.73) by hpmail3.netservers.net with SMTP; 19 Mar 2002 20:27:36 -0000 Message-ID: <010c01c1cf83$c6bb8cd0$110a010a@headquarters.wcgroup.com> From: "Samuel J. Sutjiono" To: , Subject: How to use an array string Date: Tue, 19 Mar 2002 15:22:21 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0109_01C1CF59.DD7A8E00" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200203/961 X-Sequence-Number: 22330 This is a multi-part message in MIME format. ------=_NextPart_000_0109_01C1CF59.DD7A8E00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, I need help. I want to build a list of customers (custid) that has the word= 'sam' in their fullname and store them to a text string (ResultList). The= n on the second query, I want to select the customers with ID that matches = the ID in the ResultList.=20=20 DECLARE rec_set record; s_result text; ResultList text :=3D '''';=20=20 =20=20=20=20=20=20=20 BEGIN For rec_set IN SELECT CustID FROM Customer where Fullname ~* ''(sam)'' =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 LOOP ResultList :=3D ResultList || '''''''' || rec_set.CustID || '''''''' || '= ', ''; END LOOP; END; BEGIN For rec_set IN SELECT Fullname, Address /*from Customer where CustID in (''1220'', ''3452'', ''4112'', '''')*/ from Customer where CustID in (ResultList) END; The problem is that the query can't find any match. Maybe because it compar= es a CustID with a string of values (ResultList). Does anybody have any sug= gestions on how I can get around this ? Does anybody know how to build an = array string so I can store the cust id ? =20 Thanks, Sam ------=_NextPart_000_0109_01C1CF59.DD7A8E00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi All,
 
I need help. I want to build a list of customers (custid) that has the= word=20 'sam' in their fullname and store them to a text string (ResultList). = Then=20 on the second query, I want to select the customers with ID that matches th= e ID=20 in the ResultList. 
 
DECLARE
    rec_set=20 record;
    s_result text;
    ResultLi= st=20 text :=3D '''';  
      =20
BEGIN
 For rec_set IN SELECT CustID
&nbs= p;FROM=20 Customer where  Fullname ~*=20 ''(sam)''
          &n= bsp;         =20
 LOOP
  ResultList :=3D ResultList || '''''''' || rec_set.= CustID=20 || '''''''' || '', '';
 END LOOP;
END;
 
BEGIN
 For rec_set IN SELECT Fullname,=20 Address
 /*from Customer where CustID in (''1220'', ''3452'', ''411= 2'',=20 '''')*/
 from Customer where CustID in (ResultList)
END;
 
The problem is that the query can't find any match. Maybe because= =20 it compares a CustID with a string of values (ResultList). Does anybod= y=20 have any suggestions on how I can get around=20 this ?  Does anybody know how to build an array string so I = can=20 store the cust id ?
 
Thanks,
Sam
------=_NextPart_000_0109_01C1CF59.DD7A8E00--