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,
Webfocus 7.7.03 Windows XP Excel, PDF, HTML, APDF, AHTML, Maintain
February 09, 2010, 05:08 AM
Dan Satchell
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"
WebFOCUS 7.7.05
February 09, 2010, 06:12 AM
Dan Satchell
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'
WebFOCUS 7.7.05
February 09, 2010, 06:44 AM
Swap
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"
Webfocus 7.7.03 Windows XP Excel, PDF, HTML, APDF, AHTML, Maintain
February 09, 2010, 07:02 AM
Dan Satchell
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
WebFOCUS 7.7.05
February 09, 2010, 08:09 AM
Swap
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.
Webfocus 7.7.03 Windows XP Excel, PDF, HTML, APDF, AHTML, Maintain
February 09, 2010, 09:07 AM
Swap
Is there any way I can retrieve the IDs from the Master file and store it in a variable?
Webfocus 7.7.03 Windows XP Excel, PDF, HTML, APDF, AHTML, Maintain
February 09, 2010, 09:23 AM
GinnyJakes
If your SQL query is in another focexec, you can -READ the SAVE file and construct your IN clause using Dialogue Manager.
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.
Webfocus 7.7.03 Windows XP Excel, PDF, HTML, APDF, AHTML, Maintain
February 09, 2010, 12:24 PM
Tony A
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
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
February 09, 2010, 03:52 PM
Waz
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
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
February 10, 2010, 01:25 AM
Swap
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.
Webfocus 7.7.03 Windows XP Excel, PDF, HTML, APDF, AHTML, Maintain
February 10, 2010, 02:59 AM
Tony A
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
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
February 10, 2010, 04:43 AM
GamP
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.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
February 10, 2010, 04:44 AM
<JG>
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')
February 10, 2010, 07:56 AM
Dan Satchell
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