SQL in a Nutshell by Kevin Kline with Daniel Kline This page lists errors outstanding in the most recent printing. If you have error reports or technical questions, wyou can send them to booktech@oreilly.com. (Please specify the printing date of your copy.) This page was last updated on June 9, 2004. Here's a key to the markup: [page-number]: serious technical mistake {page-number}: minor technical mistake : important language or formatting problem (page-number): minor formatting problem, or language change ?page-number?: question or request for clarification CONFIRMED errors: (13) table 2-2 row money; range is given as two negative values. One should be positive 31 for alter procedure, towards the bottom of the page.... the bolded name of get_next_br should actually be get_next_nbr {30 - 31} RIGHT('000000' + CAST(ROUND(RAND(@random_nbr)*1000000,0)) AS CHAR(6), 6) + the second close parenthesis before AS should not be there and should instead be after the first 6 (look below) RIGHT('000000' + CAST(ROUND(RAND(@random_nbr)*1000000,0) AS CHAR(6)), 6) + {31} for alter procedure, towards the bottom of the page.... the bolded name of get_next_br should actually be get_next_nbr {41} Second Code example; On the code example the column names for the resulting CASE statements are renamed to 'YTD Sales' and 'Number of titles'. Single quotes are used in the code. However for this to work correctly double quotes should be used eg, select CASE .... END "YTD SALES", ... {85} Paragraph before "Examples": "PostgreSQL executes the trigger on each row or once for the entire transaction, using the FOR EACH ROW and FOR EACH STATEMENT clauses, respectively." Postgresql prior to 7.4 does not support the FOR EACH STATEMENT clause. Beginning with 7.4 FOR EACH STATEMENT causes the trigger to execute once for each SQL statement executed, not once for each transaction. [136-137] Left [Outer] Join & Right [Outer] Join; The Oracle theta style examples are switched. i.e. the Left Join example is a Right Outer Join, and the Right Join example is a Left Outer Join. [178] Table 4-7 is cut off after the "objectproperty" function. The follow entries are missing, and should be added to the table: open{[Global]cursor_name}| cursor_variable_name} Opens local or global cursor. Refer to language elements section for examples. opendatasource(provider_name, init_string) Makes a connection to data source without using a linked server name. For examples refer to the Loaders section. openquery(linked_server, ‘query’) Queries remote data source previously setup as a linked server. For an example refer to the Loaders section. openrowset(‘provider_name’, {‘datasource’; ‘user_id’,password | ‘provider_string’}, {[catalog.][schema.]object | ‘query’}) Queries a remote data source without setting it up as a linked server. For an example refer to the Loaders section. parsename(‘object_name’, object_piece) Returns database name, owner name, server name or object name for the object specified. Object piece is an integer between 1 and 4. patindex(‘%pattern%’, expression) Returns position of the first occurrence of a pattern in a string. permissions(object_id, column) Returns a value containing a bitmap with current users permissions on the specified object / column. pi Returns Pi constant. power(numeric_expression, y) Converts argument1 to the power of argument2. radians(numeric_expression) Converts degrees to radians. rand([seed]) Returns pseudo-random FLOAT type value between 0 and 1. replicate(character_expression, integer_expression) Repeats a string a number of times. replace(‘string_expression1’,’string_expression2’,’string_expression3) Replaces the occurrence of the second expression with the third expression IN the first expression. Same as STUFF. reverse(character_expression) Reverses characters of a string. right(character_expression, integer_expression) Returns part of a string consisting of a given number of characters from its right. rtrim(character_expression) Trims trailing space characters from the expression. round(numeric_expression, length [,function]) Rounds argument to the specified precision. rowcount_big() Returns the number of rows affected by last query (same as @@ROWCOUNT). session_user Returns user name in the current connection. sign(numeric_expression) Returns -1 if the argument is negative, 0 if it is 0, and 1 if the argument is positive. sin(float_expression) Returns sine. soundex(character_expression) Returns a 4-character code based on how the argument string sounds. space(integer_expression) Returns a string consisting of a given number of space characters. stats_date(table_id, index_id) Returns date and time when index statistics was last updated. stdev(expression) Returns standard deviation of values in the column. stdevp(expression) Returns standard deviation for the population of values. str(float expression [, length [, decimal]]) Converts numeric data to character strings. stuff(character_expression, start, length, character_expression) Replaces a part of one string with another string. substring(expression, start, length) Extracts a part of a string. sum([All | Distinct] expression) Computes sum of column values. suser_id([‘login’]) Returns system user ID of a given login name. Incidentally, this function will always return NULL with SQL Server 2000. Therefore, avoid using this function. suser_sid([‘login’]) Returns Security ID (SID) for the current user or specified login in binary format. suser_sname([server_user_sid]) Returns login name for the current user or specified login’s Security ID (SID). system_user Returns login name for the current session. tan(float_expression) Returns tangent of the float expression. textptr(column) Returns a pointer to a text, ntext or image column in varbinary format. textvalid(‘table.column’, text_ptr) Returns true or false (1 or 0) depending on whether provided pointer to text, ntext or image column is valid. typeproperty(datatype,property) Returns information about data type properties. unicode(‘ncharacter_expression’) Returns the Unicode integer value for the first character of the input parameter. user Returns user name in the current database for the current connection. user_id([‘user’]) Returns user ID in the current database. user_name([id]) Returns user name in the current database. upper(character_expression) Converts a string to uppercase. var(expression) Returns statistical variance in a column. varp(expression) Returns statistical variance for a population for all values in the expression. year(date) Returns an integer which is a YEAR part of the specified date.