Re: Something in SQl that I assumed woulld work

On 08-May-2017 13:39 -0600, Alan Shore wrote:

On 08-May-2017 13:35 -0600, Rob Berendt wrote:

On 08-May-2017 13:14 -0600, Alan Shore wrote:

Before I forget, we are on V7r1

Let me lay the groundwork first

We have a procedure in a service program that if you input the
suffix and product number, the available quantity is returned
From that procedure. I created an SQL function on this procedure

I have no problems running the following sql script

Select Isuffx, skuno, idescr
, itemavail(isuffx, skuno) as Available_qty
from prodfa.itmmstp
where isuffx = '0034'

Where isuffx is the suffix, skuno is the product number,
itemavail is the sql function and prodfa.itmmstp is the item
master

We have a separate suffix (0049) that is used as a reserve - so I
was wanting to display that available quantity at the same time
So I tried

Select Isuffx, skuno, idescr
, itemavail(isuffx, skuno) as Available_qty
, itemavail('0049', skuno) as Avail_qty0049
from prodfa.itmmstp
where isuffx = '0034'

and within STRSQL I get the message "ITEMAVAIL in *libl type *N
not found", with the cursor on the sql function with 0049
specified

I was just wondering if this is the way the script SHOULD be
giving this response?

CAST a spell over it oh wizard of sql and see if that resolves it.

itemavail(cast ('0049' as char(4)), skuno) as Avail

Select Isuffx, skuno, idescr
, itemavail(isuffx, skuno) as Available_qty
, itemavail(cast ('0049' as char(4)), skuno) as Avail_qty0049
from prodfa.itmmstp
where isuffx = '0034'

Study "overloading" if you want the detail answer.

Ah -ha Bingo, Gringo THAT was it.
Obviously my next question is - why does this need to be done?

Other responses clarify an enhancement (not available on the noted IBM i 7.1) and more. None however, showed an example of a simple invocation, available on v7r1, to /resolve/ the concern without either changing the invoked external procedure or having to use explicit-cast on the invocation (the following is untested and clearly incomplete, per not having been given the original UDF definition); offered here, because trying to find the "sourced" from searching "overloaded" in the KnowledgeCenter would likely prove unfruitful or at least quite indirect, because as the latter term does not even appear in the doc link included below:

create function itemavail /* same routine name */
( sfx varchar(04) /* enable character constant as VARCHAR */
, sku … ) /* unchanged 2nd parameter */
returns … /* same return data type */
specific itemavail_VC /* optional naming; e.g. enable */
/* DROP SPECIFIC vs by matching parameters */
source itemavail /* "source" original UDF with CHAR() */
( char(04)
, … ) /* 2nd parm type; as in original/"sourced" */

For additional reference:
[https://archive.midrange.com/midrange-l/201204/msg00678.html](SQL UDF - hardcoded parameters)
[https://archive.midrange.com/midrange-l/201204/msg00689.html](SQL UDF - hardcoded parameters)
[https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzcfso.htm](IBM i->IBM i 7.1->Database->Reference->SQL reference->Statements ->CREATE FUNCTION (Sourced))