Hints and Tips update (aka, SQL on #ibmi and #sqlserver: divided by a common language)

Decided to take a nice 'split string' table function I'd borrowed from some kind person on the internet for the Coordinate My Care data warehouse (SQL Server), and use it as a basis for a similar function to split multi-value attributes on IBM i, e.g. list of special authorities on a user profile. Cue a frustrating, if eventually successful, afternoon.

I really don't like #ibmi table functions: for starters, why can't I INSERT direct into the table to be returned, as in #sqlserver ?

However I did learn a few useful things that are worth sharing I think:

#ibmi Hints and Tips #18: create/replace table in QTEMP from SQL:
DECLARE GLOBAL TEMPORARY TABLE [table] ([col] [type], ... ) WITH REPLACE
(You can then use the table absolutely normally as QTEMP.[table].)

#ibmi Hints and Tips #19: FOR var1 as cur1 CURSOR FOR [SELECT statement] DO
[whatever, using column names direct from result set]; END FOR;
(I don't know whether you can do this in SQL Server, but I am definitely going to investigate.)

#ibmi Hints and Tips #20: table function usage: esp. note the final identifier (x here):
SELECT * FROM TABLE(MyTableFunction(parm1, ...)) x
(Don't like this super complicated syntax, either!)