Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

combine multiple select statements into one


Hi, I have a table with two columns and the following data:

Flag    Value
f1        v1
f2        v2
f3       v3

Each flag name is unique.  And I'm executing multiple select to find
the flag value:

select @value1 = value from table1 where flag='f1'
select @value2 = value from table1 where flag='f2'
select @value3 = value from table1 where flag='f3'

Just wondering is there anyway to combine the above statements into
one select?

On Jun 5, 10:16 pm, jgong <jgong@yahoo.com> wrote:

declare @val1 varchar(10)
declare @val2 varchar(10)
select
@val1 = case flag when 'f1' then value else @val1 end,
@val2 = case flag when 'f2' then value else @val2 end

from t
select @val1,@val2

Regards
Amish Shah
http://shahamishm.tripod.com

-----------------------------------------------Reply-----------------------------------------------

You can use CASE, like this:

SELECT @value1 = CASE WHEN flag = 'f1'
                                   THEN value
                                   ELSE @value1 END,
           @value2 = CASE WHEN flag = 'f2'
                                   THEN value
                                   ELSE @value2 END,
           @value3 = CASE WHEN flag = 'f3'
                                   THEN value
                                   ELSE @value3 END
FROM table1
WHERE flag IN ('f1', 'f2', 'f3')

HTH,

Plamen Ratchev
http://www.SQLStudio.com

-----------------------------------------------Reply-----------------------------------------------

Almost.  The CASE espressions need to be inside aggretate expressions,
or the three variables will all be set from the last row processed.
Just use MAX(CASE WHEN.....) for each of them.

Here is a simple example demonstrating the problem.  The first does
not use MAX and only one variable gets assigned properly.  The second
assigns both.

declare @a nvarchar(60)
declare @b nvarchar(60)

select @a = case when id = 4 then name end,
       @b = case when id = 5 then name end
  from sysobjects
 where id IN (4,5)
select @a, @b

---------------       ------------
NULL                  sysrowsets

select @a = max(case when id = 4 then name end),
       @b = max(case when id = 5 then name end)
  from sysobjects
 where id IN (4,5)
select @a, @b

--------------------- -------------
sysrowsetcolumns      sysrowsets  

Roy Harvey
Beacon Falls, CT

On Tue, 5 Jun 2007 13:53:14 -0400, "Plamen Ratchev"

Actually not needed. As it was stated:

> Each flag name is unique.

Therefore, there will be only one row hit per value. And in your example you
missed the ELSE clause of the CASE expression, which will guarantee no NULL
gets assigned after the matching flag is hit:

SELECT @value1 = CASE WHEN flag = 'f1'
                                   THEN value
                                   ELSE @value1 END

Correct?

CREATE TABLE table1 (
  flag varchar(20) PRIMARY KEY,
  value varchar(20))

INSERT INTO table1 VALUES ('f1', 'v1')
INSERT INTO table1 VALUES ('f2', 'v2')
INSERT INTO table1 VALUES ('f3', 'v3')

DECLARE @value1 varchar(20),
        @value2 varchar(20),
        @value3 varchar(20)

SELECT @value1 = CASE WHEN flag = 'f1'
                      THEN value
                      ELSE @value1 END,
       @value2 = CASE WHEN flag = 'f2'
                      THEN value
                      ELSE @value2 END,
       @value3 = CASE WHEN flag = 'f3'
                      THEN value
                      ELSE @value3 END
FROM table1
WHERE flag IN ('f1', 'f2', 'f3')

SELECT @value1, @value2, @value3

DROP TABLE table1

Plamen Ratchev
http://www.SQLStudio.com

-----------------------------------------------Reply-----------------------------------------------

On Tue, 5 Jun 2007 15:11:27 -0400, "Plamen Ratchev"

<Pla@SQLStudio.com> wrote:
>Actually not needed. As it was stated:

>> Each flag name is unique.

>Therefore, there will be only one row hit per value. And in your example you
>missed the ELSE clause of the CASE expression, which will guarantee no NULL
>gets assigned after the matching flag is hit:

