|
Hi everyone--
Now that I have the SQL User Defined Function working The result of
this UDF gives me the next identification/batch number I need to use
the result of this in a number of SQL scripts My first thought was to
capture the result of this UDF and create a SQL variable
I read the following web page
https://stackoverflow.com/questions/47662356/create-a-temporary-variab
le-in-db2-400-v5r4-within-a-runsqlstm-source-file-mem
So I tried this
CREATE or REPLACE VARIABLE MYVARIABLE decimal default (select
NBTYGPL.NEXTNUMBERSQL('ALAN SHORE TEST') from SYSIBM.SYSDUMMY1) so
that I could use MYVARIABLE in a number of different SQL scripts
It doesn't like it
The error is
Use of function NEXTNUMBERSQL in NBTYGPL not valid.
F1 prompt on this message results in
Additional Message Information
Message ID . . . . . . : SQL0583 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Message . . . . : Use of function NEXTNUMBERSQL in NBTYGPL not valid.
Cause . . . . . : Function NEXTNUMBERSQL in NBTYGPL cannot be invoked
where
specified because it is defined to be not deterministic or contains
an
external action. Functions that are not deterministic cannot be
specified
in a GROUP BY clause or in a JOIN clause, or in the default clause
for a
global variable. Functions that are not deterministic or contain
an
external action cannot be specified in a PARTITION BY clause or an
ORDER BY
clause for an OLAP function and cannot be specified in the select
list of a
query that contains an OFFSET clause. The RAISE_ERROR function
cannot be
specified in a GROUP BY or HAVING clause.
Recovery . . . : Remove the function. Try the request again.
Bottom
Press Enter to continue.
Am I reading this right, I cannot use the SQL function in this method?
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: Alan Shore
Sent: Thursday, December 10, 2020 1:04 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] Re: Problem with an SQL UDF created from a
procedure within a service program
Thanks Charles
I thought that the rectification was the change of CHAR to VARCHAR
Learn something new every day
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On
Behalf Of Charles Wilt
Sent: Thursday, December 10, 2020 12:52 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXTERNAL] Re: Problem with an SQL UDF created from a
procedure within a service program
Alan,
Just wanted to make sure you realize that changing to from VALUE to
CONST was the actual fix.
CHAR vs VARCHAR isn't the problem...
Years back, having the proc defined with CHAR and trying to call it
with a literal from Run SQL Scripts would result in a "not found" error.
IBM has recently enhanced the implicit conversion the DB will do.
Since the RPG proc was invoked, you didn't have any issues passing
VARCHAR as a CHAR.
Charles
On Wed, Dec 9, 2020 at 1:02 PM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:
Okay - I figured out a way around this situationDefinition.
I created a NEW function NEXTNUMBERSQL and changed the PR/PI so that
Function is now varying Here is the code
P NextNumberSQL B export
D NextNumberSQL PI 12p 0
D Function 50 const
D Varying
D UseCommitmentControl...
D n const options(*nopass)
D HldNextNumber s like(nn_NextNum)
D HldFunction s 50
*- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
- -
* Open the appropriate next numbers file, as needed
HldFunction = %trim(Function);
select;
when %parms = 1;
HldNextNumber = NextNumber(HldFunction);
other;
HldNextNumber = NextNumber(HldFunction:
UseCommitmentControl);
endsl;
return HldNextNumber;
P NextNumberSQL E
Then I created the SQL function in the following manner CREATE or
replace FUNCTION NBTYGPL.NEXTNUMBERSQL ( INORD VARCHAR(50))
RETURNS DECIMAL(12, 0) LANGUAGE RPGLE SPECIFIC
NBTYGPL.NEXTNUMBERSQL NOT DETERMINISTIC NO SQL CALLED ON NULL
INPUT DISALLOW PARALLEL EXTERNAL NAME 'NBTYGPL/VALUE(NEXTNUMBERSQL)'
PARAMETER STYLE GENERAL
These changes work
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: Alan Shore
Sent: Wednesday, December 9, 2020 2:17 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] Re: Problem with an SQL UDF created from a
procedure within a service program
Apologies Charles
I thought had done that -
D NextNumber PR 12p 0
D Function 50 value
D UseCommitmentControl...
D n const options(*nopass)
D NextNumber PI 12p 0
D Function 50 value
D UseCommitmentControl...
D n const options(*nopass)
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On
Behalf Of Charles Wilt
Sent: Wednesday, December 9, 2020 2:00 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXTERNAL] Re: Problem with an SQL UDF created from a
procedure within a service program
again...post your PR/PI
Charles
On Wed, Dec 9, 2020 at 7:34 AM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:
Hi Charles
Thanks for your reply
Within STRDBG, not specifying the second parameter seems to be
working fine The procedure uses the following code
C Select
C When %parms = 1
C eval CommitmentControlIsActive = *off
C Other
C eval CommitmentControlIsActive =
C
UseCommitmentControl
C EndSL
And this looks to be working fine
According to Birgitta - the problem is how the input parameter is
defined in my function and How I am using the function in STRSQl
Tru=ing to rctify this situation - but having no luck
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On
Behalf Of Charles Wilt
Sent: Wednesday, December 9, 2020 9:27 AM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] Re: Problem with an SQL UDF created from a
procedure within a service program
You said it accepts two parms, but you only have 1 defined...
CALLED NULL INPUT probably isn't what you want with PARAMETER
STYLE GENERAL since there won't be anyway for your RPG program to
determine if a NULL is passed.
Bet to post the PR/PI of the RPG proc in addition to the SQL
----
Charles
On Wed, Dec 9, 2020 at 5:36 AM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:
Before I forget, we are on V7r3--
I have a procedure (NEXTNUMBER) within a service program
(VALUES) This is working in a number of programs I even created
a test program that uses this procedure, just so that I could
STRDBG and look at the logic flow It works It accepts 2
parameters, the second one being optional First parameter is a
char(50) parameter Second parameter is a logical parameter
Below is how I created the SQL function When I run the function
in STRSQL
select NBTYGPL.NEXTNUMBER('ALAN SHORE TEST') from
SYSIBM.SYSDUMMY1
Using STRDBG in another session (after using STRSRVJOB), I can
see that the first parameter is garbage Anyone any idea what I did wrong?
As always - all answers gratefully accepted
Here is how I created the SQL function
CREATE FUNCTION NBTYGPL.NEXTNUMBER ( INORD CHAR(50) ) RETURNS
NUMERIC(12, 0) LANGUAGE RPGLE SPECIFIC NBTYGPL.NEXTNUMBER NOT
DETERMINISTIC NO SQL CALLED ON NULL INPUT DISALLOW PARALLEL
EXTERNAL NAME 'NBTYGPL/VALUE(NEXTNUMBER)'
PARAMETER STYLE GENERAL
Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx 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 Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx 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 Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx 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 Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx 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 Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx 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 Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx 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 Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
