Problem with JDE date conversion on iSeries

[snufse1]

I have created a sp that should return a JDE date in yyyymmdd format (*ISO). Here is the sp:

create procedure get_jobdet
(in @jobnumber char(12),
@costcode char(8))
result set 1
language sql
reads sql data
begin
declare c1 scroll cursor with return for
select glmcu, glsub, glu,
date(digits(dec(gldgj + 1900000,7,0)))
from vgiprddta/f0911li
where glmcu = @jobnumber and
glsub = @costcode and
gldct = 'FP' and
globj = ' '
group by glmcu, glsub, glu, gldgj;
open c1;
end;

Now when I call the sp using the iSeries navigator (or using linked server on Sequel Server 2005) like this:

call qgpl.get_jobdet(' 1100281', '0080 ')

I get following result:

1100281 0080 8000 0813-09-30
1100281 0080 50000 0113-09-30

The date being returned (last field) is not correct


Now if I call with a "select...) like this:

select glmcu, glsub, glu, date(digits(dec(gldgj + 1900000,7,0)))
from vgiprddta.f0911li
where glmcu = ' 1100281' and
glsub = '0080 ' and
gldct = 'FP' and
globj = ' 'I get a good result for the date:

1100281 0080 8000 2007-01-31
1100281 0080 50000 2008-08-31

I do not understand why my sp is not working on the date convert when my select is working and basically do the same thing. I've been working on this probelm for several days, does anyone have some good tips? Thank you

 

[mikrom]

I see these 2 possible problems:

1. In the create statement of your procedure you have not library specified

create procedure get_jobdet
...

and then you call a procedure from QGPL:

call qgpl.get_jobdet('     1100281', '0080    ')

Do you call the same one as you created or an older one?
Proove if your procedure was truly created in QGPL and not in other library (e.g. your *curlib).
To be sure that you create it in QGPL specify in your create statement the schema:

create procedure QGPL.get_jobdet
...

2. In your procedure you use as a path separator "/"

...
from vgiprddta/f0911li
...

and when you call it you use as a path separator "."

call qgpl.get_jobdet('     1100281', '0080    ')

the same with the select (without SP)

...
from vgiprddta.f0911li 
...

I wonder how it can work. Are you everytime changed your settings in iSeries Navigator?

 

[snufse1]

1. I create my procedure in QTXT and use the RUNSQLSTM, by default that created the procedure in QGPL

2. From the Iseries Navigator I have to use "." as a seperator and in my sp "/", just a different syntax

3. There is only one sp out there (in QGPL) and both of the examples above calling the "sp" or doing a "select from ..." work.

The problem is in the "sp" call correct date is not being returned. Thank you.

 

[snufse1]

Hi Microm,

Think I eventually figured it out.

I am scripting my sp in QTXT and I call the "RUNSQLSTM" to check syntax and create my procedure in QGPL (by default). I started looking at optional parameters creating the proc and changed the "Date Format" attribute to *ISO (by default it was set to *JOB). Then I ran the proc from the iSeries navigator and from SQL Server 2005 and both rendered a valid and correct date.

Thank you for yor suggestions.