Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.