Differences between SAS SQL and SPD Server SQL :: SAS® Scalable Performance Data Server 5.3: User's Guide

Reserved Keywords

SPD Server uses keywords to initiate statements or to refer to syntax elements. In SPD Server SQL, keywords are treated as reserved words. For example, you can use the words “where” and “group” only in certain ways because SPD Server uses WHERE and GROUP BY clauses. You cannot use them for identifiers because this use introduces ambiguity. For example,

select count(*) from sales.receipts where
                        table='April';

is a valid but ambiguous statement.

If you use a keyword as an identifier and the request is submitted via explicit SQL pass-through, the server will return an error and the request will fail. If you use a keyword as an identifier in an implicit SQL pass-through request, the request will fail and will not return an error. You must set

%let
                        SPDSIPDB=YES

to see the failure message.

For both implicit and explicit SQL pass-through failures, the location of the failure is indicated in the failure message with a # (number sign). For example, in the following message, the # indicates a parsing error on the keyword MATCH:

SPDS_NOTE:  Parse Failure:  select TXT_3.Messages from #MATCH TXT_1 inner join ...

In contrast, SAS SQL allows keywords in some, but not all, syntax locations. SAS SQL also uses underscores to denote errors.

The following list contains current SPD Server keywords. Some of the words are reserved for future enhancements to SPD Server SQL.

add	date	grant	missing	select
all	dec	group	modify	set
alter	decimal	gt	natural	smallint
and	default	having	ne	some
any	delete	in	no	table
as	desc	index	not	then
asc	describe	indexes	notin	to
async	dictionary	informat	null	trailing
begin	disconnect	inner	num	trim
between	distinct	insert	numeric	true
both	double	int	on	union
by	drop	integer	operation	unique
calculated	else	intersect	option	unknown
cascade	end	into	or	update
case	engname	is	order	upper
char	engopt	join	outer	using
character	eq	label	overlaps	validate
column	except	le	partial	values
connect	execute	leading	precision	varchar
connection	exists	left	privileges	verbose
contains	false	lib	public	view
contents	float	libref	real	when
copy	for	like	references	where
corr	format	load	reset	with
corresponding	from	lower	restrict	without
create	full	lt	revoke	yes
cross	ge	match	right

Table Options and Delimiters

SPD Server explicit SQL pass-through uses brackets to delimit table options. SAS SQL uses parentheses as delimiters.

Here is an example of how a table option is specified for CREATE TABLE in explicit SQL pass-through:

execute(
create table spptbv05x[netpacksize=12288](
    z char(5),
    x num,
    y numeric)
) by sasspds;

Here is an example of how to specify a table option in an INSERT statement:

execute(
  insert into spptbv05x [syncadd=yes]
     values("one",1,50)
     values("two",2,30)
     values("three",3,30)
     values("four",4,60)
     values("five",5,70)
     values("six",6,80)
) by sasspds;

Mixing Scalar Expressions and Boolean Predicates

SPD Server SQL does not allow mixing scalar expressions with Boolean predicates. SAS SQL does allow mixing scalar expressions with Boolean predicates in most places.

Scalar expressions represent a single data value, either a numeric value or a string from a constant specification. Examples include the following:

1
'hello there'
'31-DEC-60'd
a function: for example: avg(a*b)
a column name: for example: sales.product_id
the CASE expression
a subquery that returns a single run-time value

.

Boolean predicates are either true or false. They are used in WHERE clauses, in HAVING clauses, and in the CASE expression. They cannot be used in SELECT clauses or assigned to columns in an UPDATE statement. Mixing scalar expressions and Boolean predicates result in errors. Here is an example:

select * from connection to sasspds
   (select * from sales where x=1 and 10);

This example produces this error:

 SPDS_ERROR: Parse Failure: select * from x where x=1 and 10#;

The # (number sign) indicates where the parsing error occurred.

INTO Clause

SPD Server SQL does not support the INTO clause. For example, SPD Server SQL does not support the following statement:

select a, b into :var1, :var2 from t where a > 7;

In contrast, SAS SQL supports the INTO clause.

Tilde Negation

SPD Server SQL supports the use of the tilde character (~) only to negate the equals operator (=), as in ~= (not equals). SAS SQL supports the use of the tilde character where the tilde is synonymous with not and can be combined with various operators. For example, SAS SQL can use the tilde with the BETWEEN operator, as in ~BETWEEN (not between). SPD Server does not recognize this expression.

Nested Queries

SAS SQL permits subqueries without delimiting parentheses in more places than does SPD Server SQL. SPD Server SQL uses parentheses to explicitly group subqueries or expressions that are nested in a query statement whenever possible. Queries with nested expressions execute more reliably and are also easier to read.

USER Value

SPD Server SQL does not support the USER keyword in the INSERT statement. For example, the following query fails in SPD Server SQL:

insert into t1(myname) values(USER);

Supported Functions

SPD Server SQL supports most of the functions that SAS supports. For more information, see SAS Functions and CALL Routines: Reference.