RE: Something in SQl that I assumed woulld work

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


Oh
By the way
Thanks for the reply Rob

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@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Monday, May 08, 2017 3:36 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Something in SQl that I assumed woulld work

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.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Alan Shore <ashore@xxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 05/08/2017 03:30 PM
Subject: Something in SQl that I assumed woulld work
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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