T-SQL

13 Sep 2006 11:23 PM

CipherTeKST

Having trouble using multiple subqueries to pull data from 3 DB's. What I
need in the end is the AssocName, Amount, Total1, Total2, Total3...

Here is the query...

USE DataBase1

SELECT DataBase1.dbo.Users.Name AS AssocName, Count(*) AS Amount,

(SELECT AgentFK, count(*) from DataBase2.dbo.Results with (nolock)
where CallStartTime >= '9/13/2006'
group by AgentFK)
AS Total1,

(SELECT FK_TsrId, count(*) from DataBase3.dbo.transactions with (nolock)
where WeekEnding = '9/16/2006'
and status IN ('CONFIRMED', 'QUALIFIED', 'PRINTED', 'NEW', 'CANCELED')
Group by FK_TsrId)
AS Total2,

(SELECT AgentFK, count(*) from DataBase2.dbo.Results with (nolock)
where CallStartTime >= '9/11/2006'
group by AgentFK)
AS Total3

from DataBase1.dbo.Users with (nolock)

INNER JOIN DataBase3.dbo.transactions with (nolock)
ON DataBase1.dbo.Users.PKUsers = DataBase3.dbo.transactions.FK_TsrId

Where (DataBase3.dbo.transactions.status IN ('CONFIRMED', 'QUALIFIED',
'PRINTED', 'NEW', 'CANCELED'))

and (DataBase3.dbo.transactions.transactiondate >= '9/13/2006')

Group By DataBase1.dbo.Users.Name

Order By DataBase1.dbo.Users.Name

The error I keep getting is...

Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.

Any help will be greatly appreciated.

--
CipherTeKST
MCSE: Security 2003, CCNA, Security+

14 Sep 2006 12:08 AM

Arnie Rowland

The Sub-Selects are returning 2 values and trying to stuff them in one
field. That won't work.

Remove the AgentFK, FK_TsrId from each of the sub-select statements.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"CipherTeKST" <CipherTe***@discussions.microsoft.com> wrote in message
news:EE39A181-BD5B-4723-87B5-FCECEA7D2EA9@microsoft.com...
> Having trouble using multiple subqueries to pull data from 3 DB's. What I
> need in the end is the AssocName, Amount, Total1, Total2, Total3...
>
> Here is the query...
>
> USE DataBase1
>
> SELECT DataBase1.dbo.Users.Name AS AssocName, Count(*) AS Amount,
>
> (SELECT AgentFK, count(*) from DataBase2.dbo.Results with (nolock)
> where CallStartTime >= '9/13/2006'
> group by AgentFK)
> AS Total1,
>
> (SELECT FK_TsrId, count(*) from DataBase3.dbo.transactions with (nolock)
> where WeekEnding = '9/16/2006'
> and status IN ('CONFIRMED', 'QUALIFIED', 'PRINTED', 'NEW', 'CANCELED')
> Group by FK_TsrId)
> AS Total2,
>
> (SELECT AgentFK, count(*) from DataBase2.dbo.Results with (nolock)
> where CallStartTime >= '9/11/2006'
> group by AgentFK)
> AS Total3
>
> from DataBase1.dbo.Users with (nolock)
>
> INNER JOIN DataBase3.dbo.transactions with (nolock)
> ON DataBase1.dbo.Users.PKUsers = DataBase3.dbo.transactions.FK_TsrId
>
> Where (DataBase3.dbo.transactions.status IN ('CONFIRMED', 'QUALIFIED',
> 'PRINTED', 'NEW', 'CANCELED'))
>
>
> and (DataBase3.dbo.transactions.transactiondate >= '9/13/2006')
>
> Group By DataBase1.dbo.Users.Name
>
> Order By DataBase1.dbo.Users.Name
>
> The error I keep getting is...
>
> Only one expression can be specified in the select list when the subquery
> is
> not introduced with EXISTS.
>
> Any help will be greatly appreciated.
> --
> CipherTeKST
> MCSE: Security 2003, CCNA, Security+

14 Sep 2006 7:44 AM

John Bell

Hi

I would expect some correlation between the AgentFK and FK_TrsId which would
mean that you don't need to use a subquery but you could use a derived table
or even (outer) join them directly. If not you may want to move the
subqueries out of this and evaluate the value separately.

John

Show quote

"CipherTeKST" wrote:

> Having trouble using multiple subqueries to pull data from 3 DB's. What I
> need in the end is the AssocName, Amount, Total1, Total2, Total3...
>
> Here is the query...
>
> USE DataBase1
>
> SELECT DataBase1.dbo.Users.Name AS AssocName, Count(*) AS Amount,
>
> (SELECT AgentFK, count(*) from DataBase2.dbo.Results with (nolock)
> where CallStartTime >= '9/13/2006'
> group by AgentFK)
> AS Total1,
>
> (SELECT FK_TsrId, count(*) from DataBase3.dbo.transactions with (nolock)
> where WeekEnding = '9/16/2006'
> and status IN ('CONFIRMED', 'QUALIFIED', 'PRINTED', 'NEW', 'CANCELED')
> Group by FK_TsrId)
> AS Total2,
>
> (SELECT AgentFK, count(*) from DataBase2.dbo.Results with (nolock)
> where CallStartTime >= '9/11/2006'
> group by AgentFK)
> AS Total3
>
> from DataBase1.dbo.Users with (nolock)
>
> INNER JOIN DataBase3.dbo.transactions with (nolock)
> ON DataBase1.dbo.Users.PKUsers = DataBase3.dbo.transactions.FK_TsrId
>
> Where (DataBase3.dbo.transactions.status IN ('CONFIRMED', 'QUALIFIED',
> 'PRINTED', 'NEW', 'CANCELED'))
>
>
> and (DataBase3.dbo.transactions.transactiondate >= '9/13/2006')
>
> Group By DataBase1.dbo.Users.Name
>
> Order By DataBase1.dbo.Users.Name
>
> The error I keep getting is...
>
> Only one expression can be specified in the select list when the subquery is
> not introduced with EXISTS.
>
> Any help will be greatly appreciated.
> --
> CipherTeKST
> MCSE: Security 2003, CCNA, Security+