The => means you associate a named parameter (e.g. SQL_Statement, or
Output_file) with a value.
Here is the documentation from the SQL Reference:
argument-list
Identifies a list of values to be passed as parameters to the procedure. The
nth unnamed argument
corresponds to the nth parameter in the procedure.
Each parameter defined (using a CREATE PROCEDURE or DECLARE PROCEDURE
statement) as OUT
must be specified as a variable. A default cannot be specified for an OUT
parameter. If a default is
used for an INOUT parameter, then the default expression is used to
initialize the parameter for input
to the procedure. No value is returned for this parameter when the procedure
exits.
When a procedure is called, arguments must be specified for all parameters
that are not defined to
have a default value. When an argument is assigned to a parameter using the
named syntax, then all
the arguments that follow it must also be assigned using the named syntax.
Any references to date, time, or timestamp special register values in the
argument list will use one
clock reading for any default expressions and a separate clock reading for
any references in the
explicit arguments.
If any argument is an array, all other arguments must be literals, special
registers, NULL, or DEFAULT.
The application requester assumes all parameters that are variables are
INOUT parameters except for
Java, where it is assumed all parameters that are variables are IN unless
the mode is explicitly
specified in the variable reference. All parameters that are not variables
are assumed to be input
parameters. The actual attributes of the parameters are determined by the
current server.
parameter-name
Name of the parameter to which the argument value is assigned. The name must
match a
parameter name defined for the procedure. Named arguments correspond to the
same named
parameter regardless of the order in which they are specified in the
argument list. When an
argument is assigned to a parameter by name, all the arguments that follow
it must also be
assigned by name.
A named argument must be specified only one time (implicitly or explicitly).
Named arguments are only allowed on a call to a procedure that was defined
using a CREATE
PROCEDURE statement.
expression
An expression of the type described in ?Expressions? on page 156, that does
not include an
aggregate function or column name. If extended indicator variables are
enabled, the extended
indicator variable values of DEFAULT and UNASSIGNED must not be used for
that expression.
DEFAULT
Specifies the default as defined in the CREATE PROCEDURE statement is used
as an argument to
the procedure. If no default is defined for the parameter, the NULL value is
used.
NULL
Specifies a null value as an argument to the procedure.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Steve
Richter
Sent: Donnerstag, 6. Februar 2020 23:12
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Create a table from a stored procedure result set with dynamic
columns
the gist code is very neat.
what does the arrow operator do?
-- Usecase:
call qusrsys.sql_to_csv(
sql_statement => 'SELECT * FROM QIWS.QCUSTCDT', output_file =>
'/tmp/test.csv'
);
On Thu, Feb 6, 2020 at 3:37 PM Niels Liisberg <nli@xxxxxxxxxxxxxxxxx> wrote:
You can look at my gist. The CSV export is doing similar task what you
are looking for
https://gist.github.com/NielsLiisberg/3bc04d45c39a816c4b52760d6f861c8b
tor. 6. feb. 2020 kl. 21.22 skrev Steve Richter
<stephenrichter@xxxxxxxxx
:
dynamically create the sql select statement as a string. Then use
PREPARE
and OPEN to run the statement.
CREATE or replace PROCEDURE test_Select( in inCucode char(6)
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
declare vSqlCode decimal(5,0) ;
declare sqlCode int DEFAULT 0 ;
DECLARE QT CHAR(1) DEFAULT '''' ;
declare vStmt char(5000) default ' ' ;
DECLARE C1 CURSOR FOR s1 ;
set vStmt =
' select a.orhordnum ordnum, a.orhcust cucode ' ||
' from dwhpf30c a ' ||
' where ( ? = '' '' or ? = a.orhcust) ' ||
' order by a.orhordnum ' ||
' fetch first 50 rows only ' ;
prepare s1
from vStmt ;
OPEN C1
using inCucode, inCucode ;
SET RESULT SETS WITH RETURN TO CLIENT CURSOR C1 ;
END
On Thu, Feb 6, 2020 at 12:36 PM Ramiro Blanco
<r_blanco@xxxxxxxxxxxxx>
wrote:
Hi,
I have an SQL stored procedure that successfully creates a result
set (pivot
data) which returns dynamic columns.
How can I create a table based on the result set where the columns
can vary?
Thank you.
--
This is the RPG programming on IBM i (RPG400-L) mailing list To
post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post
a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
