• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Joining Incompatible Data

    January 13, 2010 Hey, Ted

    A physical file contains a nine-byte character acknowledgement number field that consists of a single letter followed by eight digits. I would like to change this field to two fields: a one-byte character and an eight-byte numeric field so that I could join the file to other files that have only the eight-digit numeric value. I realize that I could do a conversion to redefine the field, but that would require extensive changes to our applications. Is there an easier method to redefine the field, such as in a logical file?

    –David

    You probably don’t have to redefine the field, David. It depends on the tools you use to join the files.

    SQL, for example, permits you to join character to numeric. Query for i, or whatever they call it these days, doesn’t.

    Let’s look at an example. Assume two database files, REF and ACK. REF contains the nine-byte character field you described (REFNO), and ACK contains the eight-digit numeric field (ACKNO). The following SQL command joins the two files.

    select r.whatever, a.whatever
      from ref as r
      left join ack as a
        on substr(r.refno,2,8) = a.ackno
    

    While this works, I prefer to be more obvious about it.

    select r.whatever, a.whatever
      from ref as r
      left join ack as a
        on decimal(substr(r.refno,2,8),8,0) = a.ackno
    

    If you do need a permanent object, you won’t be able to use a logical file, because a logical file will not convert an alpha substring to numeric. However, an SQL view will.

    create view RefAcks as
    select r.key, 
           substr(r.refno,1,1) as RefCode,
           decimal(substr(r.refno,2,8),8,0) as RefAckNo
    from ref as r
    

    You can use the view in SQL queries, just as you can a table.

    select r.whatever, a.whatever
      from refacks as r
      left join ack as a
        on r.refackno = a.ackno
    

    And the view will work in those tools, like Query, that require join fields to be of compatible data types.

    SQL views do lots of things that logical files can’t do. It’s not too late to make another New Year’s resolution: when possible use SQL views instead of logical files.

    –Ted

    RELATED STORIES

    Joining on Incompatible Fields in Query/400

    Common Sense Comparisons



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Help/Systems:  Event-driven job scheduling for UNIX, Linux, Windows & IBM i servers
    COMMON:  Join us at the annual 2010 conference, May 3 - 6, in Orlando, Florida
    Manta Technologies:  Year-End SALE! 40% off the complete library and all combo packs. Ends Jan 15

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Sterling Targets Smartphone-Carrying Consumers with New Mobile E-Commerce Apps The System iWant, 2010 Edition: Big Boxes

    Leave a Reply Cancel reply

Volume 10, Number 2 -- January 13, 2010
THIS ISSUE SPONSORED BY:

System i Developer
Vision Solutions
Halcyon Software

Table of Contents

  • Consume an IWS Web Service From a VB.NET Client
  • Joining Incompatible Data
  • FTPING i5/OS Files to a Unix Server

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • To Comfort The Afflicted And Afflict The Comfortable
  • How FalconStor Is Reinventing Itself, And Why IBM Noticed
  • Guru: When Procedure Driven RPG Really Works
  • Vendors Fill In The Gaps With IBM’s New MFA Solution
  • IBM i PTF Guide, Volume 27, Number 27
  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle