WIXLIB

Rocket UniVerseSQL ReferenceVersion 11.2.5July 2015UNV-1125-SQLR-1

NoticesEditionPublication date: July 2015Book number: UNV-1125-SQLR-1Product version: Rocket UniVerse V11.2.5Copyright Rocket Software, Inc. or its affiliates 1985-2015. All Rights Reserved.TrademarksRocket is a registered trademark of Rocket Software, Inc. For a list of Rocket registered trademarksgo to: www.rocketsoftware.com/about/legal. All other products or services mentioned in thisdocument may be covered by the trademarks, service marks, or product names of theirrespective owners.ExamplesThis information might contain examples of data and reports. The examples include the names ofindividuals, companies, brands, and products. All of these names are fictitious and any similarityto the names and addresses used by an actual business enterprise is entirely coincidental.License agreementThis software and the associated documentation are proprietary and confidential to RocketSoftware, Inc. or its affiliates, are furnished under license, and may be used and copied only inaccordance with the terms of such license.Note: This product may contain encryption technology. Many countries prohibit or restrict theuse, import, or export of encryption technologies, and current use, import, and exportregulations should be followed when exporting this product.2

Corporate informationRocket Software, Inc. develops enterprise infrastructure products in four key areas: storage,networks, and compliance; database servers and tools; business information and analytics; andapplication development, integration, and modernization.Website: www.rocketsoftware.comRocket Global Headquarters77 4th Avenue, Suite 100Waltham, MA 02451-1468USATo contact Rocket Software by telephone for any reason, including obtaining pre-salesinformation and technical support, use one of the following telephone numbers.CountryUnited yJapanNetherlandsNew ZealandSouth AfricaUnited KingdomToll-free telephone 0-980-8180800-520-0439Contacting technical supportThe Rocket Customer Portal is the primary method of obtaining support. If you have currentsupport and maintenance agreements with Rocket Software, you can access the Rocket CustomerPortal and report a problem, download an update, or find answers in the U2 Knowledgebase. Tolog into the Rocket Customer Portal or to request a Rocket Customer Portal account, go towww.rocketsoftware.com/support.In addition to using the Rocket Customer Portal to obtain support, you can send email tou2support@rocketsoftware.com or use one of the following telephone numbers.CountryNorth AmericaUnited Kingdom/FranceEurope/AfricaAustraliaNew ZealandToll-free telephone number 1 800 729 3553 44(0) 800 773 771 or 44(0) 20 8867 3691 44 (0) 20 88673692 1 800 707 703 or 61 (0) 29412 5450 0800 505 5153

Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta BetaTable ofContentsTable of ContentsChapter 1Chapter 1: IntroductionUniVerse SQL syntax conventions . . . .Examples used in this book . . . . . .The UniVerse demonstration database .The Circus database . . . . . . .Chapter 2. . . . . . . . . . . . 2-16.3-33-53-63-73-73-73-83-83-93-10Chapter 2: The SQL catalogWhat Is the SQL catalog? . . . . . .Structure of the SQL catalog . . . . .UV ASSOC . . . . . . . . . .UV COLUMNS . . . . . . . . .UV SCHEMA. . . . . . . . . .UV TABLES . . . . . . . . . .UV USERS . . . . . . . . . . .UV VIEWS. . . . . . . . . . .Using the SQL catalog . . . . . . .Finding SQL catalog inconsistenciesFixing SQL catalog inconsistencies .Chapter 3.Chapter 3: Data typesUniVerse SQL data categoriesSQL data types . . . . .BIT . . . . . . . .CHAR . . . . . . .DATE . . . . . . .DEC . . . . . . .DOUBLE PRECISION .FLOAT . . . . . .INT . . . . . . . .NCHAR . . . . . c (bookTOC.template)March 25, 2014 12:30 pm

NUMERIC . . . . . . . .NVARCHAR. . . . . . . .REAL . . . . . . . . . .SMALLINT . . . . . . . .TIME . . . . . . . . . .VARBIT . . . . . . . . .VARCHAR . . . . . . . .Data types and data categories . . .Data types and conversion codes.Chapter 4Chapter 5.3-103-113-123-123-133-133-133-153-16Programming with ODBC. . . . . . . . .Multivalued columns and associations . . .Using SQL statements in programs . . . . .SQL syntax in programs . . . . . . . .SELECT statements in programmatic SQL .Using parameter markers in DML 9Chapter 4: UniVerse SQL in client programsChapter 5: TriggersApplying business rules . . . . . .Using triggers . . . . . . . . . .When does a trigger fire? . . . .What events fire a trigger? . . . .Creating triggers . . . . . . . . .Modifying triggers. . . . . . .Listing information about triggers . . .Trigger programs . . . . . . . . .Transactions . . . . . . . . .Opening files . . . . . . . .Handling errors. . . . . . . .Handling record and file locks . .Order of operations . . . . . .Nested triggers and trigger recursionSome examples . . . . . . . . .Extending referential integrity. . .Preventing deletions . . . . . .Validating data . . . . . . . .Changing a record before writing it .Auditing changes . . . . . . .Table of Contents 5

Chapter 6Chapter 6: UniVerse SQL statementsStatement page layout . . . . . .ALTER TABLE . . . . . . . . .ADD clause: Column . . . . .ADD clause: Column Synonym . .ADD clause: Association . . . .ADD clause: Table Constraint . .DROP clause: Association. . . .DROP clause: Integrity Constraint .ALTER clause: SET DEFAULT . .ALTER clause: DROP DEFAULT .TRIGGER clause . . . . . . .CALL . . . . . . . . . . . .CREATE INDEX . . . . . . . .CREATE SCHEMA . . . . . . .CREATE TABLE . . . . . . . .Column definition . . . . . .ASSOC clause . . . . . . . .Table constraints . . . . . . .CREATE TRIGGER . . . . . . .CREATE VIEW . . . . . . . . .DELETE . . . . . . . . . . .DROP INDEX . . . . . . . . .DROP SCHEMA . . . . . . . .DROP TABLE . . . . . . . . .DROP TRIGGER . . . . . . . .DROP VIEW . . . . . . . . . .GRANT . . . . . . . . . . .Database privileges . . . . . .Table privileges . . . . . . .INSERT. . . . . . . . . . . .Specifying columns . . . . . .Specifying values . . . . . .VALUES clause . . . . . . .Query specification . . . . . .REVOKE . . . . . . . . . . .Database privileges . . . . . .Table privileges . . . . . . .SELECT . . . . . . . . . . .SELECT clause . . . . . . .6 UniVerse SQL 6-916-946-95

WHERE clause . . . . . . .WHEN clause . . . . . . .GROUP BY clause . . . . . .HAVING clause . . . . . .ORDER BY clause . . . . . .FOR UPDATE clause . . . . .Report qualifiers . . . . . .Processing qualifiers . . . . .UNION operator . . . . . .UPDATE. . . . . . . . . . .Set expressions . . . . . . .WHERE clause . . . . . . .WHEN clause . . . . . . .Referential integrity actions . .Column . . . . . . . . . . .EVAL expressions . . . . . .Condition . . . . . . . . . .Comparing values . . . . . .Specifying a range: BETWEEN .Phonetic matching: SAID . . .Pattern matching: LIKE . . . .Testing for the null value: IS NULLData type . . . . . . . . . .Expression . . . . . . . . . .Concatenation operator . . . .CAST function . . . . . . .Function expressions . . . . .Identifier. . . . . . . . . . .Delimited identifiers . . . . .Literal . . . . . . . . . . .Character strings . . . . . .Bit strings . . . . . . . . .Hex strings . . . . . . . .Numbers . . . . . . . . .Dates . . . . . . . . . .Times . . . . . . . . . .Relational operator . . . . . . .Set function. . . . . . . . . .Subquery . . . . . . . . . .Table . . . . . . . . . . . 3Table of Contents 7

