August 31, 2007 at 1:16 am
is there anything else i can use instead of case statement in stored procedures.
case statement creates each row but i want all the result in a single row.
please help me in this...
August 31, 2007 at 1:30 am
You may be able to achieve a single row result with a case statement too. If you post your problem with a bit more details it will make it easier to answer, or find a solution.
Solutions can range from using COALESCE, user defined functions to pivot tables.
Regards,
Andras
August 31, 2007 at 1:51 am
here is my script::::
SELECT
SubsTrans
.PubCode,
SubsTrans
.[Update],
CASE
SubsTrans.Trans_id
WHEN
(5)
THEN
SUM([SubsTrans].[TransAmount])
ELSE
0
END
AS RE,
CASE
SubsTrans.Trans_id
WHEN
(1)
THEN
SUM([SubsTrans].[TransAmount])
ELSE
0
END
AS CN,
CASE
SubsTrans.Trans_id
WHEN
(3)
THEN
SUM([SubsTrans].[TransAmount])
ELSE
0
END
AS MC,
CASE
SubsTrans.Trans_id
WHEN
(4)
THEN
SUM([SubsTrans].[TransAmount])
ELSE
0
END
AS PR
FROM
(SubsTrans INNER JOIN TransControl
ON
SubsTrans.Trans_id = TransControl.Trans_id)
INNER
JOIN PublicationControl
ON
SubsTrans.PubCode = PublicationControl.PubCode
WHERE
(((SubsTrans.TransDate) Between [PublicationControl].[PrStartDate] And [PublicationControl].[PrEndDate]))
GROUP
BY SubsTrans.PubCode, SubsTrans.[Update],SubsTrans.Trans_id
HAVING
(((SubsTrans.[Update])= 'True'));
any suggestion please
August 31, 2007 at 2:21 am
Encapsulating the above into a select with part of the same group by statement, and adding a MAX(RE), MAX(CE), MAX(MC) .. columns (assuming their values are not negative) could work.
Something like:
SELECT ... MAX(x.RE), MAX(x.CE), MAX(x.MC) FROM (
---your query---
) AS x
GROUP BY x.PubCode, x.[Update]
The query optimizer recognizes that the GROUP BY is on the subset of the columns of the subquery, so the execution plan for my test case looked very reasonable. You will need to check that this is the case with your query though. If not, this solution would suffer in terms of performance.
Regards,
Andras
August 31, 2007 at 7:38 am
Try this:
SELECT
SubsTrans.PubCode
, SubsTrans.[Update]
,sum(CASE when SubsTrans.Trans_id = 5 THEN [SubsTrans].[TransAmount] ELSE 0 END) AS RE
,sum(CASE when SubsTrans.Trans_id = 1 THEN [SubsTrans].[TransAmount] ELSE 0 END) AS CN
,sum(CASE when SubsTrans.Trans_id = 3 THEN [SubsTrans].[TransAmount] ELSE 0 END) AS MC
,sum(CASE when SubsTrans.Trans_id = 4 THEN SubsTrans].[TransAmount] ELSE 0 END) AS PR
FROM
SubsTrans INNER JOIN TransControl
ON SubsTrans.Trans_id = TransControl.Trans_id
INNER JOIN PublicationControl
ON SubsTrans.PubCode = PublicationControl.PubCode
WHERE
SubsTrans.TransDate Between [PublicationControl].[PrStartDate]
And [PublicationControl].[PrEndDate]
and SubTrans.[UPDATE]='TRUE'
GROUP
BY SubsTrans.PubCode, SubsTrans.[Update]
Hope this helps
August 31, 2007 at 7:51 am
Thank you so much Richard,
I really appreciate your assistance.
thanks once again.....its working really fine.
Regards
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply