Hi Buck,
Thanks for your reply.
I have already set the SQL parser for this source type and that works fine. I was just wondering if the outline view could be configured to show for it because it is in effect a program, with field definitions and processing logic and labels etc.
Below I have pasted my source so you can see what I am talking about and you reference the UDF in an SQL statement in the same way you would use a built in function like SUBSTR, e.g. select opamchain('AN7659', '01351544') from sysibm.sysdummy1;
Best regards,
Mark
SQL UDF source:
/*********************************************************************/
/* History Block */
/* ------------- */
/* Author : Mark Austin */
/* Date : 02/03/2015 */
/* Jira Ticket : PREF-2334 */
/* Task : PIP07167 */
/* Description : Changed to remove the dropping of chains when the */
/* writer percentage is < the OP or AM percentage. */
/* Also changed to select the OP IP, OP %, AM IP, */
/* AM % and the writer % which is then processed */
/* programatically to cap the AoR % to 100% and */
/* string it all together. */
/* */
/* Author : Nuria Belinchon */
/* Date : 23/02/2015 */
/* Jira Ticket : PREF-2334 */
/* Task : PIP07167 */
/* Description : Changed to select only the OP-AM chains where the */
/* percentage is not 0. Also changed the control of */
/* the SQLSTATE. */
/* */
/* Author : Mark Austin */
/* Date : 19/02/2015 */
/* Jira Ticket : PREF-2334 */
/* Task : PIP07167 */
/* Description : Changed to concatenate the OP and AM percentage */
/* into the string with the IP number so co-pub songs*/
/* with differing ratios between the co-pubs can be */
/* seperated out by the data analysis script. */
/* */
/*********************************************************************/
CREATE FUNCTION OPAMCHAIN
(SONG CHAR(6), WRITER CHAR(8)) RETURNS CHAR(160)
LANGUAGE SQL
SPECIFIC OPAMCHAIN
NO EXTERNAL ACTION
DISALLOW PARALLEL
SET OPTION DBGVIEW = *SOURCE
BEGIN
DECLARE REPORTING_STRING VARCHAR(160);
DECLARE OP_IP CHAR(8);
DECLARE OP_PERC DECIMAL(5,2);
DECLARE OP_AOR NUMERIC(7,4);
DECLARE AM_IP CHAR(8);
DECLARE AM_PERC DECIMAL(5,2);
DECLARE AM_AOR NUMERIC(7,4);
DECLARE WRITER_PERC DECIMAL(5,2);
DECLARE SQLSTATE CHAR(5);
DECLARE AT_END INTEGER DEFAULT 0;
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE C1 CURSOR FOR
SELECT DISTINCT T2.D7P_INTPTY_IP_NUMBER,
T2.D7P_IPLNK_PERCENTAGE,
T3.D7P_INTPTY_IP_NUMBER,
T3.D7P_IPLNK_PERCENTAGE,
T1.D7P_IPLNK_PERCENTAGE
FROM MPD7REP T1
INNER JOIN MPD7REP T2
ON T2.D7P_SONG_SONG_CODE = T1.D7P_SONG_SONG_CODE
AND CASE WHEN DEC(SUBSTR(HEX(SUBSTR(T2.D7P_IPLNK_LINK_IP_NUMBER
,1,2)),1,3),3,0) IS NULL THEN 0
ELSE DEC(SUBSTR(HEX(SUBSTR(T2.D7P_IPLNK_LINK_IP_NUMBER
,1,2)),1,3),3,0)
END = T1.D7P_IPLNK_SEQUENCE_NUMBER
AND T2.D7P_IPLNK_CAPACITY = 'OP'
INNER JOIN MPD7REP T3
ON T3.D7P_SONG_SONG_CODE = T1.D7P_SONG_SONG_CODE
AND CASE WHEN DEC(SUBSTR(HEX(SUBSTR(T3.D7P_IPLNK_LINK_IP_NUMBER
,1,2)),1,3),3,0) IS NULL THEN 0
ELSE DEC(SUBSTR(HEX(SUBSTR(T3.D7P_IPLNK_LINK_IP_NUMBER
,1,2)),1,3),3,0)
END = T1.D7P_IPLNK_SEQUENCE_NUMBER
AND T3.D7P_IPLNK_CAPACITY = 'AM'
WHERE T1.D7P_SONG_SONG_CODE = SONG
AND T1.D7P_INTPTY_IP_NUMBER = WRITER
AND T1.D7P_IPLNK_SEQUENCE_NUMBER =
CASE WHEN DEC(SUBSTR(HEX(SUBSTR(T1.D7P_IPLNK_LINK_IP_NUMBER
,1,2)),1,3),3,0) IS NULL THEN 999
ELSE DEC(SUBSTR(HEX(SUBSTR(T1.D7P_IPLNK_LINK_IP_NUMBER
,1,2)),1,3),3,0)
END
AND T3.D7P_IPLNK_SEQUENCE_NUMBER - T2.D7P_IPLNK_SEQUENCE_NUMBER =
CASE WHEN (SELECT COUNT(*) FROM MPD7REP
WHERE D7P_SONG_SONG_CODE = T1.D7P_SONG_SONG_CODE
) < 99 THEN 10
ELSE 5
END;
DECLARE CONTINUE HANDLER FOR NOT_FOUND
SET AT_END = 1;
SET REPORTING_STRING = 'ZYX';
OPEN C1;
IF SUBSTR(SQLSTATE,1,2) NOT IN ('00','01','02') THEN
GOTO ENDPGM;
END IF;
RECORD_LOOP: LOOP
FETCH C1 INTO OP_IP, OP_PERC, AM_IP, AM_PERC, WRITER_PERC;
IF AT_END = 1 THEN
LEAVE RECORD_LOOP;
END IF;
IF SUBSTR(SQLSTATE,1,2) NOT IN ('00','01','02') THEN
LEAVE RECORD_LOOP;
END IF;
IF WRITER_PERC < OP_PERC THEN
SET OP_AOR = 100;
ELSE
SET OP_AOR = OP_PERC * 100 / WRITER_PERC;
END IF;
IF WRITER_PERC < AM_PERC THEN
SET AM_AOR = 100;
ELSE
SET AM_AOR = AM_PERC * 100 / WRITER_PERC;
END IF;
SET REPORTING_STRING = REPORTING_STRING || OP_IP || OP_AOR
|| AM_IP || AM_AOR;
END LOOP;
CLOSE C1;
ENDPGM: RETURN substr(REPORTING_STRING,4,157);
END
-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of Buck Calabro
Sent: 03 March 2015 17:06
To: Rational Developer for IBM i / Websphere Development Studio Client for System i & iSeries
Subject: Re: [WDSCI-L] Outline for SQL UDF's
On 3 March 2015 at 05:37, Austin, Mark <Mark.Austin@xxxxxxxxxx<mailto:Mark.Austin@xxxxxxxxxx>> wrote:
I am having some “fun” with SQL User Defines Functions (UDF) on the
iSeries
and have noticed I get no outline view for this source type. We use
turnover and
have configured it to have a source type of EXTFUN as per the turnover
instructions
but I was wondering if there we can get an outline view if I configure
this
source extension in RDi properly.
I'm not sure what you're storing in an EXTFUN member, but you can set your own parser associations in Preferences > LPEX Editor > Parsers > Parser Associations If it's pure SQL, then maybe you can try a Document Parser of 'sql'.
--buck
--
This is the Rational Developer for IBM i / Websphere Development Studio Client for System i & iSeries (WDSCI-L) mailing list To post a message email: WDSCI-L@xxxxxxxxxxxx<mailto:WDSCI-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx<mailto:WDSCI-L-request@xxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
http://archive.midrange.com/wdsci-l.
________________________________
“**Confidentiality**
The information contained in this e-mail is confidential, may be privileged and is intended solely for the use of the named addressee. Access to this e-mail by any other person is not authorised. If you are not the intended recipient, you should not disclose, copy, distribute, take any action or rely on it and you should please notify the sender by reply. Any opinions expressed are not necessarily those of the company.
We may monitor all incoming and outgoing emails in line with current legislation. We have taken steps to ensure that this email and attachments are free from any virus, but it remains your responsibility to ensure that viruses do not adversely affect you.
________________________________
**Confidentiality**
The information contained in this e-mail is confidential, may be privileged and is intended solely for the use of the named addressee. Access to this e-mail by any other person is not authorised. If you are not the intended recipient, you should not disclose, copy, distribute, take any action or rely on it and you should please notify the sender by reply. Any opinions expressed are not necessarily those of the company.
We may monitor all incoming and outgoing emails in line with current legislation. We have taken steps to ensure that this email and attachments are free from any virus, but it remains your responsibility to ensure that viruses do not adversely affect you.
midrange.com
