• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Global Temporary Tables and Host Variables

    March 31, 2010 Ted Holt

    Global temporary tables are a marvelous, powerful feature of SQL, allowing SQL programmers to create temporary tables as needed in order to build complex queries. One problem you will encounter occurs when you want to use a host variable within a program to limit the amount of data loaded into the table. You may not use a host variable in a Declare Global Temporary Table command. Fortunately, this is an easy limitation to circumvent.

    First, let’s look at an example of the problem. In the following code fragment, I attempt to create a global temporary table using data for one customer only.

    D MyPgm
    D  inCompany                     3p 0 const
    D  inCustomer                    5p 0 const
    
     /free
         exec sql
            drop table session/Temp1;
         exec sql
            declare global temporary table Temp1 as
                (select *
                   from sales
                  where companyno  = :inCompany
                    and customerno = :inCustomer)
              with data;
    

    This does not compile. In fact, even SEU won’t accept it.

    The solution is to create the file and load it in two steps. Here’s one possibility.

    exec sql
        drop table session/Temp1;
    exec sql
        declare global temporary table Temp1 as
           (select * from sales)
         definition only;
    exec sql
        insert into session/Temp1
           (select *
              from sales
             where companyno  = :inCompany
               and customerno = :inCustomer);
    

    The phrase “definition only” in the declaration tells the system to create the table based on the fields in the select clause, but not to put any data into the table. Once the table exists, I load it with an insert, which does accept host variables.

    I rarely use this technique. In this example, I intentionally kept the select simple, pulling data from one table only, but it would be more realistic to pull data from more than one table, like this:

    select h.companyno, h.customerno, h.saledate, h.shipdate,
           h.termcode, t.termdesc,
           co.custname, co.class,
           co.address1, co.address2, co.city,
           co.state, co.postalcode, d.lineno, d.itemno,
           d.quantity, d.price,
           d.quantity * d.price as extended,
           i.itemdesc, i.itemtype, i.itemclass
      from saleshdr as h
      join customer as cu
        on cu.companyno  = h.CompanyNo
       and cu.customerno = h.CustomerNo
      join salesdtl as d
        on h.orderno = d.orderno
      join items as i
        on d.itemno = i.itemno
      join company as co
        on cu.companyno = co.companyno
      join term as t
        on h.termcode = t.termcode
     where companyno  = :inCompany
       and customerno = :inCustomer);
    

    Imagine replacing both of the select statements in the first example with that or worse. The first select would not have to have the where clause, but having to keep the rest of the two instances of the select in sync when changing the query seems less than ideal to me. The first technique is OK for very simple queries, but most queries are not simple.

    Here’s a second technique that uses similar principles and doesn’t require duplication of the select statement.

    exec sql
        drop table session/Parms;
    exec sql
        declare global temporary table Parms
           (CompanyNo  dec(3,0), CustomerNo dec(5,0));
    exec sql
        insert into session/Parms
           values(:inCompany, :inCustomer);
    exec sql
        drop table session/Temp1;
    exec sql
        declare global temporary table Temp1 as
           (select h.*
              from session/Parms as p
              join sales as h
                on p.companyno  = h.CompanyNo
               and p.customerno = h.CustomerNo)
         with data;
    

    In this example, I create two temporary files–Temp1 to hold the sales data and another one called Parms. The Parms table has only one row (record), into which I load the values of the host variables. From there, I use joins (often an inner join, as in this example, but also often a cross join) to access the host variable values in queries.

    I find this technique more useful, especially when the values of the host variables are called for in the building of many temporary tables within a program.

    The ability to create global temporary tables is a great boon for SQL programming, and fortunately, the lack for support for host variables in global temporary table definitions is not a show-stopper.



                         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
    Racksquared

    It’s time for IBM Power in the Cloud!

    Stop buying hardware and make the move to the cloud. It’s easier and more cost effective than you might think.

    • IBM Power in the Cloud
    • IBM Power Backup Solutions
    • IBM Power High Availability and DR solutions
    • IBM Power Colocation with Management and Monitoring

    Let’s talk about your business needs.

    Call: 855-380-7225
    Email: Sales@racksquared.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Help/Systems:  Robot/CONSOLE monitors System i resources automatically
    Northeast User Groups Conference:  20th Annual Conference, April 12 - 14, Framingham, MA
    COMMON:  Join us at the annual 2010 conference, May 3 - 6, in Orlando, Florida

    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

    Source Technologies Now Supports System i with MICR Printer IBM Holds i 6.1 Prices Steady, Slashes Application Server Fees

    Leave a Reply Cancel reply

Volume 10, Number 12 -- March 31, 2010
THIS ISSUE SPONSORED BY:

WorksRight Software
ProData Computer Services
Northeast User Groups Conference

Table of Contents

  • Variable Procedure Calls in Free-Format RPG
  • Global Temporary Tables and Host Variables
  • Implementing 128-Character Passphrases in i/OS

Content archive

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

Recent Posts

  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13
  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12

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 © 2023 IT Jungle