Hey,

I have a sql report.

I want to

1: get the count of records based on ACCT_USER_ID

2: put the number into page heading

How can i do that

ENGINE SQLORA SET DEFAULT_CONNECTION TEST

SQL SQLORA PREPARE SQLOUT FOR

select * from
);

END
TABLE FILE SQLOUT
PRINT
ACCT_LAST_NAME AS 'Last Name'
ACCT_FIRST_NAME AS 'First Name'
BY ACCT_USER_ID

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

Check out / study up on the use of &LINES and / or &RECORDS...

Maria,

You can use a multi-set report:

  
-* File MariaTan3.fex
TABLE FILE CAR
SUM CNT.COUNTRY NOPRINT CNT.SALES NOPRINT
PRINT SALES 
BY COUNTRY
BY CAR
BY MODEL
HEADING
"Sales report for <CNT.COUNTRY Countries, <CNT.SALES Records"
END

Yep Danny, that'll work. I was thinking along the lines of:

select * from );
END
-RUN
TABLE FILE SQLOUT
...
HEADING
"There are &RECORDS / &LINES in this file."
...

Or something like that, seeing that those variables had known / usable values fronm the SQLOUT file...

You would like the "count of records based on ACCT_USER_ID"?

You could do it in the SQL statement, here's an example based on a DB2 table on my server:

SQL DB2
SELECT 
MIN(BASEL.TIME_D.TIME_DIM_KEY) AS TIME_DIM_KEY,
BASEL.TIME_D.CALENDAR_YEAR,
COUNT(*) AS COUNT
FROM BASEL.TIME_D
GROUP BY BASEL.TIME_D.CALENDAR_YEAR
ORDER BY BASEL.TIME_D.CALENDAR_YEAR
;
TABLE ON TABLE HOLD AS H001
END

TABLE FILE H001
PRINT TIME_DIM_KEY
BY CALENDAR_YEAR PAGE-BREAK
HEADING
"COUNT: <COUNT"
END

&LINES and &RECORDS are based on the whole report and not on the BY field.