Sybase select variable logic
Ok, I have a question relating to an issue I've previously had. I know how to fix it, but we are having problems trying to reproduce the error.


We have a series of procedures that create records based on other records. The records are linked to the primary record by way of a `link_id`. In a procedure that grabs this `link_id`, the query is

    select @p_link_id = id --of the parent
    from  table
    where thingy_id = (blah)

Now, there are multiple rows in the table for the activity. Some can be cancelled. The code I have doesn't disinclude cancelled rows in the select statement, so if there are previously cancelled rows, those ids will appear in the select. There is always going to be one 'open' record that is selected if I disinclude cancelled rows. (append `where status != 'C'`)

This solves this issue. However, I need to be able to reproduce the issue in our development environment.

I've gone through a process where I've entered a whole heap of data, opening, cancelling, etc to try and get this select statement to return an invalid id. However, whenever I run the select, the ids are in order (sequence generated), but in the case where this error occured, the select statement returned what seems to be the first value into the variable.

For example.

    ID   Status
    1    Cancelled
    2    Cancelled
    3    Cancelled
    4    Open

Given the above, if I do a select for the ID I want, I want to get '4'. In the error, the result is 1. However, even if I enter in 10 cancelled records, I still get the last one in the select.

In oracle, I know that if you select into a variable and more than one record is returned, you get an error (I think). Sybase apparently can assign multiple values into a variable without erroring.

I'm thinking that there's either something to do with how the data is selected from the table, where the id's without a sort order don't return in ascending order, or there's a dboption where a select into a variable will save the first or last value queried.
Sybase select variable logic
===

Ok, I have a question relating to an issue I've previously had. I know how to fix it, but we are having problems trying to reproduce the error.


We have a series of procedures that create records based on other records. The records are linked to the primary record by way of a `link_id`. In a procedure that grabs this `link_id`, the query is

    select @p_link_id = id --of the parent
    from  table
    where thingy_id = (blah)

Now, there are multiple rows in the table for the activity. Some can be cancelled. The code I have doesn't disinclude cancelled rows in the select statement, so if there are previously cancelled rows, those ids will appear in the select. There is always going to be one 'open' record that is selected if I disinclude cancelled rows. (append `where status != 'C'`)

This solves this issue. However, I need to be able to reproduce the issue in our development environment.

I've gone through a process where I've entered a whole heap of data, opening, cancelling, etc to try and get this select statement to return an invalid id. However, whenever I run the select, the ids are in order (sequence generated), but in the case where this error occured, the select statement returned what seems to be the first value into the variable.

For example.

    ID   Status
    1    Cancelled
    2    Cancelled
    3    Cancelled
    4    Open

Given the above, if I do a select for the ID I want, I want to get '4'. In the error, the result is 1. However, even if I enter in 10 cancelled records, I still get the last one in the select.

In oracle, I know that if you select into a variable and more than one record is returned, you get an error (I think). Sybase apparently can assign multiple values into a variable without erroring.

I'm thinking that there's either something to do with how the data is selected from the table, where the id's without a sort order don't return in ascending order, or there's a dboption where a select into a variable will save the first or last value queried.