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 form 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 form 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 form 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 the UPDATE command 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 the DELETE command 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.