Hi,

I am trying to draw a graph. if no records found in search query how to redirect to HTML page "No records found". I know In reports we are using IF &LINES EQ 0 GOTO NORECS; how to find the no record in graphs.

I am using this way.

-IF GRAPH EQ 0 GOTO NORECS;

But GRAPH never equals to '0'

Kindly help me.
Thanks in advance

Hi Ginny,

-IF &LINES EQ 0 GOTO NORECS; all ways going to "No record found” Statement. Since this graphs so &LINES all ways = 0.

This following is the my code


ENGINE SQLORA SET DEFAULT_CONNECTION CAR

SQL SQLORA PREPARE SQLOUT FOR
select mnth,sum(mnthc),sum(rjc),sum(erc)
from (
select substr(MONR,1,6) "MNTH",
count(MONR) "MNTHC",
N'' "RJ",
0 "RJC",
N'' "ER",
0 "ERC"
from SOSAP_GRAPHS
where to_date(CDATE,'mm/dd/yyyy') >= to_date('FDATE','mm/dd/yyyy')
and to_date(CDATE,'mm/dd/yyyy') <= to_date['&TODATE','mm/dd/yyyy')
group by MONR
UNION
select substr(MONR,1,6) "MNTH",
0 "MNTHC",
SUBSTR(TYPE,1,6) "RJ",
count(TYPE) "RJC",
N'' "ER",
0 "ERC"
from SOSAP_GRAPHS
where to_date(CDATE,'mm/dd/yyyy') >= to_date('FDATE','mm/dd/yyyy')
and to_date(CDATE,'mm/dd/yyyy') <= to_date['&TODATE','mm/dd/yyyy')
and NVL(TYPE,'') = 'RJ'
group by MONR,TYPE
UNION
select substr(MONR,1,6) "MNTH",
0 "MNTHC",
N'' "ER",
0 "ERC",
SUBSTR(TYPE,1,6) "RJ",
count(TYPE) "RJC"
from SOSAP_GRAPHS
where to_date(CDATE,'mm/dd/yyyy') >= to_date('FDATE','mm/dd/yyyy')
and to_date(CDATE,'mm/dd/yyyy') <= to_date['&TODATE','mm/dd/yyyy')
and NVL(TYPE,'') = 'ER'
group by MONR,TYPE )
group by mnth;
END


-IF &LINES EQ 0 GOTO NORECS;

GRAPH FILE SQLOUT
SUM SUM_MNTHC_ AS 'Total Volume' SUM_RJC_ AS 'Accepted Volume' SUM_ERC_
AS 'Excluded Volume'
BY MNTH AS ''
HEADING
"Application Volume"
ON GRAPH SET LOOKGRAPH VLINE
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D OFF
ON GRAPH SET VZERO ON
ON GRAPH SET GRID ON
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHSTYLE *
setFillColor(getLegendArea(),new Color(255,255,255));
setMarkerDisplay(true);
setConnectLineMarkers(true);
setConnectScatterMarkers(true);
setO1LabelDisplay(true);
setO1AxisSide(0);
setO1MajorGridDisplay(false);
setO1MinorGridDisplay(false);
setAxisAssignment(0,0);
setSeriesType(0,2);
setAxisAssignment(1,0);
setSeriesType(1,2);
setAxisAssignment(2,0);
setSeriesType(2,2);
setY1LabelDisplay(true);
setY1AxisSide(0);
setY1MajorGridDisplay(true);
setY1MajorGridStyle(0);
setY1MinorGridDisplay(false);
setTextFormatPreset(getY1Label(),-1);
setTextFormatPattern(getY1Label(),"#.##");
setPieFeelerTextDisplay(1);
setPieLabelDisplay(0);
setTextFormatPreset(getPieSliceLabel(),1);
setLegendDisplay(true);
setFontSizeAbsolute(getY1Title(),true);
setFontSizeAbsolute(getY1Label(),true);
setFontSizeAbsolute(getY2Title(),true);
setFontSizeAbsolute(getY2Label(),true);
setFontSizeAbsolute(getO1Title(),true);
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=9,
COLOR='WHITE',
BACKCOLOR='WHITE',
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
FONT='ARIAL',
SIZE=12,
COLOR='BLACK',
STYLE=BOLD,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
$
ENDSTYLE
END
-* this will display an html screen stating there were no records
-* matching the selection criteria
-*GOTO REP;
-*-REP
-NORECS
-HTMLFORM fbrnorec
-EXIT
-*-REP
-*-EXIT

Put this after the graph

-IF &LINES EQ 0 GOTO NORECS;
-EXIT

Kunamneni,

Just so you know, the &LINES refers to how many lines of data in the final answer set which is then used for either a tabular report (TABLE) or for a graph (GRAPH). It is not specific to a tabular report.

This rule is also true for &RECORDS which indicates how many individual records were retrieved from the database that fit the selection (WHERE,IF) criteria.

You should have a -RUN statement before the -IF &LINES statement no matter if you put it before or after the GRAPH FILE statements. I would suggest leaving it before the GRAPH FILE statement and add the -RUN in the appropriate place.

True

If you will note, my post had the -RUN in it.

Ginny,

Apparently we all overlooked that including Kunamneni.