Assigning Variables

i have a snippit of a query
DECLARE @.INPUTRPT int
DECLARE @.ADDACSRPT int
SELECT a.companyname,
CASE WHEN EXISTS (Select @.INPUTRPT = Count(Licence)
from INPUT_HEADERS as b
WHERE (b.DatePostedToBureau IS NULL AND b.licence =
a.licence))
THEN (Select Count(Licence)
from BossData.dbo.INPUT_HEADERS as b
WHERE (b.DatePostedToBureau IS NULL AND b.licence =
a.licence))
ELSE 0
END as 'INPUTRPT',
CASE WHEN EXISTS (Select Count(Licence)
from ADDACS_HEADERS as b
WHERE (b.DateSubmitted IS NULL AND b.licence =
a.licence))
THEN (Select Count(Licence)
from BossData.dbo.ADDACS_HEADERS as b
WHERE (b.DateSubmitted IS NULL AND b.licence =
a.licence))
ELSE 0
END as 'ADDACSRPT'
how can i assign the variable to the case results"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:B4146EC0-BA94-44BB-B7C0-AABBED189C77@.microsoft.com...

> how can i assign the variable to the case results
SELECT @.variable = CASE Column1
WHEN 1 THEN 'Hello'
ELSE 'World'
END AS SomeName
FROM...
Rick Sawtell
MCT, MCSD, MCDBA|||On Wed, 14 Dec 2005 05:50:24 -0800, Peter Newman wrote:

>i have a snippit of a query
>DECLARE @.INPUTRPT int
>DECLARE @.ADDACSRPT int
>SELECT a.companyname,
> CASE WHEN EXISTS (Select @.INPUTRPT = Count(Licence)
> from INPUT_HEADERS as b
> WHERE (b.DatePostedToBureau IS NULL AND b.licence =
>a.licence))
> THEN (Select Count(Licence)
> from BossData.dbo.INPUT_HEADERS as b
> WHERE (b.DatePostedToBureau IS NULL AND b.licence =
>a.licence))
> ELSE 0
> END as 'INPUTRPT',
> CASE WHEN EXISTS (Select Count(Licence)
> from ADDACS_HEADERS as b
> WHERE (b.DateSubmitted IS NULL AND b.licence =
>a.licence))
> THEN (Select Count(Licence)
> from BossData.dbo.ADDACS_HEADERS as b
> WHERE (b.DateSubmitted IS NULL AND b.licence =
>a.licence))
> ELSE 0
> END as 'ADDACSRPT'
>how can i assign the variable to the case results
Hi Peter,
Rick already answered the final question, but I believe that the query
can be simplified - you don;t need the CASE expressions (a COUNT
subquery always returns one row, so the EXISTS test will always result
in True).
SELECT a.companyname,
(Select Count(Licence)
from BossData.dbo.INPUT_HEADERS as b
WHERE (b.DatePostedToBureau IS NULL AND b.licence = a.licence))
as 'INPUTRPT',
(Select Count(Licence)
from BossData.dbo.ADDACS_HEADERS as b
WHERE (b.DateSubmitted IS NULL AND b.licence = a.licence))
as 'ADDACSRPT'
(Later)
I just noticed that you try to assign a variable in a statement that
will also return rows to the client. That is not possible in SQL Server.
You either assign variables, OR you return data - never both.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)