Re: SQL long column name band-aids

Unless you love renaming them in D specs...

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





From:
Charles Wilt <charles.wilt@xxxxxxxxx>
To:
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:
07/08/2009 03:36 PM
Subject:
Re: SQL long column name band-aids
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



Aaron,

Does the third part package or any existing customer programs use RPG
RLA for I/O now?

In other words, is anything currently using the existing short field
names?

If not, then I'd recreate them with readable short field names. The
existing ERP package that uses the long SQL names won't care.

If that's really not an option, then probably the next best solution
would be to create a DDS logical that renames the fields.

HTH,
Charles



On Wed, Jul 8, 2009 at 3:09 PM, Aaron Bartell<aaronbartell@xxxxxxxxx>
wrote:

So this week I find myself working with a third part ERP package that

had

their tables in DB2/400 created with SQL DDL. The column names are

much

longer than the 10 chars RPG supports so I have been looking for a way

to

remedy the solution. Obviously I could just use the generated 10 char
column names but those make code very unreadable so I am not going to

use

that option.

Note I don't have the ability to change the SQL DDL of these tables

and

recreate them, so anything I do has to be a veneer of sorts.

My first idea was to use SQL Views as those allow me to use the FOR

COLUMN

to alias the field down to 10 chars. For example:

CREATE VIEW CHRTACCT1V (
BA_RECCODE FOR COLUMN RECCOD,
SUB_NO FOR COLUMN SUBNO ,
ACCT_NO FOR COLUMN ACTNO ,
ACCT_TYPE FOR COLUMN ACTTYPE )
AS
SELECT
COALESCE(BA_RECCODE, 0) AS BA_RECCODE,
COALESCE(SUB_NO, 0) AS SUB_NO,
COALESCE(ACCT_NO, 0) AS ACCT_NO,
COALESCE(ACCT_TYPE, 0) AS ACCT_TYPE
FROM CHRTACCT;

Note: The COALESCE is to get rid of NULL values and replace them with

the

columns default. This makes it easier to deal with in RPG.

This worked fine for 10 minutes until I came across the scenario where

I

needed to do a SETLL and READE on the CHRTACCT1V View. Views can't

have

indexes built on them. The tables definition doesn't have the short
column names so I can't create an index over it without having to

resort

to the obscure column names.

Now, I know I can resort to doing embedded SQL in my RPG but I would
prefer to just use native opcodes as that keeps the code cleaner. Do

any

of you SQL geeks have some pointers for me?

Aaron Bartell
[1]http://mowyourlawn.com

p.s. If you want to see me shave parts of my body please head over to
[2]http://firstgiving.com/aaronbartell to learn how to make it happen.
Yes it sounds gross, but it is for a good cause!

References

Visible links
1. http://mowyourlawn.com/
2. http://firstgiving.com/aaronbartell
--
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.

--
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.



--
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.