Skip to content

Instantly share code, notes, and snippets.

@phpdave
Last active May 27, 2016 02:47
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save phpdave/8822f8a4d9b0cdf69f738fff49aa2f19 to your computer and use it in GitHub Desktop.
Save phpdave/8822f8a4d9b0cdf69f738fff49aa2f19 to your computer and use it in GitHub Desktop.
UDTF to get Key Physicals
-- 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