• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Simulate a Boolean Data Type in a Database Table

    October 1, 2008 Hey, Mike

    I want to create a column in an SQL table and only allow two values. They could be 1 or 0, Y or N, or whatever. I don’t want the database to allow any other values into the column. I want the default to be the “false” value. How would you define such a column in SQL? I understand that Oracle supports a BOOLEAN data type, but I don’t think such exists on the AS/400.

    —Guru Reader

    You are correct, SQL Server has the BIT data type and Oracle has the BOOLEAN data type but there is no equivalent data type in DB2 for i. However, you can use a CHECK constraint to simulate a BOOLEAN type. Here’s an example:

    Create Table TestBoolean
      (Bool Numeric(1,0) Not Null Default 0 
      Constraint BoolCheck Check (Bool In (0,1)))
    

    In this case, the CHECK constraint mandates the value of column Bool to contain a 0 or 1 (identical to the behavior of SQL Server’s BIT data type). Of course DEFAULT will give your column a default value of 0 if one isn’t explicitly given. Keep in mind that a constraint name must be unique within a schema. Therefore you won’t be able to specify BoolCheck as a constraint name on more than one table within the same schema (library).

    You can also emulate a BOOLEAN value with a character field that contains the values ‘Y’ and ‘N’, such as:

    Create Table TestBoolean
      (Bool Char(1) CCSID 37 Not Null Default 'N'
      Constraint BoolCheck Check (Bool In ('Y', 'N')))
    

    Now, when you insert or update a value into this column that is not allowed by the check constraint you get the following error:

    SQL0545 INSERT or UPDATE not allowed by CHECK constraint.
    

    If you have multiple fields with the same CHECK requirements, you can implement the validation for all three columns within one constraint:

    Create Table TestBoolean
      (Bool1 Numeric(1,0) Not Null Default 0, 
       Bool2 Numeric(1,0) Not Null Default 0, 
       Bool3 Numeric(1,0) Not Null Default 0, 
      Constraint CheckFlags Check(Bool1 In (1,0) And
                          Bool2 In (1,0) And Bool3 In (1,0)))
    

    Of course you can vary this example to allow NULLs if required.

    Finally, for compatibility with other database servers, one other possible variation is to create a distinct type called BIT. (You can’t use the name BOOLEAN because it is an SQL reserved keyword–maybe IBM intends to give us a BOOLEAN data type in the future.) However, when implementing the distinct type you will still need to specify the CHECK constraint at the table level to enforce the allowed values. Also, distinct types require more processing overhead. See the documentation for the CREATE TYPE command for more info.

    –Mike

    Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Michael to Ted Holt via the IT Jungle Contact page.



                         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
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    MoshiMoshi:  Episode Three now showing! Watch and Win!
    COMMON:  Join us at the Focus 2008 workshop conference, October 5 - 8, in San Francisco, California
    Vision Solutions:  A $20 gas card for completing a short i5/OS DR survey

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.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 Developers' 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
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    TMW Systems Certifies ALK Technologies’ Truck Routing Software Bytware Bought by Help/Systems and Audax

    Leave a Reply Cancel reply

Volume 8, Number 33 -- October 1, 2008
THIS ISSUE SPONSORED BY:

WorksRight Software
System i Developer
Bug Busters Software Engineering

Table of Contents

  • Keep Your Hands on the Keyboard with RSE
  • Simulate a Boolean Data Type in a Database Table
  • Admin Alert: When System Job Tables Attack, Part II

Content archive

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

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • 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

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