SQL Commands

SELECT

In simple data manipulation, you use the SELECT command to retrieve a single row, i.e., a one-row query result. The syntax of the SELECT command that describes a one-row query result is:

	SELECT SelectList

	  INTO HostVariables

	  FROM TableNames

	 WHERE SearchCondition

Note that the GROUP BY, HAVING, and ORDER BY clauses are not necessary, since these clauses usually describe multiple-row query results.

You may omit the WHERE clause from certain queries when the select list contains only aggregate functions:

	EXEC SQL SELECT  {{AVG}}(SalesPrice)

	           INTO :AvgSalesPrice

	           FROM  PurchDB.Parts;

A WHERE clause may be used, however, to qualify the rows over which the aggregate function is applied:

	EXEC SQL SELECT  AVG(SalesPrice)

	           INTO :AvgSalesPrice

	           FROM  PurchDB.Parts

	          WHERE  SalesPrice > :SalesPrice;

If the select list does not contain aggregate functions, a WHERE clause is needed to restrict the query result to a single row:

	EXEC SQL SELECT  PartName,  SalesPrice

	           INTO :PartName, :SalesPrice

	           FROM  PurchDB.Parts

	          WHERE  PartNumber = :PartNumber;

Because the host variables that hold query results for a simple SELECT command are not arrays of records, they can hold only a single row. A runtime error occurs when multiple rows qualify for a simple SELECT command. You can test for an sqlcode value of -10002 to detect this condition:

	#define MultipleRows    -10002

	.

	.

	.

	int GetRow()

	{

	.

	.

	.

	     The SELECT command is executed here.



	     if (sqlca.sqlcode == MultipleRows) {

	        printf("\n WARNING:  More than one row qualifies!");

	        }

	}

When multiple rows qualify but the receiving host variables are not in an array of records and the BULK option is not specified, none of the rows are returned.

When a column named in the WHERE clause has a unique index on it, you can omit testing for multiple-row query results. A unique index prevents the key column(s) from having duplicate values. The following index, for example, ensures that only one row will exist for any part number in PurchDB.Parts:

	CREATE UNIQUE INDEX PartNumIndex

	                 ON PurchDB.Parts (PartNumber)

If a key column of a unique index can contain a null value, the unique index insures that no more than one null value can exist for that column.

It is useful to execute the SELECT command before executing the INSERT, DELETE, or UPDATE commands in the following situations:

INSERT

In simple data manipulation, you use the INSERT command to either insert a single row or copy one or more rows into one table from another table.

Use the following syntax of the INSERT command to insert a single row:

	INSERT INTO  TableName

	            (ColumnNames)

	     VALUES (DataValues)

You can omit ColumnNames when you provide values for all columns in the target table:

	EXEC SQL INSERT INTO    PurchDB.Parts

	               VALUES (:PartNumber,

	                       :PartName   :PartNameInd,

	                       :SalesPrice :SalesPriceInd);

Remember that when you do include Column Names but do not name all the columns in the target table, ALLBASE/SQL attempts to insert a null value into each unnamed column. If an unnamed column was defined as NOT NULL, the INSERT command fails.

To copy one or more rows from one or more tables to another table, use the following syntax of the INSERT command:

	INSERT  INTO  TableName

	             (ColumnNames)

	      SELECT  SelectList

	        FROM  TableNames

	       WHERE  SearchCondition1

	    GROUP BY  ColumnName

	      HAVING  SearchCondition2

Note that the SELECT command embedded in this INSERT command cannot contain an INTO or ORDER BY clause. In addition, any host variables used must be within the WHERE or HAVING clauses.

The following example copies historical data for filled orders into table PurchDB.OldOrders, then deletes rows for these orders from PurchDB.Orders, keeping that table minimal in size.

	The INSERT command copies rows from PurchDB.Orders to 				PurchDB.OldOrders.



	EXEC SQL INSERT  INTO  PurchDB.OldOrders

	                      (OldOrder, OldVendor, OldDate)

	               SELECT  OrderNumber, VendorNumber, OrderDate

	                 FROM  PurchDB.Orders

	                WHERE  OrderNumber = :OrderNumber;



	Then the DELETE command deletes rows from PurchDB.Orders.



	EXEC SQL DELETE  FROM  PurchDB.Orders

	                WHERE  OrderNumber: = OrderNumber;

UPDATE

In simple data manipulation, you use UPDATE command syntax to change data in one or more columns:

	UPDATE TableName

	   SET Columname = ColumnValue

	       [,...]

	WHERE SearchCondition

As in the case of the DELETE command, if you omit the WHERE clause, the value of any column specified is changed in all rows of the table.

If the WHERE clause is specified, all rows satisfying the search condition are changed, for example:

	EXEC SQL UPDATE PurchDB.Vendors

	            SET ContactName   = :ContactName :ContactNameInd,

	                VendorStreet  = :VendorStreet,

	                VendorCity    = :VendorCity,

	                VendorState   = :VendorState,

	                VendorZipCode = :VendorZipCode

	          WHERE VendorNumber  = :VendorNumber;

In this example, column ContactName can contain a null value. To insert a null value, the program must assign a number less than zero to the indicator variable for this column, ContactNameInd:

	The program prompts the user for new values for the four columns.

	printf ("\n Enter Vendor Street > ");

	getline(VendorStreet);



	printf ("\n Enter Vendor City > ");

	getline(VendorCity);



	printf ("\n Enter Vendor State > ");

	getline(VendorState);



	printf ("\n Enter Vendor Zip Code > ");

	getline(VendorZipCode);



	printf ("\n Enter Contact Name (0 for null) > ");

	getline(ContactName);



	   If the user enters a 0 to assign a null value to column

     	ContactName, the program assigns a -1 to the indicator

     	variable; otherwise, the program assigns a 0 to this variable:



	if (ContactName[0] == '0') {

	   ContactNameInd = -1;

	   }

	else

	   ContactNameInd =  0;

DELETE

In simple data manipulation, you use DELETE command syntax to delete one or more rows from a table:

	DELETE FROM TableName

	      WHERE SearchCondition

The WHERE clause specifies a SearchCondition that rows must meet to be deleted, for example:

	EXEC SQL DELETE FROM PurchDB.Orders

	               WHERE OrderDate < :OrderDate;

If the WHERE clause is omitted, all rows in the table are deleted.