Re: SQL long column name band-aids

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.