I got a SQL from business to show the duration of the processes over time. How can I create a graph to show the duration on each day for a given process? Here is what I have right now. I want to change SUM to PRINT, it give the following error:

0 NUMBER OF RECORDS IN TABLE= 90 LINES= 90
0 NUMBER OF RECORDS IN GRAPH= 90 PLOT POINTS= 1
VERTICAL AXIS IS NOT NUMERIC..CAN'T GRAPH


How can I convert the AS_OF_DATE to numeric? Will it let me to create the graph after the conversion of date?

Here is my current code:
**********************************

SET SQLENGINE = SQLORA
SQL SET SERVER ICS
-RUN
SQL SQLORA
Select p.process_name, to_char(l.end_time - l.START_TIME) as Duration, to_number(to_char(l.AS_OF_DATE,'MMDDYY'),'999999') as AS_OF_DATE
from BEE_DBO.PROCESS_EXECUTION_LOG l, BEE_DBO.PROCESSES p
where l.process_id = p.process_id and l.PROCESS_ID = 5583
-*and to_char(l.as_of_date, 'yyyy/mm/dd') > '2010/01/01'
order by l.as_of_date desc;
TABLE ON TABLE HOLD AS BEE_GRAPH FORMAT ALPHA
-*PRINT *
END
-RUN
-*TABLE FILE HOLD02
DEFINE FILE BEE_GRAPH
AS_OF_DATE/D12=DAMDY(AS_OF_DATE);
END
GRAPH FILE BEE_GRAPH
SUM CNT.DURATION
ACROSS AS_OF_DATE
BY PROCESS_NAME

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

Looks like the sql is converting duration to be a char datatype. Maybe remove the to_char (syntax?).

removed the to_char still got the same error:

0 NUMBER OF RECORDS IN TABLE= 90 LINES= 90
0 NUMBER OF RECORDS IN GRAPH= 90 PLOT POINTS= 9
VERTICAL AXIS IS NOT NUMERIC..CAN'T GRAPH

What do you get if you put this in after the sql's END statement:

-RUN
WHENCE BEE_GRAPH MASTER
?FF BEE_GRAPH

quote:

-RUN
WHENCE BEE_GRAPH MASTER
?FF BEE_GRAPH

0 NUMBER OF RECORDS IN TABLE= 90 LINES= 90
C:\ibi\DEVSTU~1\srv76\wfs\edatemp\ts000005\bee_graph.mas
FILENAME= BEE_GRAPH
PROCESS_NAME E01 A100
DURATION E02 A27V
AS_OF_DATE E03 D20.2

See:

DURATION E02 A27V

The DURATION is still being returned as character data, and that is causing the error. So you could do a define on DURATION turn it into a number.