reuse subquery results in where clause (newbie)

How can I reuse the result of a subquery in the same query where-clause
without having to duplicate the subquery itself? Say I have a query like:
select a,b
(select count(c) from MyOtherTable where MyOtherTable.d = a) as
someCount
from MyTable
order by someCount
and I want to filter the rows by someCount: if I add a clause like:
where someCount > 5
I get a syntax error (invalid column name). How can I refer to the
calculated field someCount without repeating the subquery as a whole?
Thanks!Dan
select <column lists> from
(
select a,b
(select count(c) from MyOtherTable where MyOtherTable.d = a) as
someCount
from MyTable
) as Der
where someCount >5
order by someCount
"Dan" <fusid@.iol.it> wrote in message
news:epI8jZRDFHA.2232@.TK2MSFTNGP14.phx.gbl...
> How can I reuse the result of a subquery in the same query where-clause
> without having to duplicate the subquery itself? Say I have a query like:
> select a,b
> (select count(c) from MyOtherTable where MyOtherTable.d = a) as
> someCount
> from MyTable
> order by someCount
> and I want to filter the rows by someCount: if I add a clause like:
> where someCount > 5
> I get a syntax error (invalid column name). How can I refer to the
> calculated field someCount without repeating the subquery as a whole?
> Thanks!
>|||Dan wrote:
> How can I reuse the result of a subquery in the same query
> where-clause without having to duplicate the subquery itself? Say I
> have a query like:
> select a,b
> (select count(c) from MyOtherTable where MyOtherTable.d = a) as
> someCount
> from MyTable
> order by someCount
> and I want to filter the rows by someCount: if I add a clause like:
> where someCount > 5
> I get a syntax error (invalid column name). How can I refer to the
> calculated field someCount without repeating the subquery as a whole?
>
Move the subquery to your FROM clause:
select a,b, someCount
from MyTable t inner join
(select d, count(c) someCount from MyOtherTable
where MyOtherTable GROUP BY d) q
ON t.a = q.d
order by someCount
You may find this enlightening:
http://groups-beta.google.com/group...09662
c8
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

returning multiple values in a subquery

I have a report that I've looked at with regards to a crosstab query -
which doesn't really give me what I need.
I have to produce a report where I provide some customer information,
and the channels in which they purchase items from us. A customer can
purchase items under several different channels, and what I need to do
is show a list of them.
The desired report would be something like:
Cust No Cust Name BA01 BA02 BA03
12345 CUST01 WF WA,WF WF
12333 CUST02 DL WA DL
12205 CUST03 N/A WA N/A
66412 CUST04 CH CH CH
What I was doing before (trying to get dollar totals in those columns) I
found I didn't have to do. However, I do have to return multiple values
into those columns, and thought I could do it with a subquery. Error
messages have led me to find I cannot - at least now how I was figuring.
The rows are just a straight select, and the columns BA01, BA02, BA03
are where I have the subselects. It's select distr_channel from
source.table where [all required fields match each other] group by
distr_channel). It's in a case statement to put in the distr_channel or
an N/A when there's no match.
How can I return these multiple values into columns BA01, BA02 and BA03
when necessary?
BCEnjoy!
create table Customer (customerno int identity(1, 1), customername
varchar(20))
create table Attributes1 (customerno int, attribute varchar(10))
create table Attributes2 (customerno int, attribute varchar(10))
insert into customer (customername) values('Customer A')
insert into customer (customername) values('Customer B')
insert into customer (customername) values('Customer C')
insert into customer (customername) values('Customer D')
insert into attributes1 (customerno, attribute) values(1, 'WF')
insert into attributes1 (customerno, attribute) values(1, 'DL')
insert into attributes1 (customerno, attribute) values(2, 'WF')
insert into attributes1 (customerno, attribute) values(3, 'WF')
insert into attributes1 (customerno, attribute) values(5, 'DL')
insert into attributes1 (customerno, attribute) values(5, 'WF')
insert into attributes2 (customerno, attribute) values(1, 'DL')
insert into attributes2 (customerno, attribute) values(1, 'WF')
insert into attributes2 (customerno, attribute) values(2, 'DL')
insert into attributes2 (customerno, attribute) values(3, 'DL')
insert into attributes2 (customerno, attribute) values(4, 'DL')
insert into attributes2 (customerno, attribute) values(4, 'WF')
if object_id('dbo.GetAttributesList') > 0
drop function dbo.AttributesList
go
create function dbo.AttributesList (@.customerno int, @.attr int)
returns varchar(1000)
as
begin
declare @.sOut varchar(1000)
set @.sOut = ''
if @.attr = 1
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + a.attribute
from attributes1 a
where a.customerno = @.customerno
end
if @.attr = 2
begin
-- attribute table 2
-- attribute table 1
select @.sOut = @.sOut + ', ' + a.attribute
from attributes2 a
where a.customerno = @.customerno
end
if len(@.sOut) > 2
set @.sOut = substring(@.sOut, 3, len(@.sOut) - 2)
return @.sOut
end
go
select c.customername, dbo.AttributesList(c.customerno, 1),
dbo.AttributesList(c.customerno, 2)
from customer c
go
"Blasting Cap" wrote:

