RE: Something in SQl that I assumed woulld work

Alan,

With SQL you can have different functions with the same name and different
parameters (Rob's reference to overloading)
Check the archives for the IDate function.

Regards,

Luis

Sent from my Moto G phone. Please excuse my brevity.


On May 8, 2017 16:41, "Alan Shore" <ashore@xxxxxxxx> wrote:

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD