Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to print multiple values(returned from another sub-query) in a report column

Read-Only Read-Only Topic
Go
Search
Notify
Tools
How to print multiple values(returned from another sub-query) in a report column
 Login/Join
 
Member
posted
I am generating a simple reprts which prints data in 3 columns returned from a SQL query. I want to add one more column to it, which display comma separated multiple values returned from another sub-query corresponding to that record ID. i.e.

1st MAIN query: SELECT cID,cName,cRole FROM CANDIDATE_TBL
2nd SUB query: SELECT a.cProjects FROM PRJ_TBL a WHERE a.cID=#MAIN_SQL_cID#

output:



cID
----
cName
--------
cRole
------
cProjects
-----------
111AAAAPMX,ONX,TN
222BBBBATN

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



WebFOCUS Versions using 7.1; Platform: Windows XP; Output Format: PDF.
 
Posts: 16 | Registered: September 07, 2007Report This Post
Virtuoso
posted Hide Post
Abhi

what you want looks not so easy.
You have to build a hold file from the subquery first and do a compute in that query

TABLE FILE SUBQUERY
COMPUTE COUNTER/I4=IF CID EQ LAST CID THEN COUNTER+1 ELSE 1;
COMPUTE CPROJECT/A500=IF CID EQ LAST CID THEN CPROJECT||','||PROJECT ELSE PROJECT;
BY CID
ON TABLE HOLD AS HOLDPROJ FORMAT FOCUS INDEX CID
END


The HOLDPROJ file has now per CID one or more records.
CID     COUNTER     CPROJECT
111        1        MX
111        2        MX,ONX
111        3        MX,ONX,TN
222        1        TN


As you can see the records was growing, and you need only the last one. Your next step is to filter these last.

TABLE FILE HOLDPROJ
PRINT CPROJECT
BY CID
BY HIGHEST 1 COUNTER
ON TABLE HOLD AS HFIN_PRO FORMAT FOCUS INDEX CID
END


The last step is to combine this final HOLD file with the first query and make your report.
I suppose you know how to do that.

There are more possible solutions (like putting the projects in an alpha hold file and create a new master for that hold file), and the length of the project field might be a problem, so you may have to play with substrings etc.
I hope this was of any help

Please update your signature so we know what platform you use and what version....




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Member
posted Hide Post
Frank, Could you please give me a code example of new master file and alpha hold file for your suggestion: "putting the projects in an alpha hold file and create a new master for that hold file"?



WebFOCUS Versions using 7.1; Platform: Windows XP; Output Format: PDF.
 
Posts: 16 | Registered: September 07, 2007Report This Post
Guru
posted Hide Post
Abhi,

You can do as Frank suggests and use a HOLD file, but unless you really need to run 2 queries you can do a JOIN

JOIN
  cID IN CANDIDATE_TBL TO MULTIPLE cID IN PRJ_TBL AS J0
 END
 
 TABLE FILE CANDIDATE_TBL
 PRINT
      cName
      cRole
      COMPUTE PROJ/A100 = IF LAST cID EQ cID THEN LAST PROJ || ( ', '|cProjects ) ELSE cProjects;
      COMPUTE CNT/I2 = IF LAST cID EQ cID THEN LAST CNT + 1 ELSE 1; NOPRINT
 BY cID
 BY TOTAL HIGHEST 1 CNT NOPRINT
 END
 


Or if you need to do SQL passthru rather than use MASTER files.

 ENGINE SQLMSS SET DEFAULT_CONNECTION CON01
 
SQL SQLMSS PREPARE SQLOUT FOR
SELECT T1."cID", T1."cName", T1."cRole", T2."cProjects" FROM
dbo.CANDIDATE_TBL T1, dbo.PRJ_TBL T2
WHERE (T2."cID" = T1."cID") ORDER BY
T1."cID";
 END

TABLE FILE SQLOUT
PRINT
     cName
     cRole
     COMPUTE PROJ/A100 = IF LAST cID EQ cID
       THEN LAST PROJ || ( ', '|cProjects )
       ELSE cProjects;
     COMPUTE CNT/I2 = IF LAST cID EQ cID THEN LAST CNT + 1 ELSE 1; NOPRINT
BY cID
BY TOTAL HIGHEST 1 CNT NOPRINT
END
 


You'll most probably need to edit the SQL code to match your database server etc.

Cheers

Stu


WebFOCUS 8.2.03 (8.2.06 in testing)
 
Posts: 253 | Location: Melbourne, Australia | Registered: February 07, 2007Report This Post
Member
posted Hide Post
Frank & StuBouyer,
I appreciate your code help for the problem. The count is coming correct, but the only problem with concatenated project string.

I am trying to concate Proj_ID (inplace of Proj_code) which is integer type. I given the Proj_list variable length of 100 and I am sure that max list size will not go more than 40-50 char.

When I run the report, it shows error:
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: PROJ_LIST
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

I am able to 'ADD' integer field data to integer variable (like CNT), but i get this error always when I try for concating those data for string variable. I also searched forums but this error could not be resolved.



WebFOCUS Versions using 7.1; Platform: Windows XP; Output Format: PDF.
 
Posts: 16 | Registered: September 07, 2007Report This Post
Virtuoso
posted Hide Post
Abhi,
You can actually concatenate integer and alphanumeric string together, but you'll have to convert the numeric string to alphanumeric first.
You can use either EDIT or FTOA to do this.
Edit will give you leading zeroes and is basically meant for converting integers. FTOA will not give leading zeroes but is meant for D-type of fields.
Give it a go with either method.

Gerard.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Abhi

if your PCODE field is an integer you can do this

DEFINE FILE xxx
PSTR/A3=EDIT(PCODE);
END

and change this string to

COMPUTE PROJ_LIST/A100 = IF LAST cID EQ cID THEN LAST PSTR || ( ', '|PSTR ) ELSE PSTR;




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Member
posted Hide Post
No solution given above could resolve the FOC282 error. I found another way from another post to do it. Here is the solution from Pietro De Santis , that is working very smoothly...

-SET &ECHO=ALL;
TABLE FILE EDUCFILE
PRINT COURSE_CODE
BY EMP_ID
ON TABLE HOLD AS HOLD01
END
DEFINE FILE HOLD01
YCOURSE_CODE/I8 = EDIT(COURSE_CODE);
XCOURSE_CODE/D8c = YCOURSE_CODE;
WCOURSE_CODE/A8 = FTOA(XCOURSE_CODE, '(D8c)', 'A8');
ZCOURSE_CODE/A8 = LJUST(8, WCOURSE_CODE, 'A8');
CONCAT_COURSE/A200 = IF EMP_ID EQ LAST EMP_ID
THEN (SUBSTR(200, CONCAT_COURSE, 1, ARGLEN(200, CONCAT_COURSE, 'I4'), ARGLEN(200, CONCAT_COURSE, 'I4'), 'A190')
|| '-' || ZCOURSE_CODE) ELSE ZCOURSE_CODE;
END
TABLE FILE HOLD01
SUM CONCAT_COURSE
BY EMP_ID
END



WebFOCUS Versions using 7.1; Platform: Windows XP; Output Format: PDF.
 
Posts: 16 | Registered: September 07, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to print multiple values(returned from another sub-query) in a report column

Copyright © 1996-2020 Information Builders