> I have a report that I've looked at with regards to a crosstab query -
> which doesn't really give me what I need.
> I have to produce a report where I provide some customer information,
> and the channels in which they purchase items from us. A customer can
> purchase items under several different channels, and what I need to do
> is show a list of them.
> The desired report would be something like:
> Cust No Cust Name BA01 BA02 BA03
> 12345 CUST01 WF WA,WF WF
> 12333 CUST02 DL WA DL
> 12205 CUST03 N/A WA N/A
> 66412 CUST04 CH CH CH
>
> What I was doing before (trying to get dollar totals in those columns) I
> found I didn't have to do. However, I do have to return multiple values
> into those columns, and thought I could do it with a subquery. Error
> messages have led me to find I cannot - at least now how I was figuring.
> The rows are just a straight select, and the columns BA01, BA02, BA03
> are where I have the subselects. It's select distr_channel from
> source.table where [all required fields match each other] group by
> distr_channel). It's in a case statement to put in the distr_channel or
> an N/A when there's no match.
> How can I return these multiple values into columns BA01, BA02 and BA03
> when necessary?
> BC
>|||Cris:
Thanks for the help. It's so simple to see what it does.
I'm adapting it for my application now.
One question - and it may be that I'm just missing something. My
customer number is a varchar field (it can have letters or numbers in
it), and so is the attribute field.
In the function, I've changed both the customerno and attr to a varchar,
created the attribute tables based on whether they were in busunit01
02 or whatever, and call it with a customer I know is in the fa_channels
table (it has 2 entries a CH and PR).
I'm calling it (testing) like this:
select c.cust_no, c.cust_name, fa = dbo.AttributesList('R0461275', '1'),
am = dbo.AttributesList(c.cust_no, '2')
from sales_customer_list c
where c.cust_no = 'R0461275'
group by
c.cust_no, c.cust_name
The result of it gives a custno & cust_name, but the fa from above
returns blank.
Only when I change the statement below from:
where x.cust_no = @.customerno
to
where x.cust_no = 'R0461275'
will it return the 2 channels it should.
The only thing I know is that the cust_no in the sales_customer_list is
a char(10), and in my fa_channels table it's varchar(10).
Am I missing something?
BC
create function dbo.AttributesList (@.customerno varchar, @.attr varchar)
returns varchar(1000)
as
begin
declare @.sOut varchar(1000)
set @.sOut = ''
if @.attr = '1'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from fa_channels x
where x.cust_no = @.customerno
end
if @.attr = '2'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from am_channels y
where y.cust_no = @.customerno
end
Cris_Benge wrote:
> Enjoy!
> create table Customer (customerno int identity(1, 1), customername
> varchar(20))
> create table Attributes1 (customerno int, attribute varchar(10))
> create table Attributes2 (customerno int, attribute varchar(10))
> insert into customer (customername) values('Customer A')
> insert into customer (customername) values('Customer B')
> insert into customer (customername) values('Customer C')
> insert into customer (customername) values('Customer D')
> insert into attributes1 (customerno, attribute) values(1, 'WF')
> insert into attributes1 (customerno, attribute) values(1, 'DL')
> insert into attributes1 (customerno, attribute) values(2, 'WF')
> insert into attributes1 (customerno, attribute) values(3, 'WF')
> insert into attributes1 (customerno, attribute) values(5, 'DL')
> insert into attributes1 (customerno, attribute) values(5, 'WF')
> insert into attributes2 (customerno, attribute) values(1, 'DL')
> insert into attributes2 (customerno, attribute) values(1, 'WF')
> insert into attributes2 (customerno, attribute) values(2, 'DL')
> insert into attributes2 (customerno, attribute) values(3, 'DL')
> insert into attributes2 (customerno, attribute) values(4, 'DL')
> insert into attributes2 (customerno, attribute) values(4, 'WF')
> if object_id('dbo.GetAttributesList') > 0
> drop function dbo.AttributesList
> go
> create function dbo.AttributesList (@.customerno int, @.attr int)
> returns varchar(1000)
> as
> begin
> declare @.sOut varchar(1000)
> set @.sOut = ''
> if @.attr = 1
> begin
> -- attribute table 1
> select @.sOut = @.sOut + ', ' + a.attribute
> from attributes1 a
> where a.customerno = @.customerno
> end
> if @.attr = 2
> begin
> -- attribute table 2
> -- attribute table 1
> select @.sOut = @.sOut + ', ' + a.attribute
> from attributes2 a
> where a.customerno = @.customerno
> end
> if len(@.sOut) > 2
> set @.sOut = substring(@.sOut, 3, len(@.sOut) - 2)
> return @.sOut
> end
> go
> select c.customername, dbo.AttributesList(c.customerno, 1),
> dbo.AttributesList(c.customerno, 2)
> from customer c
> go
>
> "Blasting Cap" wrote:
>|||Never mind!!!
I tried one other thing I hadn't before - I gave the varchar in the
function the same length as the input, and it worked!!
Thanks again for the very simple solution to my problem.
BC
Blasting Cap wrote:
> Cris:
> Thanks for the help. It's so simple to see what it does.
> I'm adapting it for my application now.
> One question - and it may be that I'm just missing something. My
> customer number is a varchar field (it can have letters or numbers in
> it), and so is the attribute field.
> In the function, I've changed both the customerno and attr to a varchar,
> created the attribute tables based on whether they were in busunit01 02
> or whatever, and call it with a customer I know is in the fa_channels
> table (it has 2 entries a CH and PR).
> I'm calling it (testing) like this:
> select c.cust_no, c.cust_name, fa = dbo.AttributesList('R0461275',
> '1'), am = dbo.AttributesList(c.cust_no, '2')
> from sales_customer_list c
> where c.cust_no = 'R0461275'
> group by
> c.cust_no, c.cust_name
>
> The result of it gives a custno & cust_name, but the fa from above
> returns blank.
> Only when I change the statement below from:
> where x.cust_no = @.customerno
> to
> where x.cust_no = 'R0461275'
> will it return the 2 channels it should.
> The only thing I know is that the cust_no in the sales_customer_list is
> a char(10), and in my fa_channels table it's varchar(10).
> Am I missing something?
> BC
> create function dbo.AttributesList (@.customerno varchar, @.attr varchar)
> returns varchar(1000)
> as
> begin
> declare @.sOut varchar(1000)
> set @.sOut = ''
> if @.attr = '1'
> begin
> -- attribute table 1
> select @.sOut = @.sOut + ', ' + x.distr_channel
> from fa_channels x
> where x.cust_no = @.customerno
> end
> if @.attr = '2'
> begin
> -- attribute table 2
> -- attribute table 2
> select @.sOut = @.sOut + ', ' + y.distr_channel
> from am_channels y
> where y.cust_no = @.customerno
> end
> Cris_Benge wrote:
>