I've got a string that is being passed into a drilldown. It is the data from a multi select variable. I have inserted '#' between each selection and now I want to convert the '#' to quotes with a comma between each one.
For example if &AREASELECT is
1234#a234#f322 then I want it to be
'1234','a234','f322'

I am using the following code
SQL SQLORA
SELECT REPLACE('&AREASELECT', '#', ''''||','||'''') AREASELECT1 FROM DUAL;
TABLE
ON TABLE HOLD AS SELECT1 FORMAT ALPHA
END
-RUN
-READ SELECT1 &AREASELECT1.A ???
-RUN

however, the string being passed in can be any length, so I can't seem to get this to work.

I tried creating the string with the quotes instead of the '#' where &VSELAREA is what is passed into &AREASELECT

-IF &SELAREA0.EXISTS GOTO DOAREAMLT;
-SET &PSELAREA = &SELAREA;
-SET &VSELAREA = '''' | &SELAREA | '''';
-IF &FROMFORM EQ 'NO' GOTO SCHEDAREA;
-IF '&SELAREA' EQ 'FOC_NONE' GOTO ENDAREAMLT;
-GOTO ENDAREAMLT;
-DOAREAMLT
-SET &PSELAREA = &SELAREA1;
-SET &VSELAREA = '''' | &SELAREA1 | '''';
-REPEAT ENDAREAMLT FOR &CNT FROM 2 TO &SELAREA0
-SET &PSELAREA=&PSELAREA | '#' | &SELAREA.&CNT;
-SET &VSELAREA=&VSELAREA | ',' | '''' | &SELAREA.&CNT | '''';
-ENDAREAMLT
-SCHEDAREA

however the string being passed in ends up looking like this:
AREASELECT=&1234','a234','f322''

Does anyone have any suggestions?

I am doing this in an test environment that is Webfocus 7.6.9

This message has been edited. Last edited by: Kerry,

Laura, the following code resolves the issue:

-SET &AREASELECT = 'ENGLAND#FRANCE#JAPAN#ITALY';

SQL DB2
SELECT REPLACE('''&AREASELECT''', '#', ''''||','||'''') AREASELECT1 FROM BASEL.TIME_D FETCH FIRST 1 ROW ONLY;
TABLE
ON TABLE HOLD AS SELECT1 FORMAT ALPHA
END
-RUN

CHECK FILE SELECT1 HOLD
-RUN

TABLE FILE HOLD
PRINT FORMAT
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN
-READ H001 &LEN.A8.

-READ SELECT1 &AREASELECT1.&LEN.EVAL
-RUN

-TYPE &AREASELECT1

I'm using DB2 SQL. The result of &AREASELECT1 is 'ENGLAND','FRANCE','JAPAN','ITALY' (including the two outer-most quotes (because I added them to the SQL REPLACE function.

As far as I know, the new HTML forms generated by Dev Studio can create a single variable with all the values selected in a multi-select, so it is possible you may not have to do this, but thanks for giving me an idea on how to use SQL to manipulate Dialogue Manager variables - this will open up new possibilities.

Thanks for your help.

I decided to try to go about this a different way. Instead of separating the parameters by a '#' I used '*#*' instead. Then I used CTRAN twice to replace the '*' with a quote and the '#' with a comma. Next I added the opening and closing qoutes. This appears to be working so I will go with it, but if I run into more problems I will try your solution. Thanks again.

You shoudl also be able to do this with the STRREP function. To a maximum lenght of 4095 chars.

Laura,

If your string is of indeterminate length then use -READ filedd, &variable (e.g. with a comma) instead of -READ filedd &variable.Ann. without the comma. This is akin to (but not the same as) using something like readln which will read an entire line of input until the end of line marker (in what ever shape or form it exists). You can then use &variable.LENGTH as per Francis to identify how long the read string is.

T