I have a SQL based report that I want to distribute through reportcaster using the burst feature. When it runs the following error return: THE ON FIELD IS NOT THE FIRST BY FIELD

The first column in the report is the sorted value the distribution list burt value should match.

I'm assuming I need to put the SQL result set in a hold file and then match the burst values? Not sure how to do this?

The first BY has to be the field you want to distribute the report on whether it's the first in the report or not. You should not have to hold and match burst values.

This is the error mesage I get from reportcaster:
______________________________________
Schedule ID: S0vkpqep7a01, Job Description: Radiation Installation Reports
Completed with errors/warnings
Task error:THE ON FIELD IS NOT THE FIRST BY FIELD
_______________________________________________

It should be bursting on the first column in the select and the column specified in the order by clause.

Here is the code for the report:

_____________________________________
SQL SQLORA SET SERVER ceh1;

-*

SQL SQLORA

SELECT radinst.ri_facility.cnty_id ,
TO_CHAR(radinst.ri_inspection.insp_date,'MM/DD/YYYY') AS Inspect_Date, radinst.ri_facility.ri_register_id Register_ID,
radinst.ri_facility.type_id,
radinst.ri_facility.business_name,
radinst.ri_facility.contact_name,
radinst.ri_facility.city,
radinst.ri_facility.zipcode,
'('||SUBSTR(radinst.ri_facility.phone,1,3)||') '||SUBSTR(radinst.ri_facility.phone,4,3)||'-'||SUBSTR(radinst.ri_facility.phone,5,4) AS PHONE,
radinst.ri_surveyor.surv_first_name || ' ' || radinst.ri_surveyor.surv_last_name AS SURVEYOR

FROM radinst.ri_inspection,
radinst.ri_facility,
radinst.ri_county,
radinst.ri_surveyor

WHERE // a big where clause //

order by radinst.ri_facility.cnty_id ASC

;

TABLE HEADING
"NYS Department of Health"
"Bureau of Environmenatl Radiation Protection"
"Non-Compliance Periodic County Inspections Lacking a Compliance Follow-up"
" "
"Report Date <+0>&DATEtrMDYY <+0> "
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BACKCOLOR=RGB(210 210 210),
$
TYPE=DATA,
COLUMN=ROWTOTAL(*),
STYLE=BOLD,
$
TYPE=TITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=10,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
$
TYPE=FOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$

ENDSTYLE
END

I've don't think you can run the SQL statement and then put FOCUS commands following the SQL unless you do a TABLE FILE command first. The results of the SQL statement automatically go into a hold file called SQLOUT. So the code should look like this:

SQL statement

TABLE FILE SQLOUT
PRINT E01, E02, (these are the assigned names to the fields in the internal matrix)
HEADING
...
END

Here's some code that I have running:
SQL SQLORA
select circt_id, to_char(creation_datetime, 'yyyymmdd hh24:mi:ss'),
to_char(compl_datetime, 'yyyymmdd hh24:mi:ss'), downstream_cust_qty,
creation_datetime, compl_datetime
from OMS.HIS_FACILITY_JOB
where to_char(creation_datetime, 'yyyymm') = &YYMM;

TABLE FILE SQLOUT
PRINT E01 E02 E03 E04 E05 E06
ON TABLE HOLD AS HISFAC
END

Try changing your code to use the TABLE FILE SQLOUT and see if that works.


Regards,
Raelene

Thanks, that worked.

Hi Raelene,

Is there any way to find howmany columns are there for the SQLOUT in the example posted by you. I have a requirement like I need not print the last column in the SELECT list but need all other columns. I need to create a generic template in which user will enter any kind of SQLs but I need not print the last column specified in the SELECT list.

Any suggestions?

Regards,
Johney.

Johney,

There is a way. But you will have to do a hold first. Please take a look at the following bit of code:

SQL
Your sql-statement;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HOLD000
END
CHECK FILE HOLD000 HOLD
TABLE FILE HOLD
COUNT FIELDNAME
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-READ HOLD &NUMBER.5
TABLE FILE HOLD000
PRINT
-SET &CNT = 0;
-SET &NUMBER = &NUMBER - 1;
-REPEAT :ENDREPEAT &NUMBER TIMES
-SET &CNT = &CNT + 1;
-SET &FIELDNAME = IF &CNT LT 10 THEN 'E0' | &CNT ELSE 'E' | &CNT;
&FIELDNAME
-:ENDREPEAT
END

Would this be of any help to you?