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)
Showing posts with label intdeclare. Show all posts
Showing posts with label intdeclare. Show all posts
Monday, March 19, 2012
Assigning Variables
assigning variables
Thanks for all the help, just one last question on xml, using the following
code
DECLARE @.hDoc int
Declare @.RptType varchar(10)
Declare @.RptNo varchar(6)
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
when running this query, how can i assign the reportType to @.rptType and
reportNumber to @.RptNo
Peter,
You can try:
DECLARE @.hDoc int
Declare @.RptType varchar(10)
Declare @.RptNo varchar(6)
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select @.RptType = reportType, @.RptNo = reportNumber
from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
... Of course, you won't be able to select the rest of the values along with
these variable assignments, so if you want a result set back you'll have you
re-select from OpenXML without the variables.
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:7627529A-C675-4DF8-AA27-78F44C66F5EB@.microsoft.com...
> Thanks for all the help, just one last question on xml, using the
following
> code
> DECLARE @.hDoc int
> Declare @.RptType varchar(10)
> Declare @.RptNo varchar(6)
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
> Select * from OpenXML(@.hDoc, '//Header') with
> (reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
> reportSequenceNumber varchar(6), userNumber varchar(6) )
> EXEC sp_xml_removedocument @.hDoc
> when running this query, how can i assign the reportType to @.rptType and
> reportNumber to @.RptNo
code
DECLARE @.hDoc int
Declare @.RptType varchar(10)
Declare @.RptNo varchar(6)
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
when running this query, how can i assign the reportType to @.rptType and
reportNumber to @.RptNo
Peter,
You can try:
DECLARE @.hDoc int
Declare @.RptType varchar(10)
Declare @.RptNo varchar(6)
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select @.RptType = reportType, @.RptNo = reportNumber
from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
... Of course, you won't be able to select the rest of the values along with
these variable assignments, so if you want a result set back you'll have you
re-select from OpenXML without the variables.
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:7627529A-C675-4DF8-AA27-78F44C66F5EB@.microsoft.com...
> Thanks for all the help, just one last question on xml, using the
following
> code
> DECLARE @.hDoc int
> Declare @.RptType varchar(10)
> Declare @.RptNo varchar(6)
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
> Select * from OpenXML(@.hDoc, '//Header') with
> (reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
> reportSequenceNumber varchar(6), userNumber varchar(6) )
> EXEC sp_xml_removedocument @.hDoc
> when running this query, how can i assign the reportType to @.rptType and
> reportNumber to @.RptNo
Subscribe to:
Posts (Atom)