Need to user query results for another query

[kbsc]

I created a query and now I need to use the results from that query to get more detailed results. Any suggestions!

1st Query:
SELECT
c.vchTrustee, COUNT(ba.vchAccountNumber) AS total, ba.vchAccountNumber
FROM
tblBankAccount ba
INNER JOIN tblCase c ON
ba.intcaseID = c.intCaseID

GROUP BY c.vchTrustee, ba.vchAccountNumber, ba.intDeleted, c.intDeleted
HAVING
COUNT(ba.vchAccountNumber) > 1
AND ba.intDeleted = 0
AND c.intDeleted = 0

ORDER by c.vchTrustee, ba.vchAccountNumber

Then I need to take those results and select detailed information for each record that exists with the same ba.vchAccountNumber > 1

SELECT
c.vchTrustee, ba.vchAccountNumber, ba.dteCreated, ba.vchCreatedBy
FROM
tblBankAccount ba
INNER JOIN tblCase c ON
ba.intcaseID = c.intCaseID

WHERE ba.intDeleted = 0
AND c.intDeleted = 0

ORDER by c.vchTrustee, ba.vchAccountNumber

 

[kbsc]

I've created the below query but I get an error of :
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '562-223' to a column of data type int.
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '312602821065' overflowed an int column. Maximum integer value exceeded.


SELECT
c.vchTrustee, ba.vchAccountNumber, ba.dteCreated, ba.vchCreatedBy, ba.intAccountID
FROM
tblBankAccount ba
INNER JOIN tblCase c ON
ba.intcaseID = c.intCaseID

WHERE
ba.vchAccountNumber = ANY
(SELECT
COUNT(ba.vchAccountNumber) AS total
FROM
tblBankAccount ba
INNER JOIN tblCase c ON
ba.intcaseID = c.intCaseID

GROUP BY c.vchTrustee, ba.vchAccountNumber, ba.intDeleted, c.intDeleted
HAVING
COUNT(ba.vchAccountNumber) > 1
AND ba.intDeleted = 0
AND c.intDeleted = 0)

ORDER by c.vchTrustee, ba.vchAccountNumber

 

[philhege]

Your inner query returns a count value, yet you're comparing it to the account number?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[TysonLPrice]

Clunky but...In your first query put the results into a temporary table:

SELECT Into ##TEMP
c.vchTrustee, COUNT(ba.vchAccountNumber) AS total, ba.vchAccountNumber
FROM
tblBankAccount ba
INNER JOIN tblCase c ON
ba.intcaseID = c.intCaseID

GROUP BY c.vchTrustee, ba.vchAccountNumber, ba.intDeleted, c.intDeleted
HAVING
COUNT(ba.vchAccountNumber) > 1
AND ba.intDeleted = 0
AND c.intDeleted = 0

ORDER by c.vchTrustee, ba.vchAccountNumber

And then use that table for part two.

 

[TysonLPrice]

Sorry...

SELECT
c.vchTrustee, COUNT(ba.vchAccountNumber) AS total, ba.vchAccountNumber

Into ##TEMP

FROM

 

[kbsc]

You're right the Count statement in the SELECT clause is what caused alot of my problems. I rewrote it to this and it did what I needed.

Thanks for everyones advice:

SELECT
ba.intAccountID, c.vchTrustee, c.vchCaseNumber, ba.vchAccountNumber, c.intStatus
FROM
tblBankAccount ba
INNER JOIN tblCase c ON
ba.intcaseID = c.intCaseID

WHERE
ba.vchTrustee <> 'DN'
AND
ba.vchAccountNumber IN
(SELECT
ba2.vchAccountNumber
FROM
tblBankAccount ba2

GROUP BY ba2.vchAccountNumber, ba2.intDeleted, ba2.vchTrustee
HAVING
COUNT(ba2.vchAccountNumber) > 1
AND ba2.intDeleted = 0)

ORDER by c.vchTrustee, ba.vchAccountNumber