Giving Up with Stored Procedure - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Giving Up with Stored Procedure

arvinarvin Posts: 22Member


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

Comments

  • gigsvoogigsvoo Posts: 328Member
    :
    :
    : 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


Sign In or Register to comment.