Chapter 72 SQL command SELECT

Chapter 72 SQL command SELECT (4)

WHERE clause

The WHERE clause qualifies or deselects specific rows in the query.
The qualified rows are those whose conditional expression is true.
Conditional expressions are lists of logical tests (predicates) that can be linked through AND and OR logical operators.
These predicates can be inverted using the NOT unary logical operator.

SQL predicates can be divided into the following categories:

  • Comparison predicate
  • BETWEEN predicate
  • LIKE predicate
  • NULL predicate
  • In and% inlist predicate
  • EXISTS predicate
  • For home predicate
  • For home% element predicate

Conditional expressions cannot contain aggregate functions.
If you want to specify the selection criteria using the value returned by the aggregate function, use the HAVING clause.

The WHERE clause can specify an explicit join between two tables using the = (inner join) symbolic join operator.

The WHERE clause can use the arrow syntax (- >) operator to specify an implicit join between the base table and a field from another table.

GROUP BY clause

The GROUP BY clause accepts the result rows of the query and divides them into separate groups based on one or more database columns.
When SELECT is used with GROUP BY, a row is retrieved for each different value of the GROUP BY field.
The GROUP BY clause is conceptually similar to the IRIS extension% FOREACH, but GROUP BY operates on the entire query, while% FOREACH allows aggregation to be selected on the sub population without limiting the entire query population.
For example:

SELECT Home_State, COUNT(Home_State) AS Population
 FROM Sample.Person
  GROUP BY Home_State

This query is for each different Home_State returns a row.

HAVING clause

The HAVING clause is similar to the WHERE clause that operates on a group.
It is usually used with the GROUP BY clause or the% after having keyword.
HAVING clause qualifies or deselects a specific row in a query.
The qualified rows are those whose conditional expression is true.
Conditional expressions are lists of logical tests (predicates) that can be linked through AND and OR logical operators.
Conditional expressions can contain aggregate functions.

ORDER BY clause

The ORDER BY clause consists of an ORDER BY keyword followed by a selection or a comma separated list of items that specifies the order in which rows are displayed.
Each item can have an optional ASC (ascending order) or desc (descending order).
The default is ascending.
The ORDER BY clause is applied to the results of the query and is often paired with the TOP clause.

The following example returns the selected fields of all rows in the database and arranges them in ascending age order:

SELECT Home_State, Name, Age 
FROM Sample.Person
ORDER BY Age

SELECT and transactions

The transaction that executes the query is defined as READ COMMITTED or READ UNCOMMITTED.
The default is READ UNCOMMITTED.
Queries that are not in a transaction are defined as READ UNCOMMITTED.

  • If READ UNCOMMITTED, SELECT returns the current state of the data, including changes made to the data by uncommitted ongoing transactions.
    These changes may then be rolled back.

  • If READ COMMITTED, the behavior depends on the content of the SELECT statement.
    Generally, the SELECT statement in read committed mode will only return the insert and update changes to the submitted data.
    Data rows that have been deleted by ongoing transactions are not returned, even if they have not been committed and may be rolled back.

However, if the SELECT statement contains the% NOLOCK keyword, DISTINCT clause, or GROUP BY clause, SELECT returns the current state of the data, including changes to the data that have not been committed in the current transaction.
The aggregate function in SELECT also returns the current state of the data for the specified column, including uncommitted changes.

Query Metadata

You can use Dynamic SQL to return metadata about the query, such as the number of columns specified in the query, the name (or alias) of the column specified in the query, and the data type of the column specified in the query.

Example

In the following example, the AvgAge computed field is calculated on all records in Sample.Person.
The HAVING clause manages the avgmiddleage calculated field to calculate the average age of those over 40 from all records in Sample.Person.
Therefore, each row of AvgAge and AvgMiddleAge has the same value.
ORDER BY clause by home_ The state field value displays the rows alphabetically.

SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 HAVING Age > 40
 ORDER BY Home_State

WHERE/HAVING/ORDER BY

In the following example, the WHERE clause limits the selection to seven specified northeastern states.
The AvgAge computed field is based on which home_ The records of states are calculated.
The HAVING clause manages the AvgMiddleAge computed field from the specified home_ The average age of people over the age of 40 is calculated from the records of states.
Therefore, each row of AvgAge and AvgMiddleAge has the same value.
ORDER BY clause by home_ The state field value displays the rows alphabetically.

SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 WHERE Home_State IN ('ME','NH','VT','MA','RI','CT','NY')
 HAVING Age > 40
 ORDER BY Home_State

GROUP BY/HAVING/ORDER BY

The GROUP BY clause causes each home_ The state group calculates the AvgAge computed field separately.
The GROUP BY clause also limits the display of output to from each home_ The first record encountered by state.
The HAVING clause manages the avgmiddleage calculated field and calculates each home_ The average age of people over 40 in the state group.
ORDER BY clause by home_ The state field value displays the rows alphabetically.

 SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 GROUP BY Home_State
 HAVING Age > 40
 ORDER BY Home_State

WHERE/GROUP BY/HAVING/ORDER BY

The WHERE clause limits the choice of seven northeastern states.
The GROUP BY clause results in_ Each in the state group calculates the AvgAge computed field separately.
The GROUP BY clause also restricts the display of output from each specified home_ The first record encountered by state.
The HAVING clause manages the avgmiddleage calculated field and calculates 7 home values_ The average age of people over 40 in each group in the state group.
ORDER BY clause by home_ The state field value displays the rows alphabetically.

SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 WHERE Home_State IN ('ME','NH','VT','MA','RI','CT','NY')
 GROUP BY Home_State
 HAVING Age > 40
 ORDER BY Home_State

Embedded SQL and dynamic SQL examples

Embedded SQL and dynamic SQL can be used to issue SELECT queries from ObjectScript programs.

The following embedded SQL program retrieves data values from a record and places them in the output host variable specified in the INTO clause.

ClassMethod Select2()
{
	n SQLCODE,%ROWCOUNT
	&sql(
		SELECT Home_State, Name, Age
			INTO :a, :b, :c
		FROM Sample.Person)
	if SQLCODE=0 {
		w !,"  Name=",b
		w !,"  Age=",c
		w !,"  Home State=",a
		w !,"Row count is: ",%ROWCOUNT 
	} else {
		w !,"SELECT fail, SQLCODE=",SQLCODE  
	}
}


DHC-APP>d ##class(PHA.TEST.SQLCommand).Select2()
 
  Name=yaoxin
  Age=31
  Home State=WI
Row count is: 1

This program retrieves (at most) one line, so the% ROWCOUNT variable is set to 0 or 1.
To retrieve multiple rows, you must declare a cursor and use the FETCH command.

The following dynamic SQL example first tests whether the required table exists and checks the SELECT privilege of the current user on the table.
Then execute the query and return the result set.
It uses a WHILE loop to repeatedly call the% Next method on the first 10 records of the result set.
It uses the% GetData method to display three field values that specify the field location specified in the SELECT statement:

ClassMethod Select3()
{
#;	s tname="Sample.Person"
#;	if $SYSTEM.SQL.TableExists(tname) & $SYSTEM.SQL.CheckPrivilege($USERNAME, "1," _ tname, "s"){
#;		GOTO SpecifyQuery
#;	} else {
#;		w "Table unavailable"  
#;		q
#;	}
#;SpecifyQuery
	s myquery = 3
	s myquery(1) = "SELECT Home_State,Name,SSN,Age"
	s myquery(2) = "FROM Sample.Person"
	s myquery(3) = "ORDER BY Name"
	s tStatement = ##class(%SQL.Statement).%New()
	s qStatus = tStatement.%Prepare(.myquery)
	if qStatus '= 1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rset = tStatement.%Execute()
	if rset.%SQLCODE=0 {
		s x=0
		while x < 10 {
			s x = x + 1
			s status=rset.%Next()
			w rset.%GetData(2)," "   /* Name field */
			w rset.%GetData(1)," "   /* Home_State field */
			w rset.%GetData(4),!     /* Age field */
		}
		w !,"End of Data"
		w !,"SQLCODE=",rset.%SQLCODE," Row Count=",rset.%ROWCOUNT
	} else {
		w !,"SELECT fail, SQLCODE=",rset.%SQLCODE 
	}
}

Tags: Database SQL

Posted on Wed, 10 Nov 2021 22:50:24 -0500 by mesh2005

Hot Tags

©2023 Programmer Help