Re: RPG numeric types mapping to SQL types

It's something you did.  I tried this on a V5R3 machine and it worked 
great.  I even redid all the following below to change from 7p0 to 1p0 to 
see if it was something funky with a packed 1 field.

CREATE TABLE ROB/TONY1
(
T1CHAR CHAR (10 ) NOT NULL WITH DEFAULT,
T1DEC DEC (7 , 0) NOT NULL WITH DEFAULT
)

INSERT INTO ROB/TONY1 VALUES('A', 5)

     D SKELETON        PR             1a
     D  Parm1                         7p 0
     P SKELETON        B                   EXPORT
     D SKELETON        PI             1a
     D  Parm1                         7P 0
      /free
       Select;
         When Parm1=5;
           return 'B';
         When Parm1=2;
           Return 'C';
         Other;
           Return 'D';
       ENDSL;
      /end-free
     P SKELETON        E

CRTRPGMOD MODULE(ROB/TONYSRV) SRCFILE(ROB/QPGMSRC)

CRTSRVPGM SRVPGM(ROB/TONYSRV) MODULE(ROB/TONYSRV) EXPORT(*ALL)

CREATE FUNCTION ROB/FIGURE 
( DEC(7,0)) 
RETURNS CHAR (1) 
LANGUAGE RPGLE 
DETERMINISTIC 
NO SQL 
RETURNS NULL ON NULL INPUT 
SIMPLE CALL 
EXTERNAL NAME 'ROB/TONYSRV(SKELETON)'

SELECT T1CHAR, T1DEC, FIGURE(T1DEC)
FROM ROB/TONY1 

....+....1....+....2....+....3....+....4
T1CHAR          T1DEC   FIGURE ( T1DEC )
A                   5          B 
********  End of data  ******** 

PTF Group             Level  Status 
SF99530                5032  Installed 
SF99529                  38  Installed 
SF99503                   3  Installed 
SF99295                   2  Installed 
SF99287                   7  Installed 
SF99282                   3  Unknown 
SF99269                   4  Installed 
SF99185                   4  Installed 
SF99173                   1  Not applicable
SF99139                   1  Installed 
SF99099                   2  Installed 

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Tony Carolla <carolla@xxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
02/11/2005 04:36 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RPG numeric types mapping to SQL types






I've created a module that will be placed in a service program, and
created an SQL Function that makes the service program module callable
from within an SQL view.  The problem is that, as you can see from the
prototype, I am using packed decimals, which translate to DECIMAL
types in SQL.  Even if I use fields from a database file that are
packed decimal, I have to CAST them in SQL to DECIMAL.  What RPG
number type can I use to avoid this CASTing in SQL?

Prototype follows:

     D DLMCCalc        PR             7P 2
     D  MedNecLvl                     1P 0 Const
     D  EMLvlCharg                    7P 0 Const
     D  Acronym                       3A   Const
     D  Family#                       9P 0 Const
     D  Member#                       2P 0 Const
     D  SvcDate                        D   Const

The SQL function creation was done like this:

Create Function DMart.CalcDCost 
(                DEC (1,0), 
                 DEC (7,0),
                 CHAR (3),
                 DEC (9,0),
                 DEC (2,0),
                 DATE)
Returns          DEC (7,2)
Language RPGLE
Deterministic
no sql
returns null on null input
no external action allow parallel
simple call
External Name 'DMART/DL_FUNC(DLMCCALC)';

Now, the pain in the rear, is that to call the function, I have to
code it in the SQL as follows:

DMart.CalcDCost(Cast(Log.MedNecLvl As DECIMAL(1,0)),
                                 Cast(Log.EMLvlCharg As DECIMAL(7,0)),
                                 Log.SysAcr,
                                 Cast(Log.Famly# As DECIMAL(9,0)),
                                 Cast(Log.Membr# As DECIMAL(2,0)), 
                                 Log.Date1Post)


-- 
"Enter any 11-digit prime number to continue..."
-- 
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.