>SELECT @value1 = CASE WHEN flag = 'f1'
>                                   THEN value
>                                   ELSE @value1 END

>Correct?

In fact running a simple test does confirm the behavior you describe.

However, I am not aware that this is a documented behavior.  If it is
not documented then it is subject to change any time a new release
comes out, or even just a new service pack.  Likewise, if it is not
documented I would not count on the simple test I ran to confirm the
behavior holding true for more complex cases involving parallel
queries and other complexities the optimizer might deal with.

But if it is documented I would love to be pointed to it, it would be
a very handy trick!

Roy Harvey
Beacon Falls, CT

-----------------------------------------------Reply-----------------------------------------------

No, I have not seen an official reference to this behavior. :)

I have observed this in both SQL Server 2000 and SQL Server 2005, but it
could very well be undocumented.

It is somehow similar to the trick with assigning a sequential number on
UPDATE, based on the syntax:
UPDATE Table
SET @variable = column = <expression>

Where the <expression> contains reference to a calculation based on
@variable.

Similar with SELECT the CASE seems to evaluate for the matching row to the
column value and for the rest will assign the variable itself. That way the
last row in the result set contains the value of the matching flag, which
then gets assigned to the variable.

Plamen Ratchev
http://www.SQLStudio.com

-----------------------------------------------Reply-----------------------------------------------

 This behavior has been around since server 7 and is documented
 as only MS can. As Plamen points out it's a manifestation of the
 update behavior of variable assignment. If your smart you can
 do some interesting things with it as is done in Rac.
 The if it's documented then it exists argument is tenuous.
 Parameter sniffing has be around forever yet was not documented
 in bol till S2005. But I'm sure that was just an oversight in
 a big company.
 Interestingly, MySql extended the idea by allowing both variables
 and columns to co-exist in a query.
 Conceptually the idea of returning multiple scalars in a single
 line is a 'row', a term that would turn a trick into a construct.

 www.beyondsql.blogspot.com

"Roy Harvey" <roy_har@snet.net> wrote in message

news:nmdb639f15u44taasajabc3u1bms807dft@4ax.com...

On Tue, 5 Jun 2007 14:15:46 -0700, "Steve Dassin"

<s@nospamrac4sql.net> wrote:
> This behavior.... is documented as only MS can.

Can you please explain that so even I can understand it?   8-)

Roy Harvey
Beacon Falls, CT

-----------------------------------------------Reply-----------------------------------------------

You could possibly infer this behavior between Update and
SELECT @local_variable (Transact-SQL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/8e1a9387-2c5d-4e51-a1fd -
a2a95f026d6f.htm

It's nothing more than a loop, bounded by select and from.
Or a foreach (row) in any programming language. Your computing
over the rows in from just like over a table in Update.

I have long given up the document peek-a-boo game. If you
find something interesting that works beat it to death. You can
be sure people at MS use this internally for god knows what.
Perhaps they don't want to wave it so they don't have to
explain it. Look how they butchered Apply. Something interesting -:)

Here is an example from Rac. We haven't had a complaint yet -:)

