SQL Quick Syntax Guide
Contents
1. Be This path is an Informix extension to ANSI SQL 92 entry level standard SQL If you initiate Informix extension checking and include this syntax branch you receive a warning If you have set the DBANSIWARN environment variable at compile time or have used the ansi compile flag you receive warnings at compile time If you have DBANSIWARN set at runtime or if you compiled with the ansi flag warning flags are set in the s lwarn structure TIEN This path is valid only only if your database or application is GLS compliant ALL A shaded option is the default If you do not specify any of the available options this option is in effect by default s Syntax that is enclosed between a pair of arrows is a subdiagram 2 of 3 6 INFORMIX Elements That Can Appear on the Path Description The vertical line is a terminator This symbol only appears at the right indicating that the syntax diagram is complete NOT A branch below the main path indicates an optional path Any term on the main path is required unless a branch can circumvent it A set of multiple branches indicates that a choice among more than two different paths is available A loop indicates a path that you can repeat Punctuation along the top of the loop indicates the separator symbol for list items as in this example If no symbol appears a blank space is the separator A gate ona path2. DISTINCT UNIQUE SUNT UNIQUE mo Expression AVC Subset MAX p 96 MIN SUM RANGE STDEV VARIANCE Procedure Call Expressions Procedure Name Expression p 107 p 96 called __ variable SQL Segments 103 Identifier Identifier B m letter underscore letter digit underscore Identifier Delimited Identifiers Delimited Identifier gt double quote letter double quote _ digit underscore nonalphanumeric character Index Name database Var dbservername 104 501 Quick Syntax Guide INTERVAL Field Qualifier INTERVAL Field Qualifier TO YEAR TO MONTH precision 2 precision Q2 precision TO MINUTE 2 SECOND precision TO SECOND 2 FRACTION Literal DATETIME Numeric DATETIME 3 gt DATETIME Field Qualifier 95 SQL Segments 105 Literal Interval Numeric Date Literal Interval INTERVAL INTERVAL Field Qualifier p 105 106 SQL Quick Syntax Guide Literal Number Literal Number Procedure Name Identifier p 104 Quoted String ee SQL Segments 107 Relational Operator Relational Operator Synonym Name Identifier p 104 m database hom 9 dbservername Table Name Identifier p 104 a database dbservername 108 SQL Quick Syntax Guide View Na
3. REFERENCING NEW DATA correlation name _ 5 AS Delete REFERENCING Clause Delete REFERENCING Clause REFERENCING OLD EE UG correlation name AS Update REFERENCING Clause Update REFERENCING Clause correlation gt REFERENCING name correlation name 42 SQL Quick Syntax Guide CREATE TRIGGER Action Clause Referencing Action Clause Referencing Triggered Action List Triggered Triggered Triggered Action List Triggered Action List INSERT Statement p 59 Condition J WHEN DELETE Statement 45 UPDATE Statement 84 EXECUTE PROCEDURE p 50 Trigger Object Modes Trigger Object Modes DISABLED ENABLED SQL Statements 43 CREATE VIEW CREATE VIEW View SELECT CREATE VIEW Name AS Statement p 109 Subset alim p 69 WITH CHECK OPTION DATABASE Database BENE DATABASE T p 93 X EXCLUSIVE DEALLOCATE DESCRIPTOR EN DEALLOCATE DESCRIPTOR 12 40 77 descriptor variable 44 SQL Quick Syntax Guide DECLARE DECLARE INSERT DECLARE CURSOR Statement i Subset p 59 FE Subset p 69 SCROLL CURSOR FOR ITH L SELECT H EEB Statement p 69 statement id Q cursor variable statement id variable EXECUTE PROCEDURE Statement p 50 DELETE FROM Condition p 89 CURRENT
4. SQL Quick Syntax Guide Version 7 2 April 1996 Part No 000 7880A Published by INFORMIX Press Informix Software Inc 4100 Bohannon Drive Menlo Park CA 94025 The following are worldwide trademarks of Informix Software Inc or its subsidiaries registered in the United States of America as indicated by an and in numerous other countries worldwide INFORMIX INFORMIX OnLine Dynamic Server C ISAM The following are worldwide trademarks of the indicated owners or their subsidiaries registered in the United States of America as indicated by an and in numerous other countries worldwide X Open Company Ltd UNIX X Open Adobe Systems Incorporated PostScript Some of the products or services mentioned in this document are provided by companies other than Informix These products or services are identified by the trademark or servicemark of the appropriate companies If you have a question about one of these products or services please contact the company in question directly Documentation Team Diana Chase Geeta Karmarkar Tom Noronha Copyright 1981 1996 by Informix Software Inc All Rights Reserved No part of this work covered by the copyright hereon may be reproduced or used in any form or by any means graphic electronic or mechanical including photocopying recording taping or information storage and retrieval systems without permission of the publisher RESTRICTED RIGHTS LE
5. DBA SQL Statements 67 REVOKE FRAGMENT REVOKE FRAGM ENT REVOKE Fragment Level ON tablename 3 v user Sree Fragment Level Privileges Fragment Level Privileges ROLLBACK WORK ROLLBACK k J WORK 68 SQL Quick Syntax Guide ROLLFORWARD DATABASE ROLLFORWARD DATABASE ROLLFORWARD DATABASE 93 SELECT A SELECT HAVING GROUP BY Clause Clause p 73 p 74 T ORDER BY F Clause p 74 INTO TEMP FOR READ ONLY Clause p 74 FOR UPDATE SQL Statements 69 SELECT SELECT Clause Select Clause ALL DISTINCT V UNIQUE Select List Expression p 96 Synonym Name p 108 70 SQL Quick Syntax Guide INTO Clause INTO Clause SELECT B INTO data variable indicator variable indicator variable INDICATOR indicator variable qata structure A FROM Clause FROM Clause FROM ri La Additional Tables p 72 SQL Statements 71 SELECT Additional Tables Additional Tables WHERE Clause WHERE Clause p gt WHERE 72 SQL Quick Syntax Guide SELECT Join Option column Relational Oper r column be name 4 GROUP BY Clause GROUP BY Clause gt GROUP BY column SQL Statements 73 SELECT HAVING Clause HAVING Clause gt HAVIN
6. table name aun DELETE CASCADE name CHECK Clause CHECK Clause __ Condition 20 SQL Quick Syntax Guide ALTER TABLE DROP Clause DROP Clause p DROP column name C mk name 2j MODIFY Clause MODIFY Clause MODIFY Modify Column Clause Modify Column Clause Modify Column Clause Modify Column Clause gt column Data Type sen p 94 DEFAULT Clause p 19 New Column Constraint Definition p 19 SQL Statements 21 ALTER TABLE ADD CONSTRAINT Clause ADD CONSTRAINT Clause Table Level Constraint gt ADD CONSTRAINT Definition Table Level Constraint Definition Table Level Constraint Definition Table Level Constraint Definition ren gt column DISTINCT Constraint Mode Definitions K PRIMARY p 20 EY FOREIGN KEY ewe D REFERENCES n Clause ame b 20 CHECK Clause p 20 22 SQL Quick Syntax Guide ALTER TABLE DROP CONSTRAINT Clause DROP CONSTRAINT Clause Constraint gt DROP CONSTRAINT Name p 93 3 Constraint Name p 93 MODIFY NEXT SIZE Clause MODIFY NEXT SIZE Clause gt MODIFY NEXT SIZE LOCK MODE Clause LOCK MODE Clause d gt LOCK MODE PAGE s ROW SQL Statements 23 BEGIN WORK BEGIN WORK BEGIN WORK CHECK TABLE Table CHECK TABLE Name p 108 CLOSE CLOSE cursor id C
7. E ALTER FRAGMENT ON TABLE surviving table INDEX surviving index MODIFY Clause p 16 SQL Statements 13 ALTER FRAGMENT ATTACH Clause ATTACH Clause 3 ATTACH consumed table X AS frag expression BEFORE dbspace surviving table AFTER AS frag expression AS REMAINDER DETACH Clause DETACH Clause DETACH ___ dbspace name new table gt INIT Clause INIT Clause FRAGMENT gt INIT BY Clause WITH ROWIDS for Tables FRAGMENT BY Clause for Indexes IN dbspace 14 SQL Quick Syntax Guide ALTER FRAGMENT FRAGM ENT BY Clause for Tables FRAGMENT BY Clause for Tables FRAGMENT BY ROUND ROBIN IN dbspace gt dbspace 5 EXPRESSION frag expression C frag expression IN dbspace IN dbspace REMAINDER IN remainder dbspace FRAGM ENT BY Clause for Indexes FRAGMENT BY Clause for Indexes 3 FRAGMENT BY EXPRESSION frag expression frag expression IN dbspace IN dbspace REMAINDER IN remainder dbspace SQL Statements 15 ALTER FRAGMENT ADD Clause ADD Clause ADD new dbspace frag expression IN new dbspace BEFORE T existing dbspace AFTER REMAINDER IN new dbspace DROP Clause DROP Clause p DROP dbspace name MODIFY Clause MODIFY Clause MODIFY mod dbspace TO frag expression IN new dbspace RE
8. IN Subquery IN Subquery Expression Condition p 96 with Do f Subquery NOT p 91 EXISTS Subquery EXISTS Subquery Condition EXISTS with Subquery NOT p 91 ALL ANY SOME Subquery ALL ANY SOME Subquery Expression Relational Condition p 96 Operator with p 108 Subquery p 91 92 SQL Quick Syntax Guide Constraint Name Constraint Name Identifier p 104 database i dbservername Database dbname dbservername dbservername dbname variable name dbservername pathname dbname pathname dbname dbservername SQL Segments 93 Data Type Data Type CUN CHAR MOINES Rt size NCHAR 8 m DATE _ DATETIME DATETIME Field Qualifier p 95 precision X ni N FLOAT float precision DOUBLE PRECISION TI INTERVAL INTERVAL Field Qualifier p 105 MONEY pm precision Ye BE SERIAL ka m J Lum SMALLFLOAT REAL N SMALLINT oL INFORMIX OnLine Dynamic Server Specific Data Types p 95 94 SQL Quick Syntax Guide DATETIME Field Qualifier INFORMIX OnLine Dynamic Server Specific Data Types INFORMIX OnLine Dynamic Server Specific Data Types IN TABLE blobspace family name VARCHAR max 3 reserve J j 0 NVARCHAR A _ CHARACTER VARYING max u reserve E DATETIME Fiel
9. indicates that you can only use that path the indicated number of times even if it is part of a larger loop Here you can specify size no more than three times within this statement segment 3 of 3 Introduction 7 How to Read a Syntax Diagram How to Read a Syntax Diagram Figure 1 shows a syntax diagram that uses most of the path elements that are listed in the previous table Figure 1 Elements of a Syntax Diagram Main diagram CREATE DATABASE database name IN dbspace OL Log Clause SE Log Clause LOG BUFFERED LOG MODE ANSI Subdiagram OL Log Clause WITH SE Log Clause WITH LOG IN pathname DATO S MODE ANSI To use this diagram to construct a statement start at the top left with the keywords CREATE DATABASE Then follow the diagram to the right proceeding through the options that you want 8 INFORMIX Sample Code Conventions To read the example syntax diagram 1 You must type the words CREATE DATABASE 2 You must supply a database name 3 You can stop taking the direct route to the terminator or you can take one or more of the optional paths 4 If desired you can designate a dbspace by typing the word IN and a dbspace name 5 If desired you can specify logging Here you are constrained by the database server with which you are working If you are using INFORMIX OnLine Dynamic Server go to the subdiagram named OL Log Clause Follow the subdiagram by
10. ndicator variable output descriptor SQL DESCRIPTOR output descriptor variable L DESCRIPTOR output sqida pointer SQL Statements 49 EXECUTE IMMEDIATE EXECUTE IMMEDIATE EXECUTE IMMEDIATE EN statement variable name EXECUTE PROCEDURE EXECUTE Procedure NN ut p 107 er INTO variable Argument Expression gt pa 7 p 96 parameter name SELECT Statement Singleton p 69 50 SQL Quick Syntax Guide FETCH FETCH FETCH cursor id mm Cursor NEXT variable indicator variable PREVIOUS indicator INDICATOR variable data structure E CURRENT NUSING SQL descriptor A DESCRIPTOR descriptor J row variable M RELATIVE position i L DESCRIPTOR ____ pointer N ABSOLUTE row position cursor id cursor variable SQL Statements 51 FREE FREE cursor id cursor variable statement id statement id variable GET DESCRIPTOR En SET TL descriptor host variable COUNT descriptor variable Described VALUE item Item Info 53 item e variable 52 SQL Quick Syntax Guide GET DIAGNOSTICS Described Item Info Described Item Info field host _ variable TYPE LENGTH PRECISION SCALE NULLABLE INDICATOR NAME DATA ITYPE IDATA ILENGTH GET DIAGNOSTICS Statement GET DIAGNOSTICS U Clause J EXCEPTION Clause p 54 Statem
11. the diagram explains the value ADD A term shown in a rectangle represents a subdiagram Clause on the same page if no page number is supplied or a p 1 14 specified page as if the subdiagram were spliced into ADD Clause the diagram at this point The same subdiagram can be represented by rectangles of different shapes as in these symbols for the ADD Clause subdiagram CREATE OPTICAL CLUSTER Statement see INFORMIX OnLine Optical User Manual This reference represents a statement described in the INFORMIX OnLine Optical User Manual Imagine that the statement is spliced into the diagram at this point An icon is a warning that this path is valid only for some products or only under certain conditions Characters on the icons indicate what products or conditions support the path These icons might appear in a syntax diagram This path is valid only for INFORMIX OnLine Dynamic Server BED This pathis valid only for INFORMIX SE 1 of 3 Introduction 5 Elements That Can Appear on the Path Element Description MEM This path is valid only for DB Access Se This path is valid only for SOL state ments in INFORMIX ESQL C and INFORMIX ESQL COBOL E C This path is valid only for INFORMIX ESQL C E CO This path is valid only for INFORMIX ESOL COBOL SPL This path is valid only for only if you are using Informix Stored Procedure Language SPL This path is valid only for INFORMIX OnLine Optical
12. typing the keyword WITH then choosing and typing either LOG BUFFERED LOG or LOG MODE ANSI Then follow the arrow back to the main diagram If you are using INFORMIX SE go to subdiagram named SE Log Clause Follow the subdiagram by typing the keywords WITH LOG IN typing a quote supplying a pathname and clos ing the quotes You can then choose the MODE ANSI option below the line or continue to follow the line across 6 Once you are back at the main diagram you come to the terminator Your CREATE DATABASE statement is complete Sample Code Conventions Examples of SOL code occur throughout this manual Except where noted the code is not specific to any single Informix application development tool If only SQL statements are listed in the example they are not delimited by semicolons To use this SQL code for a specific product you must apply the syntax rules for that product For example if you are using the Query language option of DB Access you must delimit multiple statements with semicolons If you are using an SQL API you must use EXEC SQL and a semicolon or other appropriate delimiters at the start and end of each statement respectively Introduction 9 SQL Statements ALLOCATE DESCRIPTOR ALLOCATE DESCRIPTOR ALLOCATE descriptor ESQL DESCRIPTOR descriptor WITH MAX occurrences variable occurrences variable ALTER FRAGM ENT ATTACH Clause p 14 DETACH Clause p 14 OL
13. 0 SQL Segments 99 Expression DBINFO Function DBINFO Function 3 gt DBINFO DBSPACE n tblspace num Ja gt expression sqica sqlerrd2 sglca sqlerrd1 Ne sessionid ge wy Exponential and Logarithmic Functions Exponential and Logarithmic Functions EXP float expression LOGN float expression LOG10 float expression ex HEX Function HEX Function gt integer expression 100 SQL Quick Syntax Guide Expression LENGTH Function LENGTH Functions Quoted gt LENGTH String p 107 CHAR LENGTH variable CHARACTER_LENGTH OCTET LENGTH column name Time Functions gt non date expression date DAY datetime expression MONTH WEEKDAY YEAR date EXTEND datetime X J expression p first TO last month day year MDY integer integer integer expression expression expression SQL Segments 101 Expression Trigonometric Functions Trigonometric Functions radian expression numeric expression TRIM Function TRIM Function e TRIM LEADING trim character value expression TRAILING BOTH trim character value expression 102 SQL Quick Syntax Guide FROM Ji source character value expression Expression Aggregate Expressions column
14. ATE eut ALL DEFERRED SQL Statements 77 SET CONNECTION SET CONNECTION ES SET CONNECTION connection name conn nm variable Database Environment p 25 DEFAULT E C CURRENT SET DATASKIP OL _ SET DATASKIP dbspace OFF DEFAULT SET DEBUG FILE TO SET DEBUG FILE TO filename variable name WITH APPEND character expression 78 SQL Quick Syntax Guide SET DESCRIPTOR SET DESCRIPTOR descriptor variable variable Nm SET DESCRIPTOR descriptor COUNT z value ES count Ed 3 item VALUE number Information number variable Item Descriptor Information Item Descriptor Information gt TYPE literal integer LENGTH variable PRECISION SCALE NULLABLE INDICATOR ITYPE ILENGTH Literal Number p 107 DATA p 105 Literal INTERVAL p 106 Quoted String p 107 data variable SQL Statements 79 SET EXPLAIN SET EXPLAIN El EXPLAIN ON 5 OFF SET ISOLATION L EB SET ISOLATION TO DIRTY READ COMMITTED READ CURSOR STABILITY REPEATABLE READ SET LOCK MODE TO SET LOCK MODE TO OL E SET aan eT BUFFERED 80 SQL Quick Syntax Guide SET OPTIMIZATION SET OPTIMIZATION SET OPTIMIZATION HIGH 8 LOW SET PDGPRIORITY OL LO SET PDQPRIORITY DEFAULT LOW OFF HIGH percent of resources SET ROLE SET ROLE role
15. D 108 RESOLUTION percent 3 Synonym 4 column 2 p 108 OL m DISTRIBUTIONS ONLY RESOLUTION percent Table Name p 108 Synonym enn DISTRIBUTIONS ONLY E M name SQL Statements 85 WHENEVER WHENEVER WHENEVER SQLERROR CONTINUE NOT FOUND J GO label GOTO label SQLWARNING STOP function name ERROR PERFORM Paragraph name 86 SQL Quick Syntax Guide SQL Segments Condition Condition Comparison Condition p 90 Condition with Subquery p 91 SQL Segments 89 Condition Comparison Conditions Boolean Expressions Condition Expression Relational 4 p 96 Operator 96 Expression BETWEEN Expression Eck ud NOT 5 Expression Literal p 96 Number p 107 NOT Literal DATETIME p 105 Literal INTERVAL p 106 Quoted String p 107 N TODAY 4 Table Name N USER N p 108 View CURRENT Name L DATETIME Field p 109 Qualifier 95 Synonym Name F SITENAME p 108 z DBSERVERNAME IS X F NULL NOT Quoted LIKE String NOT N MATCHES ESCAPE Synonym Name column column name p 108 name alias 90 501 Quick Syntax Guide Condition Condition with a Subquery Condition with Subquery IN Subquery p 92 EXISTS Subquery p 92 ALL ANY SOME Subquery p 92 SQL Segments 91 Condition
16. Em PREPARE String statement id variable variable name SQL Statements 63 PUT PUT cursor id cursor variable USING RECOVER TABLE RECOVER TABLE SE RENAM E COLUMN Table Name p 108 RENAME COLUMN E 64 SQL Quick Syntax Guide FROM variable name indicator variable indicator variable INDICATOR indicator variable SQL DESCRIPTOR descriptor descriptor variable sqida pointer Vn DESCRIPTOR old column name TO new column name RENAM E DATABASE OL _ RENAME DATABASE old database name RENAM E TABLE Table EE RENAME TABLE Name p 108 REPAIR TABLE REPAIR TABLE RENAME DATABASE new database name new table TO name Table Name p 108 SQL Statements 65 REVOKE REVOKE Table Level Privileges table name FROM PUBLIC 67 view name user name i EXECUTE ON Name p 107 role name Table Level Privileges ON table name PUBLIC p 67 view name 2 user synonym name user CASCADE RESTRICT Database Level PUBLIC Privileges 67 3 user user 66 SQL Quick Syntax Guide REVOKE Table Level Privileges Table Level Privileges p ALL X PRIVILEGES INSERT DELETE SELECT UPDATE INDEX ALTER REFERENCES Database Level Privileges Database Level Privileges CONNECT RESOURCE
17. G ORDER BY Clause gt ORDER BY first last alias N select number N display label lt INTO TEMP Clause INTO TEMP Clause B INTO TEMP temp table name X 7 WITH NO LOG 74 SQL Quick Syntax Guide SET Table Mode Format p 75 List Mode Format 77 Transaction Mode Format 77 Table Mode Format Table Mode Format Object Modes for CONSTRAINTS FOR table name Constraints and Unique Indexes INDEXES p 76 TRIGGERS Object Modes for Triggers and Duplicate Indexes p 76 SQL Statements 75 SET Object Modes for Constraints and Unique Indexes Object Modes for Constraints and Unique Indexes DISABLED ENABLED FILTERING WITHOUT ERROR WITH ERROR Object Modes for Triggers and Duplicate Indexes Object Modes for Triggers and Duplicate Indexes gt DU DISABLED gt ENABLED 76 SQL Quick Syntax Guide SET List Mode Format List Mode Format 3 Object Modes for po CONSTRAINTS constraint Constraints and name Unique Indexes p 76 3 Object Modes for INDEXES EN name Constraints and Unique Indexes p 76 Object Modes for Triggers and Duplicate Indexes p 76 5 Object Modes for SL oe name Triggers and Duplicate Indexes p 76 Transaction Mode Format Transaction Mode Format 3 gt CONSTRAINTS d IMMEDI
18. GEND Software and accompanying materials acquired with United States Federal Government funds or intended for use within or for any United States federal agency are provided with Restricted Rights as defined in DFARS 252 227 7013 c 1 ii or FAR 52 227 19 SQL Quick Syntax Guide Introduction The following statements and segments for Version 7 2 are presented in this SOL statements SQL segments Stored Procedure Language SPL statements INFORMIX OnLine Optical statements This guide presents the syntax diagrams for statements and segments but does not provide any explanatory information For complete descriptions of SQL and SPL statements and segments see the Informix Guide to SQL Syntax For complete descriptions of INFORMIX OnLine Optical statements see the INFORMIX OnLine Optical User Manual These manuals provide the follow ing information for each statement A brief introduction that explains the purpose of the statement A syntax diagram that is identical to the syntax diagram in the SQL Quick Syntax Guide A syntax table that provides the purpose restrictions and syntax for all italicized elements in a syntax diagram Rules and examples of usage References to related statements Introduction 3 Syntax Conventions Syntax Conventions This section describes conventions for syntax diagrams Each diagram displays the sequences of required and optional keywords terms and symbols that are valid in a given
19. L Statement see SQLS Statement enp SQL Statements 31 CREATE PROCEDURE FROM CREATE PROCEDURE FROM ESQL CREATE PROCEDURE FROM filename variable name CREATE ROLE CREATE ROLE role name CREATE SCHEMA CREATE TABLE pa CREATE SCHEMA user Statement AUTHORIZATION name D 33 CREATE INDEX Statement p 27 CREATE VIEW Statement p 44 CREATE SYNONYM Statement p 33 GRANT Statement p 55 CREATE TRIGGER Statement p 40 CREATE OPTICAL CLUSTER Statement p 123 32 SQL Quick Syntax Guide CREATE SYNONYM CREATE SYNONYM ulli CREATE Synonym Table Name SYNONYM FOR a PUBLIC p 108 View Name p 109 PRIVATE CREATE TABLE Table Column CREATE TABLE lum Name Definition p 108 OL Table Level Constraint a WITH Definition ROWIDS p 36 Storage Option p 39 Column Definition Option Column Definition column gt ps O DEFAULT Column Level Clause Constraint p 34 Definition 95 SQL Statements 33 CREATE TABLE DEFAULT Clause DEFAULT Clause b gt DEFAULT literal NULL DATETIME Field Qualifier p 95 SITENAME p 97 DBSERVERNAME p 97 34 SQL Quick Syntax Guide CREATE TABLE Column Level Constraint Definition Option Column Level Constraint Definition DISTINCT Constraint Mode Constraint PRIMARY Definitions Mode De
20. LOSE DATABASE CLOSE DATABASE r COMMIT WORK COMMIT WORK 24 SOL Quick Syntax Guide CONNECT CONNECT CONNECT TO Environment AS connection name conn nm AS variable DEFAULT E WITH CONCURRENT TRANSACTION Database Environment Database Environment dbname Qabservername dbname dbservername db env variable pathname dbname pathname dbname abservername SQL Statements 25 CREATE AUDIT USER Clause USER Clause gt USER user identifier USING auth variable p 1 user id J variable CREATE AUDIT CREATE AUDIT FOR pathname CREATE DATABASE CREATE Database DATABASE Name IN dbspace SE Log Clause H OL Log Clause SE LOG Clause SE Log Clause b gt WITH LOG IN pathname T GUN EEG MODE ANSI 26 SQL Quick Syntax Guide CREATE INDEX OL LOG Clause OL Log Clause WITH X 7 LOG BUFFERED LOG MODE ANSI CREATE INDEX CREATE Index Definition p 28 Object Modes for Unique 3 FILLFACTOR percent Indexes IN dbspace p 28 FRAGMENT Object Modes EXPRESSION Clause p 29 p 28 SQL Statements 27 CREATE INDEX Index Definition Index Definition 3 Index Name ON table name column name 104 P ASC Synonym Name DESC p 108 FRAGMENT BY EXPRESSION Clause FRAGMENT BY EX
21. MAINDER IN new dbspace 16 SQL Quick Syntax Guide ALTER INDEX ALTER INDEX Index Name ALTER INDEX p 104 ALTER TABLE 3 MENN ALTER TABLE Table Name ADD Clause p 108 p 18 Synonym DROP Clause Name p 21 p 108 MODIFY Clause p 21 ADD CONSTRAINT Clause p 22 DROP CONSTRAINT Clause p 23 A MODIFY NEXT SIZE Clause p 23 LOCK MODE Clause p 23 ADD ROWIDS DROP ROWIDS SQL Statements 17 ALTER TABLE ADD Clause ADD Clause ADD Add Column Clause 3 Add Column Clause Add Column Clause Add Column Clause new pw column Data Type p 94 DEFAULT red New Column Constraint Definition p 19 18 SQL Quick Syntax Guide J BEFORE hama ALTER TABLE DEFAULT Clause DEFAULT Clause DEFAULT M literal F NM NULL A CURRENT p 97 DATETIME Field Qualifier p 95 USER p 97 TODAY p 97 SITENAME p 97 DBSERVERNAME p 97 New Column Constraint Definition New Column Constraint Definition L DISTINCT Constraint Mode Constraint No PRIMARY Definitions Mode p 20 eat REFERENCES p Clause p 20 SQL Statements 19 ALTER TABLE Constraint Mode Definitions Constraint Mode Definitions CONSTRAINT Constraint DISABLED p 93 ENABLED FILTERING WITHOUT ERROR WITH ERROR REFERENCES Clause REFERENCES Clause gt REFERENCES
22. OF 2150 cursor SQL Statements 45 DESCRIBE DESCRIBE ESQL USING mi _ SQL DESCRIPTOR Di descriptor statement descriptor id variable L variable INTO sqlda pointer DISCONNECT EMEN DISCONNECT 775 CURRENT DEFAULT ALL connection name conn_nm variable DROP AUDIT 5 Table DROP AUDIT FOR p 108 Synonym Name p 108 DROP DATABASE DROP DATABASE Database Name p 93 46 SQL Quick Syntax Guide DROP INDEX DROP INDEX DROP INDEX DROP PROCEDURE Procedure DROP PROCEDURE Name p 107 DROP ROLE DROP ROLE role name DROP SYNONYM Synonym DROP SYNONYM Name p 108 DROP TABLE DROP TABLE CASCADE RESTRICT SQL Statements 47 DROP TRIGGER DROP TRIGGER Trigger DROP TRIGGER Name p 41 DROP VIEW DROP VIEW CASCADE Synonym p 106 RESTRICT EXECUTE statement id statement id variable EXECUTE 48 SQL Quick Syntax Guide USING Clause USING Clause gt EXECUTE 3 storage variable SQL DESCRIPTOR DESCRIPTOR INTO Clause INTO Clause INTO output variable name Storage 1 indicator variable storage INDICATOR indicator variable 4 storage descriptor storage descriptor variable storage sqlda pointer output indicator variable output INDICATOR
23. PRESSION Clause 3 FRAGMENT BY frag expression ___ frag expression 2 EXPRESSION IN dbspace i IN dbspace REMAINDER IN remainder dbspace Object Modes for Unique Indexes Object Modes for Unique Indexes DISABLED ENABLED FILTERING WITHOUT ERROR WITH ERROR 28 SQL Quick Syntax Guide CREATE PROCEDURE Object Modes for Duplicate Indexes Duplicate Indexes ENABLED CREATE PROCEDURE Procedure EE CREATE PROCEDURE Name p 107 DBA RETURNING Clause p 30 END PROCEDURE WITH DOCUMENT an LISTING IN Parameter Option Parameter yo variable SQL Data Type name Subset p 94 DEFAULT default value LIKE table column name name L INC BYTE TEXT SQL Statements 29 CREATE PROCEDURE RETURNING Clause RETURNING Clause SQL Data Type RETURNING Subset p REFERENCES ka TEXT 30 SQL Quick Syntax Guide CREATE PROCEDURE Statement Block Statement Block CALL Statement 4 DEFINE 4 ON B p 113 Statement EXCEPTION 114 Statement CONTINUE 119 Statement p 113 EXECUTE PROCEDURE Statement p 50 EXIT Statement p 115 FOR Statement 116 FOREACH Statement p 117 IF Statement p 117 LET Statement p 119 RAISE EXCEPTION Statement p 119 RETURN Statement p 120 SYSTEM Statement p 120 TRACE Statement p 120 WHILE Statement p 120 Subset of SQ
24. PTICAL e FOR Ta CLUSTERSIZE owner Clause FOR Clause FOR Clause _____ es blob PN INFORMIX OnLine Optical Statements 123 ON Clause ON Clause cluster key column name L DROP OPTICAL CLUSTER Au cluster CLUSTER MX Fa owner RELEASE r family name volume number family name variable procedure variable name 124 SQL Quick Syntax Guide RESERVE RESERVE RESERVE ae name volume number family name variable N procedure variable name SET MOUNTING TIMEOUT SET MOUNTING TIMEOUT TO WAIT NOT WAIT seconds Function Expressions Function Expressions p 98 DESCR blob column name FAMILY blob column name VOLUME blob column name INFORMIX OnLine Optical Statements 125
25. age Option Storage Option CREATE TABLE IN dbspace 25 Extent Option FRAGMENT BY Clause IN pathname FRAGMENT BY Clause FRAGMENT BY Clause BM FRAGMENT BY ROUND ROBIN IN dbspace EXPRESSION frag expression 2 frag expression IN dbspace IN dbspace Extent Option LOCK MODE Clause p 40 dbspace REMAINDER IN remainder dbspace X Eg EXTENT SIZE kbytes NEXT SIZE kbytes SQL Statements 39 CREATE TRIGGER LOCK MODE Clause 3 gt LOCK MODE T PAGE ROW CREATE TRIGGER Trigger CREATE TRIGGER Name p 41 Trigger Object Modes p 43 INSERT ON Insert Action REFERENCING Clause Clause Referencing p 42 p 43 KE DELETE Delete Action REFERENCING Clause Clause Referencing 42 43 UPDATE Clause p 41 ON Update Action REFERENCING Clause Clause Referencing p 42 p 43 40 SQL Quick Syntax Guide CREATE TRIGGER Trigger Name Trigger Name Identifier p 104 UPDATE Clause UPDATE gt UPDATE MU Action Clause Action Clause ROW Triggered BEFORE Action List p 43 Triggered Triggered FOR EACH Action List AFTER Action List Triggered de 5 Action List p 43 Tri ggered AFTER Action List p 43 Triggered Action List p 43 SQL Statements 41 CREATE TRIGGER Insert REFERENCING Clause Insert REFERENCING Clause
26. atements 117 IF IF Statement List IF Statement List a BEGIN Statement p 31 CALL Statement p 113 CONTINUE Statement p 113 EXIT Statement p 115 FOR Statement p 116 FOREACH Statement p 117 IF Statement p 117 LET Statement p 119 RAISE EXCEPTION Statement p 119 RETURN Statement p 120 SYSTEM Statement p 120 TRACE Statement p 120 WHILE Statement p 120 SQL Statement 118 SQL Quick Syntax Guide LET variable Ed Expression LET name Name p 96 107 called variable Expression p 96 ON EXCEPTION Statement ON EXCEPTION Block END EXCEPTION p 31 error number SQL WITH RESUME SET error variable ISAM error variable error data variable RAISE EXCEPTION RAISE EXCEPTION 201 ISAM error _ _ errortext variable SPL Statements 119 RETURN RETURN SYSTEM SYSTEM expression character variable TRACE PROCEDURE Expression p 96 WHILE Condition uc d END WHILE p p 31 120 SQL Quick Syntax Guide INFORMIX OnLine Optical Statements ALTER OPTICAL CLUSTER ALTER OPTICAL CLUSTER AUER cluster CLUSTERSIZE DB Gu f name Clause owner CLUSTERSIZE Clause CLUSTERSIZE Clause gt CLUSTERSIZE CREATE OPTICAL CLUSTER CREATE Juster
27. d Qualifier TO YEAR DAY TO MONTH HOUR TO DAY MINUTE TO HOUR SECOND TO MINUTE FRACTION TO SECOND TO FRACTION 3 digit SQL Segments 95 Expression Expression Column Expressions p 9 Constant Expressions p 9 8 7 7 Function Expressions p 98 Aggregate Expressions p 103 Procedure Call Expressions p 103 variable name J Edd procedure variable name A Expression 96 SQL Quick Syntax Guide Column Expressions M first last __ ROWID Constant Expressions Quoted Expression String p 107 USER SITENAME DBSERVERNAME Literal Number p 107 TODAY CURRENT DATETIME Field Qualifier 95 Literal DATETIME p 105 Literal INTERVAL p 106 n UNITS datetime unit SQL Segments 97 Expression Function Expressions Algebraic Functions p 99 DBINFO Function p 100 Exponential and Logarithmic Functions p 100 HEX Function p 100 LENGTH Function p 101 Time Functions p 101 Trigonometric Functions p 102 TRIM Function p 102 98 SQL Quick Syntax Guide Expression Algebraic Functions Algebraic Functions ABS num_expression MOD dividena divisor POW base exponent ROOT radicand Expression p 96 rounding factor 0 SQRT sqrt radicand Expression p 96 truncating factor
28. en W JE user OPTION AS grantor Fragment Level Privileges Fragment Level Privileges 58 SQL Quick Syntax Guide INFO TABLES INFO COLUMNS FOR oe ACCESS 7 PRIVILEGES REFERENCES STATUS gn INSERT INTO Ce name Procedi qunm PROCEDURE Name L VALUES Clause p 61 SELECT Statement Subset p 69 Argument p 60 SQL Statements 59 INSERT Argument Expression gt 96 parameter _ name SELECT Statement Singleton p 69 60 SQL Quick Syntax Guide INSERT VALUES Clause VALUES Clause variable M VALUES xe name indicator variable a indicator variable NULL Literal Number p 107 Quoted String p 107 Literal DATETIME p 105 Literal INTERVAL p 106 TODAY p 97 2 CURRENT p 97 MM 97 DBSERVERNAME p 97 SQL Statements 61 LOAD LOAD LOAD FROM filename E era A DELIMITER delimiter J G INTO Table Name p 108 LOCK TABLE LOCK TABLE IN X SHARE f MODE EXCLUSIVE 62 SQL Quick Syntax Guide OPEN OPEN OPEN cursor cursor variable REOPTIMIZATION variable name SQL DESCRIPTOR descriptor descriptor variable gt DESCRIPTOR Sqlda pointer SELECT En OUTPUT TO an T Sean p ET PIPE d HEADINGS PREPARE ESQL statement Quoted
29. ent Clause Statement Clause un st varz MORE NUMBER ROW_COUNT SQL Statements 53 GET DIAGNOSTICS EXCEPTION Clause EXCEPTION Clause EXCEPTION except num en var 54 SQL Quick Syntax Guide CLASS ORIGIN CONNECTION ALIAS MESSAGE LENGTH MESSAGE TEXT RETURNED SQLSTATE SERVER NAME SUBCLASS ORIGIN GRANT GRANT Database Level OL role name P WITH GRANT OPTION EXECUTE oN 6008 p 107 Table Privileges for PUBLIC and Users p 56 Table Privileges for Roles p 56 Database Level Privileges Database Level Privileges RESOURCE DBA SQL Statements 55 GRANT Table Privileges for PUBLIC and Users Table Privileges for PUBLIC and Users PUBLIC Synonym Name p 108 N WITH GRANT OPTION Table Privileges for Roles Table Privileges for Roles role name role name Synonym Name p 108 AS grantor 56 SQL Quick Syntax Guide GRANT Table Level Privileges Table Level Privileges gt lt ALL zj PRIVILEGES 5 No INSERT DELETE N SELECT en i name ser name UPDATE No REFERENCES lt name INDEX Sa ALTER SQL Statements 57 GRANT FRAGMENT GRANT FRAGM ENT 5 GRANT Fragment Level Privileges ON tablename dbspace FRAGMENT TO user pa WITH GRANT v
30. finitions REFERENCES Clause p 36 Constraint Mode Definitions Constraint Mode Definitions gt DISABLED CONSTRAINT Constraint p 93 ENABLED FILTERING WITHOUT ERROR WITH ERROR SQL Statements 35 CREATE TABLE REFERENCES Clause REFERENCES Clause gt REFERENCES table name lan column ON DELETE CASCADE name CHECK Clause CHECK Clause Condition Table Level Constraint Definition Option Table Level Constraint Definition G 3 column ame Constraint Mode DISTINCT aint Definitions p 35 N PRIMARY KEY REFERENCES FOREIGN KEY We name CHECK Clause 36 SQL Quick Syntax Guide TEMP TABLE Clause TEMP TABLE Clause TEMP temp M TABLE table name Definition Temp Table Table Level Constraint p 38 Temporary Table Column Definition Temporary Table Column Definition CREATE TABLE _ WITH NO LOG E gt Data name p 94 DEFAULT NOT NULL Clause 34 Temp Table Column Constraint Definition p 38 SQL Statements 37 CREATE TABLE Temp Table Column Constraint Definition Temp Table Column Constraint Definition a DISTINCT PRIMARY KEY CHECK Clause p 36 Temp Table Table Level Constraint Temp Table Table Level Constraint an p UNIQUE name a DISTINCT PRIMARY KEY 38 SQL Quick Syntax Guide Stor
31. me View Name Identifier m database i was 9 dbservername SQL Segments 109 Stored Procedure Language Statements CALL Procedure CALL Name p 107 rocedure RETURNING Argument Expression p 96 parameter name SELECT Statement Subset p 69 CONTINUE CONTINUE FOR WHILE FOREACH SPL Statements 113 DEFINE DEFINE SQL Data DEFINE GLOBAL amm Subset DEFAULT name p 94 DEFAULT REFERENCES BYTE NULL g variable name Table N 23 DE column name A Synonym Name 114 501 Quick Syntax Guide EXIT Default Value Clause Default Value Literal Number p 107 Quoted String p 107 Literal Interval p 106 Literal Datetime p 105 DATETIME Field Qualifier EXIT FOR WHILE FOREACH SPL Statements 115 FOR variable left right Statement FOR name N expression expression S STEP increment expression expression left right expression expression increment STEP expression 116 SQL Quick Syntax Guide FOREACH FOREACH SELECT INTO Statement END Block us 31 FOREACH Y 3 WITH HOLD cursor FOR name i uum WITH HOLD EXECUTE Procedure PROCEDURE Name p 107 INTO variable name Expression Subset parameter p 96 name Condition IF Statement ELIF p 89 SPL St
32. name NULL NONE SET SESSION AUTHORIZATION SET SESSION AUTHORIZATION TO SQL Statements 81 SET TRANSACTION SET TRANSACTION SET TRANSACTION READ WRITE READ ONLY OL MAC ISOLATION LEVEL READ COMMITTED REPEATABLE READ SERIALIZABLE READ ONLY ISOLATION LEVEL READ UNCOMMITTED START DATABASE Database START DATABASE Mamo WITH LOG IN pathname p 93 ES MODE ANSI WITH NO LOG 82 SQL Quick Syntax Guide START VIOLATIONS TABLE START VIOLATIONS TABLE l START VIOLATIONS TABLE FOR tablename 5 ru violations diagnostics 2726 ROWS numrows STOP VIOLATIONS TABLE EFE STOP VIOLATIONS TABLE FOR tablename UNLOAD e SELECT UNLOAD TO filename bw E d Statement p DELIMITER delimiter UNLOCK TABLE UNLOCK TABLE SQL Statements 83 UPDATE UPDATE SET Clause SET Clause SET Clause l 2 84 501 Quick Syntax Guide Condition p 89 CURRENT OF uw ud Expression Subset SELECT Statement Subset p 69 Expression Subset 96 SELECT Statement Subset p 69 UPDATE STATISTICS UPDATE STATISTICS UPDATE STATISTICS JE FOR PROCEDURE di Procedure Name p 107 Table DROP DISTRIBUTIONS 3 Synonym 108 N MEDIUM Table
33. statement command line or other specification as in the following diagram of the SET EXPLAIN statement EB SET EXPLAIN ON OFF Each syntax diagram begins at the upper left corner and ends at the upper right corner with a vertical terminator Between these points any path that does not stop or reverse direction describes a possible form of the statement For a few diagrams notes in the text identify path segments that are mutually exclusive Syntax elements in a path represent terms keywords symbols and segments that can appear in your statement Except for separators in loops which the path approaches counterclockwise from the right the path always approaches elements from the left and continues to the right Unless otherwise noted at least one blank character separates syntax elements 4 INFORMIX Elements That Can Appear on the Path Elements That Can Appear on the Path You might encounter one or more of the following elements on a path Element Description KEYWORD A word in UPPERCASE characters is a keyword You must spell the word exactly as shown however you can use either uppercase or lowercase characters Punctuation and other non alphanumeric characters are literal symbols that you must enter exactly as shown E Single quotes are literal symbols that you must enter as shown variable A word in italics represents a value that you must supply A table immediately following
Download Pdf Manuals
Related Search