Appendix AAppendix A: UniVerse SQL grammarBNF conventions . . . . . . .Common syntax elements . . . .Tokens, characters, and symbolsKeywords . . . . . . . .Delimiters . . . . . . . .Literals . . . . . . . . .Identifiers and names . . . .Value expressions . . . . . . .Primaries . . . . . . . .Column specifications . . . .Set functions . . . . . . .Character value expressions . .Numeric value expressions . .Data types. . . . . . . . . .Tables . . . . . . . . . . .Query expressions . . . . . . .Simple query specification . .Interactive query specification .Interactive report statement . .Table expression . . . . . .FROM clause . . . . . . .WHERE clause . . . . . .WHEN clause . . . . . . .GROUP BY clause . . . . .HAVING clause . . . . . .Subqueries . . . . . . . .Predicates . . . . . . . . . . comparison-to-value predicate between predicate . . . . in-value-list predicate . . . soundex predicate . . . . null predicate . . . . . . like predicate . . . . . .Data manipulation . . . . . . .DELETE statement . . . . .INSERT statement . . . . .UPDATE statement . . . . .Schema definition statements . . .Schema definition . . . . .8 UniVerse SQL -40A-40

Table definition . . . . . . . .View definition . . . . . . . .Index definition. . . . . . . .Trigger definition . . . . . . .Privilege definition . . . . . .Schema manipulation statements . . .DROP SCHEMA statement. . . .ALTER TABLE statement . . . .DROP TABLE statement. . . . .DROP VIEW statement . . . . .DROP INDEX statement. . . . .DROP TRIGGER statement. . . .REVOKE statement . . . . . .User definition statements . . . . .Grant database privilege statement .Revoke database privilege statementCalling procedures . . . . . . . .Description and rules . . . . . .Appendix A-58A-59A-59A-60A-61A-61Appendix B: Reserved wordsTable of Contents 9

1Administering UniData on Windows NT or Windows 20000Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta BetaChapterChapter 1: IntroductionUniVerse SQL syntax conventions . . .Examples used in this book . . . . .The UniVerse demonstration databaseThe Circus database . . . . . . . . . . . . . . . . . . . . . . ch 25, 2014 12:30 pm Administering UniData on Windows NT or Windows 20001. . . . . . . . .1-31-41-41-4

Verse SQL is both a database language and a set of capabilities. UsingSQL you can query and update data in UniVerse files as well as in SQL tables.Starting with Release 8.3.3 of UniVerse, you can use UniVerse SQL in clientprograms as well as interactively.UniVerse SQL conforms to the ANSI/ISO 1989 standard established for SQL,enhanced to take advantage of the postrelational database structure ofUniVerse. In contrast to first-normal-form (1NF) databases, which can haveonly one value for each row and column position (or cell), UniVerse is anonfirst-normal-form (NF2) database, which can hold more than one value ina cell. UniVerse also supports nested tables called associations, which aremade up of a group of related multivalued columns in a table.Programmatic SQL in UniVerse conforms to ANSI-1989 SQL with IntegrityEnhancements (Level 1), and it includes many features from ANSI-1992 SQLand most features from the ODBC core level of SQL grammar.1-2 UniVerse SQL Reference

C:\U2Doc\UniVerse\11.2\Source\sqlref\Ch1UniVerse SQL syntax conventionsIn this book the presentation of SQL syntax differs in some respects from thepresentation of syntax in other UniVerse documentation. Because SQLstatements can be complex and detailed, we first present an overview ofsyntax elements with brief descriptions of what each element does. Forexample, the overview of SELECT statement syntax first describes each typeof clause you can use, without presenting complete details of each clause.Subsequent sections describe each syntax element in complete detail.Because many of the same syntax elements, such as table and columnspecifications, conditional expressions, and so forth, are common to severalSQL statements, they are treated separately, each in its own section, makingthem easier to understand and use. For example, since the SELECT, INSERT,UPDATE, and DELETE statements can all specify tables, you will find aseparate section, “Table,” that describes the table expression syntax to use inthese four statements.1-3