Re: Something in SQl that I assumed woulld work

[Robert Rogerson <rrogerson@xxxxxxxxxxx>]

Hi Alan,

Could the problem be that when you try to call the procedure with '0049' 
the type must be CAST?

Rob


On 5/8/2017 3:14 PM, Alan Shore wrote:
> Hi everyone
> 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?
>
> I realize that I can create a CTE with a join - but was just wondering
>
> 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