Birgitta,
iDate(b.indate,char('*YMD'))
gives me the same error and so does;
iDate(b.indate,Cast('*YMD' as CHAR(20)))
Thank you, any other thoughts?
Guy
Inactive hide details for "Birgitta Hauser"
<Hauser@xxxxxxxxxxxxxxx>"Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
"Birgitta Hauser" To: "'RPG programming on the
<Hauser@xxxxxxxxxxxxxxx> AS400 / iSeries'"
Sent by: <rpg400-l@xxxxxxxxxxxx>
rpg400-l-bounces@xxxxxxxxxxxx cc:
Subject: AW: SQLRPGLE question
10/14/2008 03:09 PM
Please respond to RPG
programming on the AS400 /
iSeries
Hi,
With which data types are the parameters of the function iDate defined?
I assume the second parameter is defined as CHAR?
You call your procedure with a constant value in parameter2. Character
constant values are interpreted as VARCHAR.
For SQL CHAR and VARCHAR are different data types. Because UDFs can be
overloaded, your function may not be found, because it is searched for a
function with a VARCHAR parameter.
Try to explicitly cast the second parameter:
Cast('MyConstValue' as CHAR(20)) or Char('MyConstValue')
Mit freundlichen Gru:ssen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Urspru:ngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
Im
Auftrag von GUY_HENZA@xxxxxxxxxxxxxx
Gesendet: Tuesday, 14. October 2008 20:25
An: alan0307d@xxxxxxxxxxx
Cc: rpg400-l@xxxxxxxxxxxx
Betreff: Re: SQLRPGLE question
This is the first time I'm trying to use a service program. I'm getting;
User-defined function IDATE cannot be invoked.
XVIDAT in IDATE type *SRVPGM not found.
Additional Message Information
Message ID . . . . . . : CPF426A Severity . . . . . . . : 50
Message type . . . . . : Escape
Date sent . . . . . . : 10/13/08 Time sent . . . . . . : 15:08:11
Message . . . . : User-defined function IDATE cannot be invoked.
Cause . . . . . : External program or service program XVIDAT in library
IDATE associated with user-defined function IDATE in library GLHLIB
cannot
be invoked for reason code 2. The reason codes and their meanings
follow:
2 -- Either the service program is not found or the program entry point
IDAT_PACKED does not exist.
I think I followed the directions to the letter.
what does "SQL7909 10 116 Position 3 Routine CONVERTTOIDATE was created,
but cannot be saved and restored." mean?
(See attached file: CRTIDATE673259.txt)(See attached file:
XVIDAT_M01669170.txt)(See attached file: XVIDAT_M02669170.txt)(See
attached file: XVIDAT_SQL669170.txt)
Here's my new select statement;
SELECT a.dprfno, a.dcusno, b.inpinv, b.inninv, d.rpag,
a.prefno, a.cusno, c.inpinv, c.inninv, e.rpag,
sum(a.price*a.shdqy)
FROM hdcinvpr a
join sc308wa b on a.dinvno = b.inpinv
and a.dcusno = b.incuno
join f0311 d on d.rpdoc = b.inpinv
and d.rpdctm = ' '
and iDate(d.rpdgj,'*JDEJUL') = iDate(b.indate,'*YMD')
join sc308wa c on a.invno = c.inpinv
and a.cusno = c.incuno
join f0311 e on e.rpdoc = c.inpinv
and e.rpdctm = ' '
and iDate(e.rpdgj,'*JDEJUL') = iDate(c.indate,'*YMD')
where b.inacod in (select plant from sc005pr)
and c.inacod in (select plant from sc005pr)
group by a.prefno, a.CUSNO, b.inpinv, b.inninv, d.rpag,
a.dprfno, a.dcusno, c.inpinv, c.inninv, e.rpag
order by a.prefno, a.CUSNO, b.inpinv, b.inninv, d.rpag,
a.dprfno, a.dcusno, c.inpinv, c.inninv, e.rpag
Regards,
Guy
Inactive hide details for "sjl" <sjl_abc@xxxxxxxxxxx>"sjl"
<sjl_abc@xxxxxxxxxxx>
"sjl" <sjl_abc@xxxxxxxxxxx> To: rpg400-l@xxxxxxxxxxxx
Sent by: cc:
rpg400-l-bounces@xxxxxxxxxxxx Subject: Re: SQLRPGLE
question
10/06/2008 05:12 PM
Please respond to RPG
programming on the AS400 /
iSeries
Guy Henza wrote:
I have a SQLRPGLE program that I need to add a JDE file to with a JDE
Julian date. How do I call a RPG III date conversion routine inside a
SQL
JOIN statement? Is it possible?
Guy -
Check out Alan Campin's iDate UDF. It will probably give you what you
need.
It can be found at www.think400.dk/downloads.htm
iDate is a SQL user defined function that converts an iSeries type of
date
to a SQL date data type. iDate support JDE type dates.
- sjl
----------------------------------------------------------------------------
---
The information contained in and transmitted with this e-mail may be
privileged, proprietary, confidential and protected from disclosure. No
privilege is hereby intended to be waived. This e-mail is intended only
for the person to whom it is addressed. If you are not the intended
recipient/addressee, any use of the e-mail and/or its contents,
including,
but not limited to, dissemination, distribution or copying is strictly
prohibited and may be unlawful, and you must not take any action in
reliance on it. If you receive this e-mail in error, please immediately
notify the sender and delete the original message and any copies of it
from your computer system. We deny any liability for damages resulting
from the use of this e-mail by the unintended recipient, including the
recipient in error.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
-------------------------------------------------------------------------------
The information contained in and transmitted with this e-mail may be
privileged, proprietary, confidential and protected from disclosure. No
privilege is hereby intended to be waived. This e-mail is intended only
for the person to whom it is addressed. If you are not the intended
recipient/addressee, any use of the e-mail and/or its contents, including,
but not limited to, dissemination, distribution or copying is strictly
prohibited and may be unlawful, and you must not take any action in
reliance on it. If you receive this e-mail in error, please immediately
notify the sender and delete the original message and any copies of it
from your computer system. We deny any liability for damages resulting
from the use of this e-mail by the unintended recipient, including the
recipient in error.
midrange.com
