Become a Member | Login | Forgot Password?   Advertise With ISUG | Partner With ISUG
International Sybase User Group HeaderInternational Sybase User Group Header
Home
Search isug.com
Google Custom Search

Resources — X

General:

Tools:

Miscellaneous:

External Links:

ISUG Partner
Novalys Website

SQL Advanced

 

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?

Useful SQL Tricks SQL Fundamentals ASE FAQ

6.2.1: How to emulate the Oracle decode function/crosstab

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

Back to top

6.2.2: How to implement if-then-else in a select clause

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.

Back to top

6.2.3: Removed

6.2.4: How to pad with leading zeros an int or smallint.

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

Back to top

6.2.5: Divide by zero and nulls

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

Back to top

6.2.6: Convert months to financial months

To convert months to financial year months (i.e. July = 1, Dec = 6, Jan = 7, June = 12 )

Method #1

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 ...

Method #2

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.

Back to top

6.2.7: Hierarchy traversal - BOMs

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.

  1. By leaving huge gaps in the left & right numbers (using the @skip variable), you can reduce the circumstances in which the numbers need to be reassigned for a given insert. Thus, as long as you can squeeze a new node between an existing pair of left and right numbers you don't need to do the re-assignment (which could affect all of the node in the hierarchy).
  2. By keeping an extra flag around in the hier table to indicate which nodes are leaf nodes (this could be maintained with a trigger as well), you avoid placing leaf nodes in the array and thus reduce the number of updates.

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.

Back to top

6.2.8: Calling OS commands from a trigger or a stored procedure

11.5 and above

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.

Pre-11.5

Periodically folks ask if it's possible to make a system command or call a UNIX process from a Trigger or a Stored Procedure.

Guaranteed Message Processing

The typical ways people have implemented this capability is:

  1. Buy Open Server and bind in your own custom stuff (calls to system() or custom C code) and make Sybase RPC calls to it.
  2. Have a dedicated client application running on the server box which regularly scans a table and executes the commands written into it (and tucks the results into another table which can have a trigger on it to gather results...). It is somewhat tricky but cheaper than option 1.

Sybase ASE 10.0.2.5 and Above - syb_sendmsg()

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. Login to the ASE using 'isql'.
  2. Enable the syb_sendmsg() feature using sp_configure. 
    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.

Using syb_sendmsg() with Existing Scripts

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.)

Backing Out syb_sendmsg()

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.

  1. Edit the RUNSERVER file to point to the SWR ASE binary you wish to use.
  2. isql -Usa -P<sa password> -Sserver_name -n -iunconfig.sendmsg -ooutput_file

Sample C program

#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);
    }
}

Back to top

6.2.9: Identities and Sequential Keys

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:

Back to start of 6.2.9

Disadvantages/Drawbacks of Using Identities

Despite its efficacy of use, the Identity has some drawbacks:

Back to start of 6.2.9

Common questions about Identities

 Is the Identity the equivalent of Oracle's auto-sequencing?:

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.

Back to start of 6.2.9

Back to top

6.2.10: How can I execute dynamic SQL with ASE?

Adaptive Server Enterprise: System 12

ASE 12 supports dynamic SQL, allowing the following:

declare @sqlstring varchar(255)
select @sqlstring = "select count(*) from master..sysobjects"
exec (@sqlstring)
go

Adaptive Server Enterprise: 11.5 and 11.9

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.

  • Firstly define your local server to be a remote server using
    sp_addserver LOCALSRV,sql_server[,INTERFACENAME]
    go
  • Enable CIS
    sp_configure "enable cis",1
    go
  • Finally, use sp_remotesql, sending the sql to the server defined in point 1.
    declare @sqlstring varchar(255)
    select @sqlstring = "select count(*) from master..sysobjects"
    sp_remotesql LOCALSRV,@sqlstring
    go

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.

 

Sybase ASE (4.9.x, 10.x and 11.x before 11.5)

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).

Dynamic Stored Procedure Execution

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.

Back to top

6.2.11: Is it possible to concatenate all the values from a column and return a single row?

It was 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.  However, this was not a 'feature' but a bug, so if you are running an EBF that has the fix for CR 210688, this is no longer possible.

(Obsolete:) 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>

Back to top

6.2.12: Selecting rows N to M without Oracle's rownum?

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 rowcount 
2> 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.

Back to top

6.2.13: How can I return number of rows that are returned from a grouped query without using a temporary table?

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

Back to top

Useful SQL Tricks SQL Fundamentals ASE FAQ

Quick Links – Sybase FAQ
Advertisements — X