Hi,

I want to store the result of one query into a string with each value seperated by comma which I can use as input to other query.

For Eg.
Suppose there is a table with students information, each having its unique ID No, then the first query will be

select ID from Students

I'll get list of IDs as
1
2
3
4
5
6
7
8
9
10

This output I want to convert to a String which will be like

'1,2,3,4,5,6,7,8,9,10' which in turn i'll provide as an input to other query.

Can you please help me with this?

Thanks!

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

One approach is to use output format COM:

TABLE FILE CAR
 SUM COUNTRY
 ACROSS COUNTRY NOPRINT
 ON TABLE SAVE AS SAVE1 FORMAT COM
END

-RUN
-READ SAVE1 &CLIST.A80
-TYPE &CLIST

The result is:

"ENGLAND","FRANCE","ITALY","JAPAN","W GERMANY"

If you are planning to use the output in an IN clause in a subsequent WebFOCUS query, then the double quote marks generated by output format COM will not work. In this case, you can use the output format DFIX:

TABLE FILE CAR
 SUM COUNTRY
 ACROSS COUNTRY NOPRINT
 ON TABLE HOLD FORMAT DFIX DELIMITER , ENCLOSURE ''''
END

-RUN
-READ HOLD &CLIST.A80

TABLE FILE CAR
 PRINT COUNTRY CAR
 WHERE COUNTRY IN (&CLIST)
END

Variable &CLIST will contain:

'ENGLAND','FRANCE','ITALY','JAPAN','W GERMANY'

The above two solutions works.
But the table I am querying returns around 1200 records and because of the huge record number, ACROSS is throwing following error.

VERB OBJECTS TIMES RETRIEVED COLUMNS EXCEEDS 256:

"The combination of ACROSS and the number of verb objects will produce more than 256
output columns in the report. Reduce the number of verb objects"

If you need to pass that many values, then you should save them in a file as a list and pass the whole file as input to your subsequent query:

TABLE FILE CAR
 PRINT DST.COUNTRY
 ON TABLE SAVE AS SAVE1
END
-RUN
-*
TABLE FILE CAR
 PRINT COUNTRY CAR
 WHERE COUNTRY IN FILE SAVE1
END

Hi Dan,

Here my problem is to retrieve the data in an SQL query. I'll be passing the parameter in a SQL Query, also each record from the previous query is Unique as its an ID.

Is there any way I can retrieve the IDs from the Master file and store it in a variable?

If your SQL query is in another focexec, you can -READ the SAVE file and construct your IN clause using Dialogue Manager.

quote:

Originally posted by GinnyJakes:
If your SQL query is in another focexec, you can -READ the SAVE file and construct your IN clause using Dialogue Manager.

Can you provide me a sample code for the same?

I'll try to implement it.

Are both of your RDBMS source the same type (e.g both SQLMSS or both SQLORA etc.)?

You could always attempt a cross query within SQL passthru.

T

Here is another way to get a single line with commas, but the record length will be quite large.

TABLE FILE TRADES
PRINT COMPUTE CNTR/P9 = LAST CNTR + 1 ; 
      COMPUTE A_CNTR/A9 = PTOA(CNTR,'(P9)','A9');
BY TRADER_ID
-*WHERE RECORDLIMIT EQ 100
ON TABLE HOLD AS TMP_DATA
END

-RUN

-SET &TOT_CNTR = &LINES ;

TABLE FILE TMP_DATA
PRINT A_CNTR
      COMPUTE COMMA/A1 = IF CNTR EQ &TOT_CNTR THEN ' ' ELSE ',';

ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS CSV_FILE
END

-RUN

quote:

Originally posted by Tony A:
Are both of your RDBMS source the same type (e.g both SQLMSS or both SQLORA etc.)?

Tony,

I want to get the result in form of comma seperated values which I'll pass to a procedure which has String as an input parameter.

eg: procedure_proc_ids('1,2,3,4,5,6,7,8,9,10')

So I cannot directly pass the result to other RDBMS source. I have to process the result from 1st block (of RDMS), convert it to the string and pass it to the second block.

So you are stuck with using the stored procedure as opposed to being able to write the entire thing yourself?

If that's the case then you are stuck with managing the process to ensure that the number of parms you try to pass in within the limitations of the interface(s) used.

Dan's or Waz's suggestions both have merits but I would ask if the parms have to be passed in one go or can they be split into manageable sizes according to limitations (both WF and the RDBMS you are using).

Of course there are other considerations depending upon the RDBMS you use - which you haven't mentioned.

As for the sample code, either of Dan's first two examples will give you a variable with your values. Depending upon what your requirements are reagrding how many you can / have to pass to your stored proc, you can wrap the code within dialogue manager to manage the repeats required.

T

Swap,
You could try an approach like this:

TABLE FILE CAR
PRINT SEATS
ON TABLE SAVE
END
-RUN
-SET &TOT = &LINES;

TABLE FILE CAR
PRINT SEATS NOPRINT
      COMPUTE CNTR/I5 = CNTR + 1; NOPRINT
      COMPUTE LARGE/A4000V = IF CNTR EQ 1 THEN EDIT(SEATS) ELSE LARGE || ',' || EDIT(SEATS);
IF CNTR EQ &TOT
END

Don't really know if it will work for your environemnt, but for the car file it does. It might be worth a go.
Be aware, when you do a save of the end-result that the computed field is of variable length - the hold file will contain the length of the field in front of the actual value - it's 6 bytes long.

quote:

procedure_proc_ids('1,2,3,4,5,6,7,8,9,10')

Parameter values do not all have to be on the same line

99999 is a dummy to allow easy code


TABLE FILE CAR
PRINT
COMPUTE ID/I5 WITH COUNTRY=LAST ID +1;
COMPUTE COMA/A1=',';
BY COUNTRY NOPRINT
ON TABLE SAVE
END
-RUN
procedure_proc_ids('
-INCLUDE SAVE
99999')

Regarding Ginny's proposed solution, here are two examples. The first writes the entire second procedure to a file named proc.fex, and then executes that procedure. The second example executes the second procedure as part of the in-line code. In both cases, the list of SEATS is written out one per line followed by a comma (as per JG's idea), and thus avoids the problem of exceeding the width limitation of a string on a single line. Perhaps one of these examples will give you the beginnings of a solution.

Example #1 (write and execute second procedure):

TABLE FILE CAR
PRINT SEATS
ON TABLE SAVE AS SAVE1
END
-RUN
-SET &COUNTER = &LINES ;
-*
FILEDEF PROC DISK proc.fex
-RUN
-*
-WRITE PROC TABLE FILE CAR
-WRITE PROC PRINT COUNTRY CAR
-WRITE PROC WHERE SEATS IN (
-*
-REPEAT ENDREPEAT1 FOR &I FROM 1 TO &COUNTER
-SET &COMMA = IF ( &I EQ &COUNTER ) THEN '' ELSE ',';
-READ SAVE1, &SEATS
-WRITE PROC &SEATS&COMMA
-ENDREPEAT1
-*
-WRITE PROC );
-WRITE PROC END
-RUN
-*
EXEC PROC

Example #2 (in-line execution of second procedure):

TABLE FILE CAR
PRINT SEATS
ON TABLE SAVE AS SAVE1
END
-RUN
-SET &COUNTER = &LINES ;
-*
TABLE FILE CAR
PRINT COUNTRY CAR
WHERE SEATS IN (
-REPEAT ENDREPEAT2 FOR &I FROM 1 TO &COUNTER
-SET &COMMA = IF ( &I EQ &COUNTER ) THEN '' ELSE ',';
-READ SAVE1, &SEATS
&SEATS&COMMA
-ENDREPEAT2
);
END

If you need a single quote at the beginning and end of the string, that can be done so:

-REPEAT ENDREPEAT3 FOR &I FROM 1 TO &COUNTER
-SET &COMMA    = IF ( &I EQ &COUNTER ) THEN '''' ELSE ',';
-SET &BEGQUOTE = IF ( &I EQ 1 ) THEN '''' ELSE '';
-READ SAVE1, &SEATS
&BEGQUOTE&SEATS&COMMA
-ENDREPEAT3