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



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: 328
    :
    :
    : 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.