Giving Up with Stored Procedure

Sat, 10 Nov 2001 23:17:37 -0700

Hi guys, i'm about to give up on this, see i have this stored procedure.

/*-------------------------------------------------------------

@pltcd VARCHAR(30), @tagno VARCHAR(30)

AS

SELECT PHENAME, SUM(PHECNT) AS PHESUM
FROM (SELECT TFP1010.PHENM AS PHENAME, COUNT(*) AS PHECNT
FROM TFP1010 JOIN
TFP5030 ON TFP1010.PHECD = TFP5030.PHECD1
WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
GROUP BY TFP1010.PHENM
UNION ALL
SELECT TFP1010.PHENM, COUNT(*)
FROM TFP1010 JOIN
TFP5030 ON TFP1010.PHECD = TFP5030.PHECD2
WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
GROUP BY TFP1010.PHENM
UNION ALL
SELECT TFP1010.PHENM, COUNT(*)
FROM TFP1010 JOIN
TFP5030 ON TFP1010.PHECD = TFP5030.PHECD3
WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
GROUP BY TFP1010.PHENM
UNION ALL
SELECT TFP1010.PHENM, COUNT(*)
FROM TFP1010 JOIN
TFP5030 ON TFP1010.PHECD = TFP5030.PHECD4
WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
GROUP BY TFP1010.PHENM) PHETOP
GROUP BY PHENAME
ORDER BY PHESUM DESC

/*-------------------------------------------------------------

the output of that stored procedure is this.

/*-------------------------------------------------------------
_______________________
PHENAME PHESUM
_______________________
OUT OF CLEARANCE 3
ELONGATION 2
WRONG CLEARANCE 2
A LINING 1
CORROSION 1
DAMAGE 1
FLUSHING 1
OTHERS 1
UNBALANCE 1

/*-------------------------------------------------------------

but what i need is display the items with values of 1 as OTHERS. my purpose
for this is to plot a pie chart to display the top 3 items (OUT OF CLEARANCE,
ELONGATION, AND WRONG CLEARANCE) having values more than 1 and items with
values of 1 should be considered as OTHERS.

hope you could help on this guys, i need it badly.

thanks in advance,

arvin

Re: Giving Up with Stored Procedure

Sun, 11 Nov 2001 19:14:18 -0700

:
:
: Hi guys, i'm about to give up on this, see i have this stored procedure.
:
: /*-------------------------------------------------------------
:
: @pltcd VARCHAR(30), @tagno VARCHAR(30)
:
: AS
:
: SELECT PHENAME, SUM(PHECNT) AS PHESUM
: FROM (SELECT TFP1010.PHENM AS PHENAME, COUNT(*) AS PHECNT
: FROM TFP1010 JOIN
: TFP5030 ON TFP1010.PHECD = TFP5030.PHECD1
: WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
: GROUP BY TFP1010.PHENM
: UNION ALL
: SELECT TFP1010.PHENM, COUNT(*)
: FROM TFP1010 JOIN
: TFP5030 ON TFP1010.PHECD = TFP5030.PHECD2
: WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
: GROUP BY TFP1010.PHENM
: UNION ALL
: SELECT TFP1010.PHENM, COUNT(*)
: FROM TFP1010 JOIN
: TFP5030 ON TFP1010.PHECD = TFP5030.PHECD3
: WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
: GROUP BY TFP1010.PHENM
: UNION ALL
: SELECT TFP1010.PHENM, COUNT(*)
: FROM TFP1010 JOIN
: TFP5030 ON TFP1010.PHECD = TFP5030.PHECD4
: WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
: GROUP BY TFP1010.PHENM) PHETOP
: GROUP BY PHENAME
: ORDER BY PHESUM DESC
:
: /*-------------------------------------------------------------
:
: the output of that stored procedure is this.
:
: /*-------------------------------------------------------------
: _______________________
: PHENAME PHESUM
: _______________________
: OUT OF CLEARANCE 3
: ELONGATION 2
: WRONG CLEARANCE 2
: A LINING 1
: CORROSION 1
: DAMAGE 1
: FLUSHING 1
: OTHERS 1
: UNBALANCE 1
:
: /*-------------------------------------------------------------
:
: but what i need is display the items with values of 1 as OTHERS. my purpose
: for this is to plot a pie chart to display the top 3 items (OUT OF CLEARANCE,
: ELONGATION, AND WRONG CLEARANCE) having values more than 1 and items with
: values of 1 should be considered as OTHERS.
:
: hope you could help on this guys, i need it badly.
:
: thanks in advance,
:
: arvin
:

For complex queries like this, please use Views to solve the problem.
Neo Gigs
http://communities.msn.com.my/VisualBasicwithNeo

'Giving Up with Stored Procedure' Thread RSS Feed

Giving Up with Stored Procedure

Re: Giving Up with Stored Procedure