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 -----------
111	AAAA	P	MX,ONX,TN
222	BBBB	A	TN

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

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, 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"?

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

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.

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.

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;

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