NuoDB Docs
NuoDB
NuoDB

NuoDB Docs

      • NuoDB At a Glance
      • Samples Directory
      • Docker
      • Docker Compose
      • Kubernetes
        • RPM Package Install
        • TAR File Install
      • New Features
      • Changes
      • Resolved Issues
      • Preview Features
      • Known Issues
      • Deprecated Features
      • End of Life Features
      • Legal Notices
        • Admin Platform
        • Networking
        • Atoms
        • Data Access and Replication
        • Archive Synchronization
        • Data Partitioning
        • Archive
        • Indexing
      • Data Caching
        • Visibility
        • Commit Protocol
        • Vectorized Execution Engine
        • Optimizer v3
        • High-level SQL Pipeline
        • Data-To-Code Execution
        • Transactional DDL
        • System Requirements
          • Obtain and Install a Product License
            • Using the RedHat Package Manager to Install or Upgrade NuoDB
            • Using a Linux TAR File to Install NuoDB
            • Resolving Warnings About Previously Used NuoDB IDs
            • Post-Installation Tasks on Linux
            • Using an EXE File to Install NuoDB on Windows
            • Using a ZIP File to Install NuoDB on Windows
            • Prepare to Upgrade
              • Upgrade with Databases Down
              • Rolling Upgrade: Databases Remain Available
            • Upgrade the Database Protocol
          • Downgrade to a Previous Release
            • Uninstalling NuoDB on Linux
            • Uninstalling NuoDB on Windows
          • Configuring NuoDB Admin
            • Generating Keystores and Truststores: Shared Admin Key
            • Generating Keystores and Truststores: Unique Admin Key
            • Using Certificates Signed by a Public Certificate Authority
            • Rotating Key Pair Certificates
            • Managing TLS Security
          • Creating the Domain and Adding Hosts
          • Starting and Stopping NuoDB Admin
            • Displaying Domain, Database, and Archive Status
            • Displaying Durable Domain Configuration Information
            • Displaying Database Process Information
            • Displaying Database Engine Attributes
          • Performing Health Checks on Domain and Database Status
          • Resetting the NuoDB Domain State
            • Determining if Admin Process (AP) Quorum Exists
            • Handling Unreachable Processes
            • Removing an Unreachable Admin Server from the Durable Domain
            • Re-establishing Admin Process (AP) Quorum
            • Creating an Archive
            • Creating a Database
            • Validating Database Archives
          • Confirming Domain and Database Status
          • Shutting down a Database
          • Restarting a Database
          • Starting a Storage Manager (SM)
          • Starting a Transaction Engine (TE)
          • Migrate to LSA
          • Extending the Database Across Multiple Hosts (Scaling Out)
          • Adding and Shutting Down a Redundant Storage Manager (SM)
          • Restoring a Database Using an Archive
          • Deprovisioning Hosts in a Multi-Host Configuration (Scaling In)
            • General Steps for Backing Up Databases
                • Copying the Archive and Journal Directory of the Shutdown Storage Manager (SM)
              • Using Hot Copy Successfully
              • Using Simple Hotcopy
              • Using Backup Sets
              • Using Full Hot Copy
              • Using Incremental Hot Copy
              • Using Journal Hot Copy
              • Hot Copy Command Formats
                • Restore from a Single Backup Set - Examples
                • Restore Data from Multiple Backup Sets - Examples
              • Example of Hot Copy Running
              • Example of Hot Copy Log Message
              • Examples of Hotcopy Errors
              • Example of Hotcopy Success
          • Deleting Databases
          • Database Management - A Working Example
          • Scripted Database Management Using pynuoadmin
        • System Requirements
        • Installing NuoDB
        • Install NuoDB License on Kubernetes
        • Enabling TLS in Containerized Environments
        • Automatic Management of NuoDB State
        • Scaling Down the Admin Layer without Kubernetes-Aware Admin
      • Durable Domain Configuration
      • Domain Credentials
      • Admin Process (AP)
      • Admin Process (AP) Quorum
      • Maintaining Admin Process (AP) Quorum
      • User Authentication and Authorization
        • Configure Transparent Data Encryption
        • Transparent Data Encryption Error Codes
        • Privileges of the Database Administrator
        • Connect to a database Using TLS and LDAP
        • User Authentication Using LDAP
        • Users and Roles
        • Schema Access Levels
        • Assign Database Access Levels
        • Examples of Assigning Database Access Levels
        • Manage Database Schemas
        • Manage Database Tables
        • Manage Other Database Objects
        • About Commit Protocols
        • About Commit Protocols and Recoverability
        • About the Safe Commit Protocol
      • Database Journaling
        • Use Cases
        • Storage Group Rules
        • Table Partition Rules
          • Rules for Adding or Removing Storage Groups
          • Add a Storage Group
          • Remove a Storage Group
          • Delete a Storage Group
        • Manage Table Partitions
        • Example
      • Restoring a Database Using an Archive
        • Terminology
        • Pros and Cons
        • Setup
        • Normal Operation
        • Disaster Recovery
        • Disaster Recovery Using Handoff - Example
      • Reducing Memory Pressure on Transaction Engines (TEs)
      • Setting Client Connection Limits for Transaction Engines (TEs)
      • Installing pynuoadmin
      • Validating Database Archives
        • Communication Graphs
        • CPU Graphs
        • Disk Graphs
        • Memory Graphs
        • NuoDB Ops System Overview Graphs
        • NuoDB Overview Graphs
        • SQL Graphs
        • Case Studies
        • NuoDB Metrics Available from the Collector
        • Kubernetes Metrics
        • Use Kubernetes Dashboards
      • Useful SQL Metrics Related to Database Operation
      • Useful System Tables Related to Database Operation
      • Client Package
      • Load Balancer Policies
        • Drivers in the NuoDB Client Package
        • NuoDB Community Drivers
        • NuoDB Drivers Available at Other Public Sites
        • Database Operations
        • Enabling TLS
        • Notes for Using C++ Driver
        • Examples
        • API Reference
        • Release Notes
        • Database Operations
        • Enabling TLS
          • Specifying Properties on the Connection URL
          • Configuring a NuoDB DataSource
          • Third Party DataSources
          • Using try-with-resources
          • JDBC Transaction Isolation Levels Supported by NuoDB
          • Canceling a Statement
          • Working with Timeouts
          • Logging
          • Exception Handling
          • ResultSet Shelf Life
          • Setting Result Set Holdability
          • LOB Streaming
          • JDBC and XA Transactions Using NuoDB
          • Direct Transaction Engine Connection
          • Using Timestamps and Time Zones
        • Examples
        • API Reference
        • Hibernate Release Notes
        • Hibernate Dialect Considerations
        • Auto Generating Keys
        • Connect to a Database
        • Using JNDI
        • Example
      • NuoDB Flyway Driver
        • Use ADO.NET Driver Objects Directly
        • Database Operations
        • Notes
        • Enabling TLS
        • Database Operations
        • Notes
        • Example
        • API Reference
          • Connect Using a Single Connection
          • Connect Using a Connection Pool
        • Queries
        • Transactions
        • Data Types
        • Best Practices
      • Sample NuoDB Programs on GitHub
      • Rules for Specifying NuoDB Identifier Names
          • Changing Default Transaction Behavior
          • Overriding Default Transaction Behavior
        • About Lock Behavior
          • About Implicit Transactions
          • About Explicit Transactions
        • Transaction Behavior in SQL Stored Procedures
        • Explicit Transactions and Rollback Modes
          • Description of NuoDB Transaction Isolation Levels
          • Examples of Reading Data After a Concurrent Transaction Updates Data
          • Examples of Selecting Data for Update After a Concurrent Transaction Updates Data
          • Examples of Updating a Row That is Being Updated by a Concurrent Transaction
          • Examples of Concurrent Updates That Do Not Overlap
          • JDBC Transaction Isolation Levels Supported by NuoDB
          • Behavior If Connection to a Database is Lost
          • Behavior If NuoDB Throws an Exception
        • INNER Join
        • LEFT OUTER Join
        • RIGHT OUTER Join
        • Cross Join Operations
        • Multiple Join Operations
        • Using Conditions to Control the Flow of Execution
        • Using Loops to Control the Flow of Execution
          • Variables
          • SQL Procedural Conditional Statements
          • Control Flow
          • Dynamic SQL
          • Comments
        • Creating Database Objects in Stored Procedures
          • DECLARE Cursor
          • OPEN Cursor
          • FETCH Cursor
          • FETCH_STATUS
          • Update and Delete Using WHERE CURRENT OF
          • CLOSE Cursor
          • DEALLOCATE Cursor
          • Examples Using Cursors
        • Using a Result Set as Input to a Stored Procedure
          • Returning a Result Set from a Stored Procedure
          • Returning Multiple Result Sets from a Stored Procedure
          • Using Embedded Java Stored Procedures
          • Using Embedded Java Triggers
          • Using Embedded Java User Defined Functions
          • Unsupported Methods in Embedded Java API
        • About the SELECT List of an Updatable View
        • About the FROM Clause of an Updatable View
        • About the WHERE Clause of an Updatable View
        • Examples of Creating and Updating Views
      • Investigating Hung Queries
      • Understanding EXPLAIN Command Output
        • Identifying Historical Slow Queries Using System Tables
        • Identifying Currently Running Slow Queries Using System Tables
        • Obtaining SQL Elapsed Time Report
        • Running ANALYZE to Obtain Index Statistics
          • Identifying Index Usage (EXPLAIN)
          • Obtaining Index Use Statistics
        • Using Optimizer Hints
        • Configuring NuoDB SQL
        • Command Line Editing With NuoDB SQL
          • About the NuoDB SQL Configuration File
          • About the NuoDB SQL History File
          • Using the NuoDB SQL Init File
        • Using Command Line History in NuoDB SQL
        • Paginating NuoDB SQL Output
        • Displaying NuoDB SQL Output Vertically
        • Batch Processing with NuoDB SQL
        • Examples with NuoDB SQL
      • Internationalization and Localization
      • Tuning Tips for System Configuration
      • Tuning Tips for NuoDB Configuration
      • Tuning Tips for Database Design
      • Tuning Tips for Journal Performance
      • Troubleshooting with NuoDB Command
        • Connection Timed Out with Java IOException
        • No Such Database Name Error
        • No Available Nodes Error
      • Identifying and Killing Long-Running Queries
      • Messages Displayed in Relation to Connections Supported
        • Log Entry Format
        • Dynamic or On-Demand Logging
        • Default Logging
        • NuoDB Admin Logging
        • Logging Options
        • Cannot Start Transaction Engine (TE) Without a Storage Manager (SM)
        • Inconsistent Statistic for Index
        • Insufficient Storage Managers (SMs) to Start Transaction
        • Storage Manager (SM) Startup Failure: Attempt to Initialize Exisiting Archive
        • Transaction Engine (TE) or Storage Manager (SM) Quits Unexpectedly
      • Basic Regular Expression Constructs
        • Host Properties (nuoadmin.conf)
        • Data Type Mappings (nuodb-types.config)
        • Database Configuration for a Particular Host (nuodb.config)
        • JVM Options (jvm-options)
      • Connection Properties
          • NuoDB Archive - Checking
          • NuoDB Archive - Restoring
          • NuoDB Command Reference
          • Docker-specific NuoDB Command Reference
        • NuoDB Dump
        • NuoDB Loader
          • Downloading NuoDB Migrator Source Code
          • NuoDB Migrator Properties File
          • About NuoDB Migrator Commands
            • NuoDB Migrator Mappings for DEFAULT Values
            • NuoDB Migrator Mappings for MySQL Unsigned Types
            • NuoDB Migrator Mappings for Unsupported Types
            • NuoDB Migrator Mappings for Supported Types
            • Dumping a Source Database to be Migrated
            • Loading Dumped Files into NuoDB Target Database
            • Generating DDL SQL Statement Scripts for Migrated Data
            • Example of Migrating From IBM DB2
            • Example of Migrating From MSSQL
            • Example of Migrating From MySQL
            • Example of Migrating From Oracle
            • Example of Migrating From PostgreSQL
          • Using NuoDB Migrator to Copy NuoDB Databases
            • Overriding NuoDB Migrator Type Mappings
            • Examples of NuoDB Migrator Schema Command
            • Troubleshooting Migration Metadata Error During Schema Generation
          • NuoDB Migrator Dump Command
          • NuoDB Migrator Load Command
        • NuoDB SQL
      • Database Options
      • Default Ports (NuoDB Admin)
      • Environment Variables
        • NuoDB Admin Log Description
        • NuoDB Admin Event Log
        • NuoDB Admin Audit Log
        • SQL Connections Log
        • Crash Directory Description
        • Engine Stop Log Description
      • Metrics Published by Database Processes
      • REST API Reference (NuoDB Admin)
        • SQL Internationalization and Localization
          • SQL String and Character Types
          • SQL Numeric Types
          • SQL Date and Time Types
          • SQL Boolean Types
          • SQL Binary Types
          • Implicit Conversions for INSERT and UPDATE Statements
        • SQL Error Codes
          • SQL Aggregate Functions
          • SQL Comparison Operators
          • SQL Conditional Expressions
          • SQL Data Type Conversion Functions
          • SQL Date and Time Functions and Operators
          • SQL JSON String Functions
          • SQL Logical Operators
          • SQL Mathematical Functions and Operators
          • SQL Pattern Matching
          • SQL String Functions and Operators
          • SQL System Information Functions
          • SQL Table Functions
          • SQL Window Functions
        • SQL Keywords
        • SQL Standard Compliance
          • ALTER DATABASE
          • ALTER FUNCTION
          • ALTER PROCEDURE
          • ALTER SEQUENCE
          • ALTER TABLE
          • ALTER TRIGGER
          • ALTER USER
          • ALTER VIEW
          • ANALYZE
          • CALL
          • COMMIT
          • CREATE FUNCTION
          • CREATE INDEX
          • CREATE PROCEDURE
          • CREATE ROLE
          • CREATE SCHEMA
          • CREATE SEQUENCE
          • CREATE TABLE
          • CREATE TRIGGER
          • CREATE USER
          • CREATE VIEW
          • DELETE
          • DROP FUNCTION
          • DROP INDEX
          • DROP PROCEDURE
          • DROP ROLE
          • DROP SCHEMA
          • DROP SEQUENCE
          • DROP TABLE
          • DROP TRIGGER
          • DROP USER
          • DROP VIEW
          • EXECUTE
          • EXPLAIN
          • GRANT
          • HELP
          • INSERT
          • KILL CONNECTION
          • KILL STATEMENT
          • LOCK
          • RELEASE SAVEPOINT
          • RENAME TABLE
          • REPLACE
          • REVOKE
          • ROLLBACK
          • SAVEPOINT
          • SELECT
          • SET
          • SHOW
          • START TRANSACTION
          • TRUNCATE
          • UPDATE
          • USE
          • WITH
        • SQL System Properties
          • ALLSYSTEMFIELDS
          • ALLSYSTEMTABLES
          • BUILTINFUNCTIONS
          • COLUMNGROUPS
          • COLUMNGROUPFIELDS
          • CONNECTIONS
          • CONNECTIONSTATEMENTS
          • DATATYPES
          • DUAL
          • FIELDS
          • FOREIGNKEYS
          • FORMATS
          • FUNCTIONCOLUMNS
          • FUNCTIONS
          • GLOBALATOMS
          • INDEXES
          • INDEXFIELDS
          • INDEXHISTOGRAMBUCKETS
          • INDEXHISTOGRAMS
          • INDEXPREFIXSTATISTICS
          • INDEXSTATISTICS
          • JAVACLASSES
          • LASTSTATEMENT
          • LOCALATOMS
          • LOCALATOMSEQUENCES
          • LOCALCONNECTIONS
          • LOCALEXECUTIONSTATS
          • LOCALINDEXATOMS
          • LOCALQUERYTIMEOUTS
          • LOCALSTATEMENTS
          • LOCALTABLEATOMFIELDS
          • LOCALTABLEATOMS
          • LOCALTRANSACTIONALLOCKS
          • LOCALTRANSACTIONS
          • MEMPROPERTIES
          • MULTIINDEXES
          • NODES
          • NODESTORAGEGROUPS
          • OPENRESULTS
          • PARTITIONCRITERIA
          • PARTITIONEDTABLES
          • PARTITIONIDS
          • PASSWORDS
          • PRIVILEGES
          • PRIVTYPES
          • PROCEDURECOLUMNS
          • PROCEDURES
          • PROPERTIES
          • PSEUDOTABLEFIELDS
          • PSEUDOTABLES
          • QUERYBUFFERSTATS
          • QUERYSTATS
          • ROLES
          • SCHEMAS
          • SEQUENCES
          • STATSCACHE
          • STORAGEGROUPS
          • TABLECONSTRAINTS
          • TABLES
          • TEMPFIELDS
          • TEMPINDEXES
          • TEMPINDEXFIELDS
          • TEMPTABLES
          • TRANSACTIONALLOCKS
          • TRANSACTIONS
          • TRIGGERCLASSES
          • TRIGGERS
          • USERROLES
          • USERS
          • VERSIONS
          • VIEW_TABLES
        • Using DbVisualizer
        • Using SQL Workbench/J
        • Using SQuirreL Universal SQL
        • Using MySQL mysqldump Utility
        • Logging
        • Privileges
        • Network Encryption
      • REST API
      • Java DataSource API
      • C Driver API
      • C++ Driver API
    • Glossary
        • About the Remote Commit Protocol
        • About the Region Commit Protocol
        • About the Local Commit Protocol
            • SQL Enumerated Types
  • NuoDB Docs
    • 6.0.2
    • 5.1.x
    • 5.0.x
  • SQL Development
  • Controlling the Flow of Execution

Controlling the Flow of Execution

Control Structures are probably the most useful (and important) part of the SQL Programming Language. With control structures, you can retrieve and/or manipulate data in a very flexible and powerful way.

Multiple Join Operations Using Conditions to Control the Flow of Execution

All content © 2024 Dassault Systemes SE.

Site built with Antora and the Antora default ui.