• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Table Value Constructors Build Tables On The Fly

    June 25, 2018 Ted Holt

    Because I wish to be as valuable and productive as possible to the people who pay me to program their computers, I continually search the Web for new ideas and techniques. Doing so often leads me to sites that cater to other computing platforms. Today I want to share with you some SQL techniques that I learned from Microsoft SQL Server professionals.

    These techniques are based on the Table Value Constructor (TVC), which is a group of data values, usually literals, organized into rows and columns. In its simplest form, a table value constructor is literal tabular data that is not stored in a database table.

    A table value constructor begins with the word VALUES, which is followed by one or more row value expressions.

    Assume REPS, a table of sales representatives, which contains decimal columns REPID and REGION. We can use a table value constructor to add a group of sales reps.

    insert into reps values
    (11, 1), (12, 3), (17, 4),
    (25, 2), (27, 2), (28, 4)
    

    Six sales reps have been assigned to regions 1 through 4.

    REPID REGION
    11 1
    12 3
    17 4
    25 2
    27 2
    28 4

    After a period of booming business and expansion into new parts of the globe, management revises the list of regions. This means that more region numbers are defined and some existing regions are assigned different region numbers. How would we go about reassigning the reps to the new region numbers?

    We could build a table of old and new region numbers and use that table as the source in a MERGE statement, as I wrote about in Updating Through A Join, Take Three. If I don’t need such a table for other purposes, I can use a table value constructor instead, like this:

    merge into reps as r
    using (values (1, 1), (2, 6), (3,4), (4,2))
      as changes (OldRegion, NewRegion)
      on r.region = changes.OldRegion
    when matched then
       update set r.Region = changes.NewRegion
    

    The system reassigned the reps using the literal values in the TVC.

    REPID REGION
    11 1
    12 4
    17 2
    25 6
    27 6
    28 2

    I learned something else that was interesting. I found out that I can use subselects instead of literals in table value constructors. Here’s a contrived example:

    insert into reps values (
    (select repid from shipments where shipmentid = 202), 4);
    

    The REPID value is retrieved from shipment 202, and whatever rep that is gets assigned to region 4.

    I haven’t use this technique in production yet, so if you do, please share your code with me. Also, if you have a TVC technique that I didn’t mention, please email me so I can share it with other readers.

    I’ve yet to see the terms table value constructor and TVC used in IBM literature, but I expect IBM to start using it one of these days. After all, how else will they let SQL Server users know that DB2 also has this feature?

    RELATED STORIES

    Updating Through A Join, Take Three

    Table Value Constructor (Transact-SQL)

    Table Value Constructors in SQL Server 2008

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: DB2, IBM i, SQL, table value constructor, TVC

    Sponsored by
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    866.378.3366

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    A Better Way To Skin The IBM i Cloud Cat Trinity Guard Gives Audit Tool A Friendly GUI

    2 thoughts on “Guru: Table Value Constructors Build Tables On The Fly”

    • Dietmar Bürkle says:
      July 4, 2018 at 12:42 pm

      Hi Ted,

      maybe you missed this article:
      Treasury Of New DB2 6.1 Features, Part 1: Query Enhancements
      https://www.itjungle.com/2009/02/04/fhg020409-story02/

      Dietmar

      Reply
    • Ted Holt says:
      July 30, 2018 at 8:14 am

      We revisit topics when we believe we can add to what we’ve already published.

      Reply

    Leave a Reply Cancel reply

TFH Volume: 28 Issue: 44

This Issue Sponsored By

  • Profound Logic Software
  • ARCAD Software
  • RPG & DB2 Summit
  • OCEAN TechCon18
  • WorksRight Software

Table of Contents

  • Lamps Plus Sheds Light On Modernization Integration
  • Trinity Guard Gives Audit Tool A Friendly GUI
  • Guru: Table Value Constructors Build Tables On The Fly
  • A Better Way To Skin The IBM i Cloud Cat
  • IT Spending To Boom In 2018, Tails Off In 2019

Content archive

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

Recent Posts

  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

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