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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     SQL based fex and Reportcaster burst

Read-Only Read-Only Topic
Go
Search
Notify
Tools
SQL based fex and Reportcaster burst
 Login/Join
 
Member
posted
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?
 
Posts: 25 | Location: NY | Registered: October 16, 2003Report This Post
Silver Member
posted Hide Post
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.
 
Posts: 31 | Location: Oklahoma City, OK | Registered: September 11, 2003Report This Post
Member
posted Hide Post
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
 
Posts: 25 | Location: NY | Registered: October 16, 2003Report This Post
Silver Member
posted Hide Post
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
 
Posts: 31 | Location: Oklahoma City, OK | Registered: September 11, 2003Report This Post
Member
posted Hide Post
Thanks, that worked.
 
Posts: 25 | Location: NY | Registered: October 16, 2003Report This Post
Gold member
posted Hide Post
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.


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
Virtuoso
posted Hide Post
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?


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 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     SQL based fex and Reportcaster burst

Copyright © 1996-2020 Information Builders