Row level selects 
Author Message
 Row level selects


Quote:


> > I'm trying to create a view on a table selecting one of two fields depending on the
> > value of a third e.g.
> >   . . .

> >    select field1 from table where field3 = 1
> >     union
> >    select field2 from table where field3 = 2

> [Second time this week!  Who's gonna keep the FAQ?  Any volunteers?]

> Don't think it's possible to do this in Sybase (see previous post).  

> An alternative way, which also doesn't work, is one of my favorite
> "can't-do-its":

> select
>   (select field1 from table where field3 = 1 and id = T.id
>     union
>    select field2 from table where field3 = 2 and id = T.id)
>  from table T

>   . . . the "if function".

> This doesn't work because Sybase also doesn't allow subqueries to contain
> unions.

> Both of the above constructs work in VAX/Rdb.

> --

> Tom Warfield                   Database Administrator/UUCP Postmaster
> vnunet!twarfield               Claritas/NPDC Inc., Alexandria, VA

It is actually possible to perform this without the use of unions. The actual inspiration for this comes from SQL Forum magazine which has been running a series of articles on what they term characteristic functions. These can be used to give similar functionality to Oracles decode instruction (apparently).

If field1 & field2 are integers then this will work

SELECT ISNULL(field1/(1-ABS(SIGN(field3-1))),
       ISNULL(field2/(1-ABS(SIGN(field3-2))),<other value>))
FROM table

The function (1-ABS(SIGN(x - y))) is fuction which will return 1 if x = y and 0 otherwise. Division by zero returns NULL hence the argument to the ISNULL function is returned.

Denoting (1-ABS(SIGN(x-y))) as eqfn(x,y).

The solution where the fields are characters is more complicated.

SELECT SUBSTRING(field1+field2,
                ISNULL(1/eqfn(field3,1),datalength(field1)+1),
                ISNULL(datalength(field1)/eqfn(field3,1),datalength(field2)))
FROM table      

only this will return field1 if field3 is 1 and field2 otherwise. It can be modified to return a different value if field3 is not 1 or 2.


This idea can be used to reduce the number of times a query has to scan a table and can greatly improves performance over equivalent union statements (if at the expense of some clarity).



Fri, 29 Dec 1995 16:41:53 GMT
 Row level selects


[ ... Stuff deleted ]

Quote:

>SELECT ISNULL(field1/(1-ABS(SIGN(field3-1))),
>       ISNULL(field2/(1-ABS(SIGN(field3-2))),<other value>))
>FROM table

>The function (1-ABS(SIGN(x - y))) is fuction which will return 1 if x = y and 0 otherwise. Division by zero returns NULL hence the argument to the ISNULL function is returned.

>Denoting (1-ABS(SIGN(x-y))) as eqfn(x,y).

>The solution where the fields are characters is more complicated.

>SELECT SUBSTRING(field1+field2,
>            ISNULL(1/eqfn(field3,1),datalength(field1)+1),
>            ISNULL(datalength(field1)/eqfn(field3,1),datalength(field2)))
>FROM table  

>only this will return field1 if field3 is 1 and field2 otherwise. It can be modified to return a different value if field3 is not 1 or 2.


>This idea can be used to reduce the number of times a query has to scan a table and can greatly improves performance over equivalent union statements (if at the expense of some clarity).

Tony's solution is brilliantly innovative!


variable set to 0 standard ansi behaviour? Is it likely to change in future versions of the product? Could the experts oblige and enlighten?

Thanks in advance,

Mahesh

--
Mahesh Panjwani  
x (201) 896-7850
#include ___std_disclaimer___

"Remember, even if you win the rat race, you're still a rat."



Sun, 31 Dec 1995 00:14:25 GMT
 Row level selects


Quote:



>> > I'm trying to create a view on a table selecting one of two
>> > fields depending on the value of a third e.g.
>> >   . . .

>> >    select field1 from table where field3 = 1
>> >     union
>> >    select field2 from table where field3 = 2

>> Don't think it's possible to do this in Sybase (see previous post).  

> It is actually possible to perform this without the use of unions.
> The actual inspiration for this comes from SQL Forum magazine . . .

I'm gonna subscribe.

Quote:

> If field1 & field2 are integers then this will work

> SELECT ISNULL(field1/(1-ABS(SIGN(field3-1))),
>        ISNULL(field2/(1-ABS(SIGN(field3-2))),<other value>))
> FROM table

This is a terrific and clever idea.  I wish I
had thought of it!  Thanks, Tony.

Here's a variation that returns whichever of two fields is larger:

SELECT ISNULL(field1*2/(ABS(SIGN(field2-field1))-sign(field2-field1)),
       ISNULL(field2*2/(ABS(SIGN(field1-field2))-sign(field1-field2)),
       field1))
FROM table

(warning - doesn't work with tinyint)

Quote:
> Allthough this use division by zero to get a NULL value and an error will be
> displayed in SQL, this does not affect the results of the query.

You can use SET ARITHIGNORE ON to suppress the error message.

Quote:

> by zero error is returned, to an open client app, as if it was an SQL
> print statement.

I've found that MS Access (v1.0), using ODBC, doesn't give you any
results if there is a divide by zero.  I couldn't figure out how to
do SET ARITHIGNORE ON from within MS Access.  Tsk.

Quote:
> This idea can be used to reduce the number of times a query has to
> scan a table and can greatly improves performance over equivalent
> union statements (if at the expense of some clarity).

How about user-defined functions, Sybase?

--

Tom Warfield                   Database Administrator/UUCP Postmaster
vnunet!twarfield               Claritas/NPDC Inc., Alexandria, VA



Sun, 31 Dec 1995 22:35:36 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. question regarding select (multiple rows select into one result row)

2. row-level or page-level locking?

3. table-level lock or row-level lock

4. Trigger - Row level or Transaction level insert

5. row level vs page level locking is it more than marketing

6. Selecting a row by it's row number

7. Q: sql select where criteria for each row is not wholly contained in the row

8. select case return rows in one row (how to group by three column)

9. Select rows where other related rows don't exist

10. Updating a Table by selecting row by row

11. Get total number of rows while only selecting top X rows

12. Select Rows After first 20 Rows


 
Powered by phpBB® Forum Software