Last active
May 27, 2016 02:47
-
-
Save phpdave/8822f8a4d9b0cdf69f738fff49aa2f19 to your computer and use it in GitHub Desktop.
UDTF to get Key Physicals
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Followed Birgitta's article @ https://www.ibm.com/developerworks/ibmi/library/i-power-of-udtf/ | |
CREATE FUNCTION DisplayKeyedPhysicals_Fnc(ParTable VarChar(10)) | |
RETURNS TABLE (FieldWeWant1 Char(10) ) | |
LANGUAGE SQL | |
MODIFIES SQL DATA | |
BEGIN | |
DECLARE CLCmd VarChar(256) Not NULL Default ''; | |
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION | |
BEGIN | |
DECLARE ERROR_HIT INTEGER; | |
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION | |
SET ERROR_HIT = 1; | |
CREATE TABLE QTEMP.TMPOBJOWN ( | |
FieldWeWant1 Char(10)); | |
END; | |
SET CLCmd = 'DSPFD FILE(MYLIB/' concat ParTable concat ') ' | |
Concat ' OUTPUT(*OUTFILE) ' | |
Concat ' OUTFILE(QTEMP/TMPOBJOWN) ' | |
Concat ' OUTMBR(*FIRST *REPLACE) '; | |
CALL QCMDEXC(CLCmd, LENGTH(CLCmd)); | |
RETURN SELECT FieldWeWant1 | |
FROM QTEMP.TMPOBJOWN; | |
END; | |
SELECT * FROM TABLE(DisplayKeyedPhysicals_Fnc('MYTABLE')) x; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment