Re: SQL long column name band-aids

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.