Javier, Ah, I see! That makes complete sense and takes care of the extra comma. Thanks for following up.
Dawn -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Javier Valencia Sent: Monday, September 12, 2005 5:12 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Variable from the contents of an ascii file Dawn: I believe that I left a couple of lines out; the following code should work correctly: set var varlist text = NULL SET VAR vcomma TEXT SET VAR vitem integer DROP CURSOR cursor1 DECLARE cursor1 CURSOR FOR SELECT ID FROM DUMMY WHERE ID < 11 ORDER BY ID ASC OPEN cursor1 FETCH cursor1 INTO vitem INDICATOR ivitem SET VAR vcomma = '' WHILE SQLCODE <> 100 THEN SET VAR varlist =(.varlist + .vcomma + (ctxt(.vitem))) SET VAR vcomma = ',' FETCH cursor1 INTO vitem INDICATOR ivitem ENDWHILE RETURN To test, create a table DUMMY with on column ID and fill it with values 1-10. The resulting string varlist will be: R>show var varlist 1,2,3,4,5,6,7,8,9,10 I have used variations of this type of code before in all kinds of applications without problems. Javier, Javier Valencia, PE President Valencia Technology Group, L.L.C. 14315 S. Twilight Ln, Suite #14 Olathe, Kansas 66062-4578 Office (913)829-0888 Fax (913)649-2904 Cell (913)915-3137 ================================================ Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from all system and destroy all copies. ====================================================== -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of Dawn Oakes Sent: Monday, September 12, 2005 7:03 AM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Variable from the contents of an ascii file Javier, Wouldn't that leave an extra comma in the beginning? The varlist variable is null for the first time through the while loop. I have a similar code, where instead of a comma, I'm inserting a couple of spaces in between each set of items (I'm fetching more than one column). My end result is a variable with two spaces at the beginning. It's not a big deal, I just use the variable in a report, so I never really tried to figure out a way around it. Sub-reports in 7.5 have allowed me to reformat the report so I'm not going to use it going forward anyway. Dawn -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Javier Valencia Sent: Friday, September 09, 2005 8:57 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Variable from the contents of an ascii file Dawn: The "SELECT LISTOF..." would be the suggested solution to Claudine's question. On the second example, if you change the code as follows, you will not have the comma at the end: set var varlist text set var vitem integer DECLARE cursor1 CURSOR FOR SELECT my_field FROM my_table WHERE whatever OPEN cursor1 FETCH cursor1 INTO vitem INDICATOR ivitem SET varlist = (ctxt(.vitem)) WHILE SQLCODE <> 100 THEN SET VAR varlist =(.varlist + ',' + (ctxt(.vitem))) FETCH cursor1 INTO vitem INDICATOR ivitem ENDWHILE Return Javier, Javier Valencia, PE President Valencia Technology Group, L.L.C. 14315 S. Twilight Ln, Suite #14 Olathe, Kansas 66062-4578 Office (913)829-0888 Fax (913)649-2904 Cell (913)915-3137 ================================================ Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from all system and destroy all copies. ====================================================== -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of Dawn Oakes Sent: Friday, September 09, 2005 2:58 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Variable from the contents of an ascii file Claudine, How about the Select...LISTOF command SELECT LISTOF(my_field) into varname from tablename where whatever This results in a note data type, but can easily be converted to text if needed. As Mike suggested, you could also use a cursor; I have an example similar to something I do: set var varlist text set var vitem integer DECLARE cursor1 CURSOR FOR SELECT my_field FROM my_table WHERE whatever OPEN cursor1 FETCH cursor1 INTO vitem INDICATOR ivitem WHILE SQLCODE <> 100 THEN SET VAR varlist =(.varlist + (ctxt(.vitem)) + ',') FETCH cursor1 INTO vitem INDICATOR ivitem ENDWHILE Return You'll end up with an extra comma, again depending on what you're doing with the variable could be important. Dawn -----Original Message----- From: Claudine Robbins [mailto:[EMAIL PROTECTED] Sent: Friday, September 09, 2005 3:36 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Variable from the contents of an ascii file Mike, I'm getting the ascii file from the following command: Unload data for my_table using my_field as ascii where my_other_field = whatever How can I construct this to equal variables? > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of MikeB > Sent: Friday, September 09, 2005 2:27 PM > To: RBG7-L Mailing List > Subject: [RBG7-L] - Re: Variable from the contents of an ascii file > > when you write the values to the ascii file, construct code on the fly > that will set the values to variables as you want like: > > out test.asc > write 'Set var v1 int = 53' > write 'set var v2 text = ''SomeText''' -- single quotes > write 'Return' > out scr > run test.asc > > > > This is displayed this way for simplicity. You can construct the > values for the write statements in code as variables and then write > that to the file as well... > > > ----- Original Message ----- > From: "Claudine Robbins" <[EMAIL PROTECTED]> > To: "RBG7-L Mailing List" <[email protected]> > Sent: Friday, September 09, 2005 2:16 PM > Subject: [RBG7-L] - Variable from the contents of an ascii file > > > > > > Hi everyone, > > > > I want to create a variable which will contain one or more values > extracted > > from a table, i.e.: 53, 45, 101. > > > > So far, I can create an ascii file, test.asc with "53","45","101" if > > I > first > > reset the quotes to ". > > > > Now, I want to put these values in a variable. I can't figure out > > what > to > > do next. > > > > TIA, > > > > Claudine :) > > > > --- RBG7-L > > ================================================ > > TO POST A MESSAGE TO ALL MEMBERS: > > Send a plain text email to [email protected] > > > > (Don't use any of these words as your Subject: > > INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, REMOVE, SUSPEND, RESUME, > > DIGEST, RESEND, HELP) > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [email protected] In the message SUBJECT, > > put just one word: INTRO > > ================================================ > > TO UNSUBSCRIBE: > > Send a plain text email to [email protected] In the message SUBJECT, > > put just one word: UNSUBSCRIBE > > ================================================ > > TO SEARCH ARCHIVES: > > Send a plain text email to [email protected] In the message SUBJECT, > > put just one word: SEARCH-n (where n is the number of days). In the > > message body, place any text to search for. > > ================================================ > > --- RBG7-L =======================3D======================= D= TO POST A MESSAGE TO ALL MEMBERS: Send a plain text email to [email protected] (Don't use any of these words as your Subject: INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP) =======================3D======================= D= TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: INTRO =======================3D======================= D= TO UNSUBSCRIBE: Send a plain text email to [email protected] In the message SUBJECT, put just one word: UNSUBSCRIBE =======================3D======================= D= TO SEARCH ARCHIVES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: SEARCH-n (where n is the number of days). In the message body, place any text to search for. =======================3D======================= D=
