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
 

I see these 2 possible problems:

1. In the create statement of your procedure you have not library specified
and then you call a procedure from QGPL:
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:

2. In your procedure you use as a path separator "/"
and when you call it you use as a path separator "."
the same with the select (without SP)
I wonder how it can work. Are you everytime changed your settings in iSeries Navigator?

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.

 

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.