create procedure racreplplus            /* sp racreplplus replace expr. +
pvtcols first (+) */
-- Sp for global replacement and adding of pivot column first (+)
   @rotatefieldspvtselect varchar(7900),  /* (not used here) pvtcols */
   @rotatefieldspvtselect1 varchar(7900), /* pvtcol name without
brackets,reference with '?' */
   @replacepvtcols varchar(2500),         /* not used here */
   @racparameterdelimitor varchar(20),    /* (not used here) delimitor for
@replacepvtcols */
    @alias varchar(500),                  /* not used here */
    @rterm varchar(1500),                  /* @replacepvtcols expression */
     @rname varchar(1000),                 /* @replacepvtcols name */
     @separator varchar(500)=',',          /* not used here */
      @rd int output,                     /* rd (pvtcol #) in
@rotatefieldspvtselect string */
       @num int=7900,                     /* maximum length of string @pvtwk
can hold */
        @pvtwk varchar(7950) output       /* holds pvtcols and expressios */
  with encryption
  as
  --
  declare @compfields varchar(7950)
  set @compfields=''
  --                  New query expression
  --
  select
  @pvtwk=case when len(@pvtwk+case when rd>1 then ',' else ''
end+'['+pivotin+']'+','+
  replace(replace(@rterm,'[*]','['+pivotin+']'),'?',pivotin) +' as
['+pivotin+@rname+']')
    <@num  and (rd-@rd=1)
    then
        @pvtwk+case when rd>1 then ',' else '' end+'['+pivotin+']'+','+
  replace(replace(@rterm,'[*]','['+pivotin+']'),'?',pivotin)+' as
['+pivotin+@rname+']'
              else @pvtwk end,
  @rd=case when len(@pvtwk)>len(@compfields) then rd else @rd end,
  @compfields=@pvtwk
  from racpovitin# (@rotatefieldspvtselect1,'&')
       where rd>@rd
          order by rd

www.rac4sql.net
www.beyondsql.blogspot.com

"Roy Harvey" <roy_har@snet.net> wrote in message

news:tisb63hruivr1c4tq3t6g6n8mqhdekc4q5@4ax.com...

On Wed, 6 Jun 2007 02:24:31 -0700, "Steve Dassin" <rac4sqlnospam@net>
wrote:

>I have long given up the document peek-a-boo game. If you
>find something interesting that works beat it to death.

Yes, I've done that.  Back in my mainframe days there were certain
behaviors of PL/1 that I exploited in just about every program I
wrote, and I had the entire shop following my lead.  Eventually I
realized that the behaviors were undocumented, and if it had changed
just about every program in the shop would have stopped working.  I
got away with it that time, but I've become a bit more careful since
then.

Roy Harvey
Beacon Falls, CT

-----------------------------------------------Reply-----------------------------------------------

Thanks everyone, it sure works and looks smart!

But other than looking smart, just wondering what's the value of using
the select...case?  The flag table is a temp table and has max 3-5
rows.  Writing 3-5 select statements is acceptable for me, and it's
simple and readable.  The select...case has similar or more lines of
code, but not very easy to understand and maintain.  It has a
performance advantage that everything is done in one select.  But
based on 80-20 rule, this little performance gain may not even matter
if it's not a bottleneck.

Correct me if my thoughts are wrong.

-----------------------------------------------Reply-----------------------------------------------

On Jun 6, 10:19 am, Roy Harvey <roy_har@snet.net> wrote:

> On Wed, 6 Jun 2007 02:24:31 -0700, "Steve Dassin" <rac4sqlnospam@net>
> wrote:

> >I have long given up the document peek-a-boo game. If you
> >find something interesting that works beat it to death.

> Yes, I've done that.  Back in my mainframe days there were certain
> behaviors of PL/1 that I exploited in just about every program I
> wrote, and I had the entire shop following my lead.  Eventually I
> realized that the behaviors were undocumented, and if it had changed
> just about every program in the shop would have stopped working.  I
> got away with it that time, but I've become a bit more careful since
> then.

Agreed. I used to depend on an even shakier version of this behavior
to number a table:

DECLARE @Counter int

SET @Counter=1

UPDATE TheTable SET CounterCol=@Counter, @CounterCol=@CounterCol+1

Worked pretty dependably in 6.5.  Then came 2000 with a better
optimizer and things started evaluating in weird orders.  Blew a bunch
of stored procedures to hell and gone.

-----------------------------------------------Reply-----------------------------------------------

You can PIVOT in SQL2005:

DROP TABLE #tmp
GO
CREATE TABLE #tmp ( flag CHAR(2), xvalue CHAR(2) )
GO

INSERT INTO #tmp VALUES( 'f1', 'v1' )
INSERT INTO #tmp VALUES( 'f2', 'v2' )
INSERT INTO #tmp VALUES( 'f3', 'v3' )
GO

DECLARE @value1 CHAR(2), @value2 CHAR(2), @value3 CHAR(2)

SELECT @value1 = f1, @value2 = f2, @value3 = f3
FROM #tmp
PIVOT( MAX(xvalue) For flag In ( [f1], [f2], [f3] ) ) pvt

SELECT @value1, @value2, @value3

wBob

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc