Editing an SQL Stored Procedure

Question

Post reply

Editing an SQL Stored Procedure

ready2drum

SSChasing Mays

Points: 604
More actions
August 5, 2009 at 8:52 am

#207811

I've edited an SQL stored procedure (see code below) and was wanting to know if I've set it up correctly....I'm trying to display the contents of columns 'CONSUME' AND 'SLOFF', while subtracting out the columns 'UNCONSUME', 'UNDOSL', and 'UNTURB' from the 'j_audit' table.
-----------------
CREATE Procedure sa_web_RTWreport
@starttime varchar(23), @endtime varchar(23), @machineID varchar(3)
AS
SELECT mx_timestamp, mach_id, mx_2, mx_3, j_id, grade_spec, run_num, wgt_amount, dbo.CVTRMS(mx_timestamp) as action_time
FROM j_audit
WHERE dbo.CVTRMS(mx_timestamp) >= @starttime AND dbo.CVTRMS(mx_timestamp) < @endtime AND
(mx_2= 'CONSUME' OR mx_2 = 'SLOFF' - mx_2 = 'UNCONSUME' OR mx_2 ='UNDOSL' OR mx_2 = 'UNTURB') AND
mach_id like @machineID + '%'
ORDER BY mach_id, j_id, mx_timestamp
Return
GO
---------------
Your feedback is greatly appreciate. Thanks in advance!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

there was a syntax error in the OR portion of the where statement where a minus sign should be an OR;

'SLOFF' - mx_2 = 'UNCONSUME'

should be

'SLOFF' OR mx_2 = 'UNCONSUME'

you stated you are trying to subtract columns...i think that might be the issue...you test values, not subtract columns.

below i assume you are testing for one of five values...maybe you want only the first tow insteaD?

since you are testing one field for five values, i would change it to an IN statemnt, but otherwise it seems OK:

CREATE Procedure sa_web_RTWreport

@starttime varchar(23), @endtime varchar(23), @machineID varchar(3)

AS

BEGIN

SELECT mx_timestamp, mach_id, mx_2, mx_3, j_id, grade_spec, run_num, wgt_amount, dbo.CVTRMS(mx_timestamp) as action_time

FROM j_audit

WHERE dbo.CVTRMS(mx_timestamp) >= @starttime

AND dbo.CVTRMS(mx_timestamp) < @endtime

AND mx_2 IN ('CONSUME','SLOFF','UNCONSUME','UNDOSL','UNTURB')

AND mach_id like @machineID + '%'

ORDER BY mach_id, j_id, mx_timestamp

Return

END

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ready2drum SSChasing Mays Points: 604 More actions · Answer 2

I appreciate your help and guidance on this issue...it makes sense to me what you stated. I've learned something new today. Thanks Lowell!

Lowell SSC Guru Points: 323546 More actions · Answer 3

thanks for the feedback;

can you clarify, you wanted to edit the procedure so it only returned results for 'CONSUME','SLOFF' and not all 5 values? was that the objective?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ready2drum SSChasing Mays Points: 604 More actions · Answer 4

I apologize for not being clear...the goal was to total up the 'wgt_amount' column by adding the records with mx_2 code of 'CONSUME' OR 'SLOFF' and then remove (subtract out) the 'UNCONSUME', 'UNTURB', AND 'UNDOSL' records.

Hope that helps! Thanks Lowell.