6.2.1 How to emulate the Oracle decode function/crosstab
6.2.2 How to implement if-then-else within a select-clause.
6.2.3 deleted due to copyright hassles with the publisher
6.2.4 How to pad with leading zeros an int or smallint.
6.2.5 Divide by zero and nulls.
6.2.6 Convert months to financial months.
6.2.7 Hierarchy traversal - BOMs.
6.2.8 Is it possible to call a UNIX command from within a stored procedure or a trigger?
6.2.9 Information on Identities and Rolling your own Sequential Keys
6.2.10 How can I execute dynamic SQL with ASE
6.2.11 Is it possible to concatenate all the values from a column and return a single row?
6.2.12 Selecting rows N to M without Oracle's rownum?
6.2.13 How can I return number of rows that are returned from a grouped query without using a temporary table?
If you are using ASE version 11.5 or later, the simplest way to implement the Oracle decode is with the CASE statement. The following code snippet should be compared with the example using a characteristic function given below .
SELECT STUDENT_ID,
(CASE WHEN COURSE_ID = 101 THEN 1 ELSE 0 END) AS COURSE_101,
(CASE WHEN COURSE_ID = 105 THEN 1 ELSE 0 END) AS COURSE_105,
(CASE WHEN COURSE_ID = 201 THEN 1 ELSE 0 END) AS COURSE_201,
(CASE WHEN COURSE_ID = 210 THEN 1 ELSE 0 END) AS COURSE_210,
(CASE WHEN COURSE_ID = 300 THEN 1 ELSE 0 END) AS COURSE_300
GROUP BY STUDENT_ID
ORDER BY STUDENT_ID
However, if you have a version of ASE that does not support the case statement, then you will have to try the following. There may be other reasons to try characteristics functions. If you go to the Amazon web site and look for reviews for of Rozenshteins book, Advanced SQL, you will see that one reviewer believes that a true crosstab is not possible with the case statement. I am not sure. I have also not done any performance tests to see which is quicker.
There is a neat way to use boolean logic to perform cross-tab or rotation queries easily, and very efficiently. Using the aggregate 'Group By' clause in a query and the ISNULL(), SIGN(), ABS(), SUBSTRING() and CHARINDEX() functions, you can create queries and views to perform all kinds of summarizations.
This technique does not produce easily understood SQL statements.
If you want to test a field to see if it is equal to a value, say 100, use the following code:
SELECT (1- ABS( SIGN( ISNULL( 100 - <field>, 1))))
The innermost function will return 1 when the field is null, a positive value if the field < 100, a negative value if the field is > 100 and will return 0 if the field = 100. This example is for Sybase or Microsoft SQL server, but other servers should support most of these functions or the COALESCE() function, which is the ANSI equivalent to ISNULL.
The SIGN() function returns zero for a zero value, -1 for a negative value, 1 for a positive value The ABS() function returns zero for a zero value, and > 1 for any non-zero value. In this case it will return 0 or 1 since the argument is the function SIGN(), thus acting as a binary switch.
Put it all together and you get '0' if the value match, and '1' if they don't. This is not that useful, so we subtract this return value from '1' to invert it, giving us a TRUE value of '1' and a false value of '0'. These return values can then be multiplied by the value of another column, or used within the parameters of another function like SUBSTRING() to return a conditional text value.
For example, to create a grid from a student registration table containing STUDENT_ID and COURSE_ID columns, where there are 5 courses (101, 105, 201, 210, 300) use the following query:
Compare this version with the case statement above.
SELECT STUDENT_ID,
(1- ABS( SIGN( ISNULL( 101 - COURSE_ID, 1)))) COURSE_101,
(1- ABS( SIGN( ISNULL( 105 - COURSE_ID, 1)))) COURSE_105,
(1- ABS( SIGN( ISNULL( 201 - COURSE_ID, 1)))) COURSE_201,
(1- ABS( SIGN( ISNULL( 210 - COURSE_ID, 1)))) COURSE_210,
(1- ABS( SIGN( ISNULL( 300 - COURSE_ID, 1)))) COURSE_300
GROUP BY STUDENT_ID
ORDER BY STUDENT_ID
ASE 11.5 introduced the case statement, which can be used to replace a lot of this 'trick' SQL with more readable (and standard) code. With a case statement, an if then else is as easy as:
declare @val char(20)
select @val = 'grand'
select case when @val = 'small' then
'petit'
else
'grand'
end
However, quite a number of people are still using pre-11.5 implementations, including those people using the free 11.0.3.3 Linux release. In that case you can use the following recipe.
To implement the following condition in a select clause:
if @val = 'small' then
print 'petit'
else
print 'grand'
fi
in versions of ASE prior to 11.5 do the following:
select isnull(substring('petit', charindex('small', @val), 255), 'grand')
To test it out, try this:
declare @val char(20)
select @val = 'grand'
select isnull(substring('petit', charindex('small', @val), 255), 'grand')
This code is not readily understandable by most programmers, so remember to comment it well.
By example:
declare @Integer int
/* Good for positive numbers only. */
select @Integer = 1000
select "Positives Only" =
right( replicate("0", 12) + convert(varchar, @Integer), 12)
/* Good for positive and negative numbers. */
select @Integer = -1000
select "Both Signs" =
substring( "- +", (sign(@Integer) + 2), 1) +
right( replicate("0", 12) + convert(varchar, abs(@Integer)), 12)
select @Integer = 1000
select "Both Signs" =
substring( "- +", (sign(@Integer) + 2), 1) +
right( replicate("0", 12) + convert(varchar, abs(@Integer)), 12)
go
Produces the following results:
Positives Only -------------- 000000001000 Both Signs ------------- -000000001000 Both Signs ------------- +000000001000
During processing, if a divide by zero error occurs you will not get the answer you want. If you want the result set to come back and null to be displayed where divide by zero occurs do the following:
1> select * from total_temp
2> go
field1 field2
----------- -----------
10 10
10 0
10 NULL
(3 rows affected)
1> select field1, field1/(field2*convert(int,
substring('1',1,abs(sign(field2))))) from total_temp
2> go
field1
----------- -----------
10 1
10 NULL
10 NULL
To convert months to financial year months (i.e. July = 1, Dec = 6, Jan = 7, June = 12 )
select ... ((sign(sign((datepart(month,GetDate())-6) * -1)+1) *
(datepart(month, GetDate())+6))
+ (sign(sign(datepart(month, GetDate())-7)+1) *
(datepart(month, GetDate())-6)))
...
from ...
select charindex(datename(month,getdate()), " July August September October November December January Febuary March April May June " ) / 10
In the above example, the embedded blanks are significant.
Alright, so you wanna know more about representing hierarchies in a relational database? Before I get in to the nitty gritty I should at least give all of the credit for this algorithm to: "_Hierarical_Structures:_The_Relational_Taboo!_, _(Can_ Transitive_Closure_Queries_be_Efficient?)_", by Michael J. Kamfonas as published in 1992 "Relational Journal" (I don't know which volume or issue).
The basic algorithm goes like this, given a tree (hierarchy) that looks roughly like this (forgive the ASCII art--I hope you are using a fixed font to view this):
a
/ \
/ \
/ \
b c
/ \ /|\
/ \ / | \
/ \ / | \
d e f | g
Note, that the tree need not be balanced for this algorithm to work.
The next step assigned two numbers to each node in the tree, called left and right numbers, such that the left and right numbers of each node contain the left and right numbers of the ancestors of that node (I'll get into the algorithm for assigning these left and right numbers later, but, hint: use a depth-first search):
1a16
/ \
/ \
/ \
2b7 8c15
/ \ /|\
/ \ / | \
/ \ / | \
3d4 5e6 9f10 11g12 13h14
Side Note: The careful observer will notice that these left and right numbers look an awful lot like a B-Tree index.
So, you will notice that all of the children of node 'a' have left and right numbers between 1 and 16, and likewise all of the children of 'c' have left and right numbers between 8 and 15. In a slightly more relational format this table would look like:
Table: hier
node parent left_nbr right_nbr
----- ------ -------- ---------
a NULL 1 16
b a 2 7
c a 8 15
d b 3 4
e b 5 6
f c 9 10
g c 11 12
h c 13 14
So, given a node name, say @node (in Sybase variable format), and you want to know all of the children of the node you can do:
SELECT h2.node
FROM hier h1,
hier h2
WHERE h1.node = @node
AND h2.left_nbr > h1.left_nbr
AND h2.left_nbr < h1.right_nbr
If you had a table that contained, say, the salary for each node in your hierarchy (assuming a node is actually a individual in a company) you could then figure out the total salary for all of the people working underneath of @node by doing:
SELECT sum(s.salary)
FROM hier h1,
hier h2,
salary s
WHERE h1.node = @node
AND h2.left_nbr > h1.left_nbr
AND h2.right_nbr > h1.right_nbr
AND s.node = h2.node
Pretty cool, eh? And, conversely, if you wanted to know how much it cost to manage @node (i.e. the combined salary of all of the boss's of @node), you can do:
SELECT sum(s.salary)
FROM hier h1,
hier h2,
salary s
WHERE h1.node = @node
AND h2.left_nbr < h1.left_nbr
AND h2.left_nbr > h1.right_nbr
AND s.node = h2.node
Now that you can see the algorithm in action everything looks peachy, however the sticky point is the method in which left and right numbers get assigned. And, unfortunately, there is no easy method to do this relationally (it can be done, it just ain't that easy). For an real- world application that I have worked on, we had an external program used to build and maintain the hierarchies, and it was this program's responsibility to assign the left and right numbers.
But, in brief, here is the algorithm to assign left and right numbers to every node in a hierarchy. Note while reading this that this algorithm uses an array as a stack, however since arrays are not available in Sybase, they are (questionably) emulated using a temp table.
DECLARE @skip int,
@counter int,
@idx int,
@left_nbr int,
@node varchar(10)
/*-- Initialize variables --*/
SELECT @skip = 1000, /* Leave gaps in left & right numbers */
@counter = 0, /* Counter of next available left number */
@idx = 0 /* Index into array */
/*
* The following table is used to emulate an array for Sybase,
* for Oracle this wouldn't be a problem. :(
*/
CREATE TABLE #a (
idx int NOT NULL,
node varchar(10) NOT NULL,
left_nbr int NOT NULL
)
/*
* I know that I always preach about not using cursors, and there
* are ways to get around it, but in this case I am more worried
* about readability over performance.
*/
DECLARE root_cur CURSOR FOR
SELECT h.node
FROM hier h
WHERE h.parent IS NULL
FOR READ ONLY
/*
* Here we are populating our "stack" with all of the root
* nodes of the hierarchy. We are using the cursor in order
* to assign an increasing index into the "stack"...this could
* be done using an identity column and a little trickery.
*/
OPEN root_cur
FETCH root_cur INTO @node
WHILE (@@sqlstatus = 0)
BEGIN
SELECT @idx = @idx + 1
INSERT INTO #a VALUES (@idx, @node, 0)
FETCH root_cur INTO @node
END
CLOSE root_cur
DEALLOCATE CURSOR root_cur
/*
* The following cursor will be employed to retrieve all of
* the children of a given parent.
*/
DECLARE child_cur CURSOR FOR
SELECT h.node
FROM hier h
WHERE h.parent = @node
FOR READ ONLY
/*
* While our stack is not empty.
*/
WHILE (@idx > 0)
BEGIN
/*
* Look at the element on the top of the stack.
*/
SELECT @node = node,
@left_nbr = left_nbr
FROM #a
WHERE idx = @idx
/*
* If the element at the top of the stack has not been assigned
* a left number yet, then we assign it one and copy its children
* on the stack as "nodes to be looked at".
*/
IF (@left_nbr = 0)
BEGIN
/*
* Set the left number of the current node to be @counter + @skip.
* Note, we are doing a depth-first traversal, assigning left
* numbers as we go.
*/
SELECT @counter = @counter + @skip
UPDATE #a
SET left_nbr = @counter
WHERE idx = @idx
/*
* Append the children of the current node to the "stack".
*/
OPEN child_cur
FETCH child_cur INTO @node
WHILE (@@sqlstatus = 0)
BEGIN
SELECT @idx = @idx + 1
INSERT INTO #a VALUES (@idx, @node, 0)
FETCH child_cur INTO @node
END
CLOSE child_cur
END
ELSE
BEGIN
/*
* It turns out that the current node already has a left
* number assigned to it, so we just need to assign the
* right number and update the node in the actual
* hierarchy.
*/
SELECT @counter = @counter + @skip
UPDATE h
SET left_nbr = @left_nbr,
right_nbr = @counter
WHERE h.node = @node
/*
* "Pop" the current node off our "stack".
*/
DELETE #a WHERE idx = @idx
SELECT @idx = @idx - 1
END
END /* WHILE (@idx > 0) */
DEALLOCATE CURSOR child_cur
While reading through this, you should notice that assigning the left and right numbers to the entire hierarchy is very costly, especially as the size of the hierarchy grows. If you put the above code in an insert trigger on the hier table, the overhead for inserting each node would be phenomenal. However, it is possible to reduce the overall cost of an insertion into the hierarchy.
Deletes on this table should never cause the left and right numbers to be re-assigned (you could even have a trigger automagically re-parent orphaned hierarchy nodes).
All-in-all, this algorithm is very effective as long as the structure of the hierarchy does not change very often, and even then, as you can see, there are ways of getting around a lot of its inefficiencies.
The Adaptive Server (11.5) will allow O/S calls from within stored procedures and triggers. These stored procedures are known as extended stored procedures.
Periodically folks ask if it's possible to make a system command or call a UNIX process from a Trigger or a Stored Procedure.
The typical ways people have implemented this capability is:
This release includes a new built-in function called syb_sendmsg(). Using this function you can send a message up to 255 bytes in size to another application from the ASE. The arguments that need to be passed to syb_sendmsg() are the IP address and port number on the destination host, and the message to be sent. The port number specified can be any UDP port, excluding ports 1-1024, not already in use by another process. An example is:
1> select syb_sendmsg("120.10.20.5", 3456, "Hello")
2> go
This will send the message "Hello" to port 3456 at IP address '120.10.20.5'. Because this built-in uses the UDP protocol to send the message, the ASE does not guarantee the receipt of the message by the receiving application.
Also, please note that there are no security checks with this new function. It is possible to send sensitive information with this command and Sybase strongly recommends caution when utilizing syb_sendmsg to send sensitive information across the network. By enabling this functionality, the user accepts any security problems which result from its use (or abuse).
To enable this feature you should run the following commands as the System Security Officer.
1> sp_configure "allow sendmsg", 1 2> go 1> sp_configure "syb_sendmsg port number",<port number> 2> go 1> reconfigure with override -- Not necessary with 11.0 and above 2> go
The server must be restarted to set the port number.
Since syb_sendmsg() installs configuration parameter "allow sybsendmsg", existing scripts that contain the syntax
1> sp_configure allow, 1 2> go
to enable updates to system tables should be altered to be fully qualified as in the following:
1> sp_configure "allow updates", 1 2> go
If existing scripts are not altered they will fail with the following message:
1> sp_configure allow, 1 2> go Configuration option is not unique. duplicate_options ---------------------------- allow updates allow sendmsg (return status = 1)
(The above error is a little out of date for the latest releases of ASE, there are now 8 rows that contain "allow", but the result is the same.)
The syb_sendmsg() function requires the addition on two config values. If it becomes necessary to roll back to a previous ASE version which does not include syb_sendmsg(), please follow the instructions below.
#include <stdlib.h>
#include <stdio.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#include <unistd.h>
#include <fcntl.h>
main(argc, argv)
int argc; char *argv[];
{
struct sockaddr_in sadr;
int portnum,sck,dummy,msglen;
char msg[256];
if (argc <2) {
printf("Usage: udpmon <udp portnum>\n");
exit(1);
}
if ((portnum=atoi(argv[1])) <1) {
printf("Invalid udp portnum\n");
exit(1);
}
if ((sck="socket(AF_INET,SOCK_DGRAM,IPPROTO_UDP))" < 0) {
printf("Couldn't create socket\n");
exit(1);
}
sadr.sin_family = AF_INET;
sadr.sin_addr.s_addr = inet_addr("0.0.0.0");
sadr.sin_port = portnum;
if (bind(sck,&sadr,sizeof(sadr)) < 0) {
printf("Couldn't bind requested udp port\n");
exit(1);
}
for (;;)
{
if((msglen="recvfrom(sck, msg, sizeof(msg), 0, NULL, &dummy))" < 0)
printf("Couldn't recvfrom() from udp port\n");
printf("%.*s\n", msglen, msg);
}
}
This has several sections, culled from various sources. It is better described as "Everything you've ever wanted to know about identities." It will serve to answer the following frequently asked questions:
What are the Features and Advantages of using Identities?
What are the Problems with and Disadvantages of Identities?
Common Questions about Identities
How do I optimize the performance of a table that
uses Identities?
How do I recover from a huge gap in my identity
column?
How do I fix a table that has filled up its identity
values?
OK, I hate identities. How do I generate sequential
keys without using the Identity feature?
How do I optimize a hand-made sequential key system
for best performance?
- Question 8.1 of the comp.database.sybase
FAQ has a quick blurb about identities and sequential numbers. Search down in the page
for the section titled, "Generating Sequential Numbers." Question 8.1 is a
general document describing Performance and Tuning topics to be considered and thus
doesn't go into as much detail as this page.
- There's a white paper by Malcolm Colton available from the sybase web site. Goto the Sybase web site http://www.sybase.com and type Surrogate in the search form. Select the Surrogate Primary Keys, Concurrency, and the Cache Hit Ratio document.
Advantages/Features of Using Identities
There's an entire section devoted to Identity columns in the ASE Reference manual, Chapter 5
Sybase System 10 introduced many changes over the 4.9.x architecture. One of these changes was the Identity feature. The identity column is a special column type that gets automatically updated by the server upon a new row insert. Its purpose is to guarantee a unique row identifier not based on the other data in the row. It was integrated with the server and made memory based for fast value retrieval and no locking (as was/is the case with homegrown sequential key generation schemes).
The Advantages and Features of Identities include:
1> set identity_insert [datababase]..[table] on 2> go
Note however that the System will not verify the uniqueness of the value you specifically insert (unless of course you have a unique index existing on the identity column).
% bcp [database]..[new_table] in [bcp datafile] -Usa -S[server] -f [fmt file] -E
For procedures on resetting identity values during a bcp, see the section regarding
1> sp_dboption [dbname], "auto identity", true 2> go or 1> sp_dboption [dbname], "identity in nonunique index", true 2> go
To tune the size of the auto identity (it defaults to precision 10):
1> sp_configure "size of auto identity", [desired_precision] 2> go
(the identity in nonunique index db_option and the size of auto identity sp_configure
value are new with System 11: the auto identity existed with the original Identity feature
introduction in System 10)
Like other dboptions, you can set these features on the model database before creating new
databases and all your future databases will be configured. Be warned of the pitfalls of
large identity gaps however; see the question regarding Burn
Factor Vulnerability in the Common Questions about
Identities section.
Disadvantages/Drawbacks of Using Identities
NOTE: Sybase 11.02.1 (EBF 6717) and below had a bug (bugid 96089) which would cause "large gaps to occur in identity fields after polite shutdowns." The Sybase 11.02.2 rollup (EBF 6886) fixed this problem. If you're at or below 11.02.1 and you use identities, you should definitely upgrade.
NOTE: there are several other System 11 bugs related to Identities. EBF 7312 fixes BugId 97748, which caused duplicate identity values to be inserted at times. EBF 6886 fixed (in addition to the above described bug) an odd bug (#82460) which caused a server crash when bcping into a table w/ an identity added via alter table. As always, try to stay current on EBFs.
Common questions about Identities
Answer: More or less yes. Oracle's auto-sequencing feature is somewhat transparent to the end user and automatically increments if created as a primary key upon a row insert. The Sybase Identity column is normally specified at table creation and thus is a functional column of the table. If however you set the "auto identity" feature for a database, the tables created will have a "hidden" identity column that doesn't even appear when you execute a select * from [table]. See the Advantages of Identities for more details.
1> create table ident_test 2> (text_field varchar(10), 3> ident_field numeric(5,0) identity) 4> go
Or alter an existing table and add an identity column:
1> alter table existing_table 2> add new_identity_field numeric(7,0) identity 3> go
When you alter a table and add an identity column, the System locks
the table while systematically incrementing and adding unique values to
each row. IF YOU DON'T SPECIFY a precision, Sybase defaults the size to
18! Thats 1,000,000,000,000,000,000-1 possible values and some major
major problems if you ever crash your ASE and burn a default number of
values... (10^18 with the default burn factor will burn 5^14 or
500,000,000,000,000 values...yikes).
1> sp_configure "identity burning set factor" 2> go
the Default value set upon install is 5000. The number "5000" in this case is read as ".05% of all the potential identity values you can have in this particular case will be burned upon an unexpected shutdown." The actual number depends on the size of the identity field as you specified it when you created your table.
To set the burn factor, type:
1> sp_configure "identity burning set factor", [new value] 2> go
This is a static change; the server must be rebooted before it takes
effect.
1> select object_name(id) "table",name "column", prec "precision" 2> from syscolumns 3> where convert(bit, (status & 0x80)) = 1 4> go
this will list all the tables and the field within the table that serves as an
identity, and the size of the identity field.
1> select identity_field from id_test 2> go identity_field -------------- 1 2 3 4 5 500006 500007 500008 (8 rows affected)
Here's your Identity burning options (based on a precision of 10^9 as above):
Burn value % of values # values burned during crash 5000 .05% 500,000 1000 .01% 100,000 100 .001% 10,000 10 .0001% 1,000 1 .00001% 100
So, the absolute lowest amount of numbers you'll burn, assuming you configure the burn factor down to 1 (sp_configure "identity burning set factor", 1) and a precision of 9, is 100 values.
Optimizing your Identity setup for
performance and maintenance
If you've chosen to use Identities in your database, here are some configuration tips to
avoid typical Identity pitfalls:
vulnerability
section for a discussion on what happens to identity values upon ASE crashes. Large
jumps in values can crash front ends that aren't equipped to handle and process numbers
upwards of 10 Trillion. I've seen Powerbuilder applications crash and/or not function
properly when trying to display these large identity values.
1> sp_configure "identity grab size", [number] 2> go
You can prefetch larger numbers of values for each user as they log into the server an
insert rows. The downside of this is, if the user doesn't use all of the prefetched block
of identity values, the unused values are lost (seeing as, if another user logs in the
next block gets assigned to him/her). This can quickly accelerate the depletion of
identity values and can cause gaps in Identity values.
(this feature is new with System 11)
- There is an excellent discussion in document http://www.sybase.com/detail?id=860 on the performance and tuning aspects of Identities. It supplements some of the information located here (Note: this will open in a new browser window).
Recovery from Large Identity value gaps or
Recovery from Identity insert errors/Full Identity tables
This section will discuss how to re-order the identity values for a table following a crash/abnormal shutdown that has resulted in huge gaps in the values. The same procedure is used in cases where the identity field has "filled up" and does not allow inserts anymore. Some applications that use Identities are not truly candidates for this process (i.e., applications that depend on the identity field for business purposes as opposed to simple unique row identifiers). Applications like this that wish to rid their dependence on identities will have to re-evaluate their database design.
% bcp database..table out [data_file] -Usa -S[server] -N
This will create a binary bcp datafile and will force the user to create a .fmt file.
The -N option tells the server to skip the identity field while bcp'ing out.
- drop and recreate the table in question from ddl (make sure your table ddl specifies the
identity field).
- Now bcp back in:
% bcp database.table in [data_file -Usa -S[server] -f[fmt file] -N
The -N option during bcp in tells the server to ignore the data file's placeholder
column for the defined identity column.
Coincidentally, if you bcp out w/o the -N option, drop the table, recreate from ddl specifying the identity field, and bcp back in w/o the -N option, the same effect as above occurs.
(note: if you bcp out a table w/ identity values and then want to preserve the identity values during the bcp back in, use the "-E" option.)
1> select [all columns except identity column] 2> [identity column name ] = identity(desired_precision) 3> into [new_table] 4> from [old table] 5> go
How do I generate Sequential Keys w/o the
Identity feature?
There are many reasons not to use the Identity feature of Sybase. This section will present several alternative methods, along with their advantages and drawbacks. The methods are presented in increasing order of complexity. The most often implemented is Method 3, which is a more robust version of Method 2 and which uses a surrogate-key storage table.
Throughout this section the test table I'm adding lines to and generating sequential numbers for is table inserttest, created like this:
1> create table inserttest 2> (testtext varchar(25), counter int) 3> go
1> begin tran
2> declare @nextkey int
3> select @nextkey=max(counter)+1 from inserttest holdlock
4> insert inserttest (testtext,counter) values ("test_text,@nextkey")
5> go
1> commit tran
2> go
This method is rather inefficient, as large tables will take minutes to return a
max(column) value, plus the entire table must be locked for each insert (since the max()
will perform a table scan). Further, the select statement does not guarantee an exclusive
lock when it executes unless you have the "holdlock" option; so either duplicate
values might be inserted to your target table or you have massive deadlocking.
1> create table keystorage 2> (tablename varchar(25), 4> lastkey int) 5> go
And initially populate it with the tablenames and last values inserted (enter in a 0 for tables that are brand new).
1> insert into keystorage (tablename,lastkey) 2> select "inserttest", max(counter) from inserttest 3> go
Now, whenever you go to insert into your table, go through a process like this:
1> begin tran
2> update keystorage set lastkey=lastkey+1 where tablename="inserttest"
3> go
1> declare @lastkey int
2> select @lastkey = lastkey from keystorage where tablename="inserttest"
3> insert inserttest (testtext,counter) values ("nextline",@lastkey)
4> go
1> commit tran
2> go
There is plenty of room for error checking with this process: for example (code adapted from Colm O'Reilly (colm@mail.lk.blackbird.ie) post to Sybase-L 6/20/97):
1> begin tran
2> update keystorage set lastkey=lastkey+1 where tablename="inserttest"
3> if @@rowcount=1
4> begin
5> declare @lastkey int
6> select @lastkey=lastkey from keystorage where tablename="inserttest"
7> end
8> commit tran
9> begin tran
10> if @lastkey is not null
11> begin
12> insert inserttest (testtext,counter) values ("third line",@lastkey)
13> end
14> commit tran
15> go
This provides a pretty failsafe method of guaranteeing the success of the select statements involved in the process. You still have a couple of implementation decisions though:
Some DBAs experienced with this keystorage table method warn of large amounts of blocking
in high insert activity situations, a potential drawback.
-----------------------------------------------------------------
----
--
DECLARE @sql_err int, @sql_count int
--
begin tran
--
select @out_seq = 0
--
UPDATE NEXT_SEQUENCE
SET next_seq_id
= ( next_seq_id
* ( sign(1 + sign(max_seq_id - next_seq_id) ) -- evaluates: 0 [when
-- next > max]; else 1
* sign(max_seq_id - next_seq_id) -- evaluates: 0 [when next = max];
-- 1 [next < max];
-- -1 [next > max]
) -- both evaluate to 1 when next < max
) + 1 -- increment by [or restart at] 1
WHERE seq_type = @in_seq_type
--
select @sql_err = @@error, @sql_count = @@rowcount
--
IF @sql_err = 0 and @sql_count = 1
BEGIN
select @out_seq = next_seq_id
from NEXT_SEQUENCE
where seq_type = @in_seq_type
--
commit tran
return 0
END
ELSE
BEGIN
RAISERROR 44999 'Error %1! returned from proc derive_next_sequence...no update occurred', @sql_err
rollback tran
END
Optimizing your home grown Sequential key
generating process for any version of Sybase
gmeyer@netcom.com)):
1> create table keystorage 2> (tablename varchar(25), 3> lastkey int, 4> filler1 char(255) not null, 5> filler2 char(255) not null, 6> filler3 char(255) not null, 7> filler4 char(255) not null, 8> filler5 char(255) not null, 9> filler6 char(255) not null, 9> filler7 char(255) not null) 10> with fillfactor = 100 11> go
We use 7 char(255) fields to pad our small table. We also specify the fillfactor create table option to be 100. A fillfactor of 100 tells the server to completely fill every data page. Now, during your initial insertion of a line of data, do this:
1> insert into keystorage
2> (tablename,lastkey,
3> filler1,filler2,filler3,filler4,filler5,filler6,filler7)
4> values
5> ("yourtable",0,
6> replicate("x",250),replicate("x",250),
7> replicate("x",250),replicate("x",250),
8> replicate("x",250),replicate("x",250),
9> replicate("x",250))
10> go
This pads the row with 1750 bytes of junk, almost guaranteeing that, given a row's byte size limit of 1962 bytes (a row cannot span more than one page, thus the 2048 page size minus server overhead == 1962), we will be able to simulate row level locking.
1> create table keystorage 2> (tablename varchar(25), 3> lastkey int) 4> with max_rows_per_page = 1 5> go
- There is an excellent discussion located in the whitepapers section of Sybase's home page discussing the performance and tuning aspects of any type of Sequential key use. It supplements the information here (note: this page will open in a new browser window).
ASE 12 supports dynamic SQL, allowing the following:
declare @sqlstring varchar(255)
select @sqlstring = "select count(*) from master..sysobjects"
exec (@sqlstring)
go
There is a neat trick that was reported first by Bret Halford ( bret@sybase.com ). (If anyone knows better, point me to the proof and I will change this!) It utilises the CIS features of Sybase ASE.
Remember to ensure that all of the databases referred to in the SQL string are fully qualified since the call to sp_remotesql places you back in your default database.
Before System 11.5 there was no real way to execute dynamic SQL. Rob Verschoor has some very neat ideas that fills some of the gaps (http://www.euronet.nl/~syp_rob/dynsql.html).
With System 10, Sybase introduced the ability to execute a stored procedure dynamically.
declare @sqlstring varchar(255)
select @sqlstring = "sp_who"
exec @sqlstring
go
For some reason Sybase chose never to document this feature.
Obviously all of this is talking about executing dynamic SQL within the server itself ie stored procedures and triggers. Dynamic SQL within client apps is a different matter altogether.
Hey, this was quite cool I thought. It is now possible to concatenate a
series of strings to return a single column, in a sort of analogous manner
to sum summing all of the numbers in a column.
Obviously, in versions before 12.5, the longest string that you can have is
255 characters, but with very long varchars, this may prove useful to
someone.
Use a case statement, a la,
1> declare @string_var varchar(255) 2> 3> select @string_var = "" 4> 5> select @string_var = @string_var + 6> (case 1 when 1 7> then char_col 8> end) 9> from tbl_a 10> 11> print "%1!", @string_var 12> go (1 row affected) ABCDEFGH (8 rows affected) 1> select * from tbl_a 2> go char_col -------- A B C D E F G H (8 rows affected) 1>
Sybase does not have a direct equivalent to Oracle's rownum but its functionality can be emulated in a lot of cases.
If you are simply trying to retrieve the first N rows of a table, then simple use:
set rowcount
replacing <N> with your desired number of rows. (set rowcount 0 restores normality.) If it is simply the last N rows, then use a descending order-by clause in the select.
1> set rowcount2> go 1> select foo 2> from bar 3> order by barID desc 4> go
If you are trying to retrieve rows 100 to 150, say, from a table in a given order. You could use this to retrieve rows for a set of web pages, but there are probably more efficient ways using cursors or well written queries or even Sybperl! The general idea is select the rows into a temporary table adding an identity column at the same time. Only select enough rows to do the job using the rowcount trick. Finally, return the rows from the temporary table where the identity column is between 100 and 150. Something like this:
set rowcount 150
select pseudo_key = identity(3),
col1,
col2
into #tempA
from masterTable
where clause...
order by 2,3
select col1,col2 from #tempA where pseudo_key between 100 and 150
Remember to reset rowcount back to 0 before issuing any more SQL or you will only get back 150 rows!
A small optimisation would be to select only the key columns for the source table together with the identity key. Once you have the set of rows you require in the temporary table, join this back to the source using the key columns to get any data that you require.
An alternative, which might be better if you needed to join back to this table a lot, would be to insert enough rows to cover the range as before, but then delete the set of unwanted rows. This would be a very efficient mechanism if the majority of your queries involved the first few rows of a table. A typical application for this might be a search engine displaying relevant items first. The chances are that the user is going to be bored after the first couple of pages and go back to playing 'Internet Doom'.
set rowcount 150
select col1,
col2
into #tempA
from masterTable
where clause...
set rowcount 100
delete #tempA
Sybase does not guarantee to return rows in any particular order, so the delete may not delete the correct set of rows. In the above example, you should add an order-by to the 'select' and build a clustered index on a suitable key in the temporary table.
The following stored proc was posted to the Sybase-L mailing list and uses yet another mechanism. You should check that it works as expected in your environment since it relies on the fact a variable will be set using the last row that is returned from a result set. This is not published behaviour and is not guaranteed by Sybase.
CREATE PROCEDURE dbo.sp_get_posts
@perpage INT,
@pagenumber INT
WITH RECOMPILE
AS
-- if we're on the first page no need to go through the @postid push
IF @pagenumber = 1
BEGIN
SET ROWCOUNT @perpage
SELECT ...
RETURN
END
-- otherwise
DECLARE @min_postid NUMERIC( 8, 0 ),
@position INT
SELECT @position = @perpage * ( @pagenumber - 1 ) + 1
SET ROWCOUNT @position
-- What happens here is it will select through the rows
-- and order the whole set.
-- It will stop push postid into @min_postid until it hits
-- ROWCOUNT and does this out of the ordered set (a work
-- table).
SELECT @min_postid = postid
FROM post
WHERE ...
ORDER BY postid ASC
SET ROWCOUNT @perpage
-- we know where we want to go (say the 28th post in a set of 50).
SELECT ...
FROM post
WHERE postid >= @min_postid
...
ORDER BY postid ASC
Yet another solution would be to use a loop and a counter. Probably the least elegant, but again, it would depend on what you were trying to do as to what would be most appropriate.
As you can see, none of these are particularly pretty. If you know of a better method, please forward it to dowen@midsomer.org.
This question is certainly not rocket science, but it is often nice to know how many rows are returned as part of a group by. This might be for a report or a web query, where you would want to tell the user how many rows were returned on page one. It is easy using a temp table, but how to do it without a temp table is a little harder. I liked this solution and thought that it might not be obvious to everyone, it was certainly educational to me. Thanks go to Karl Jost for a very nice answer.
So, give data like:
name item ---- ---- Brown 1 Smith 2 Brown 5 Jones 7
you wish to return a result set of the form:
name sum(item) rows ---- --------- ---- Brown 6 3 Jones 7 3 Smith 2 3
rather than
name sum(item) rows ---- --------- ---- Brown 6 2 Jones 7 1 Smith 2 1
Use the following, beguilingly simple query:
select name, sum(item), sum(sign(count(*))) from data group by name