Here My conditions are ,
- if workorderi id = 13 -i.e valid number and allworkorderid=' ' then print only one workorderid
- if workorderid=1666666-invalidnumber and allworkorderid=' ' then print invalidnumber
- if workorderid=845u485u45u4 -invalidnumber and allworkorderid='yes' then print allworkorderid
- if workorderid = 13 -validnumber and allworkorderid='yes' then print allworkorderid .
- if workorderid=845u485u45u4 -invalidnumber and allworkorderid='no' then print invalid character
Every conditions is working except 5 th one , It displaying both the messages but i want precedence only on string for these condition
i.e
133333,'no' should be a invalid character, what ever the number it may be if it is no it should display invalid character .
Below i gave my code Samples:
create or ALTER PROC Sp_workorders (@workorderid INT,
@allworkerid VARCHAR(20))
AS
BEGIN
--this is used to check the id , if it exists below condition will execute or else ,it will print as invalid number
IF EXISTS(SELECT wo.workorderid,
wr.productid
FROM adventureworks2019.production.workorder wo
INNER JOIN adventureworks2019.production.workorderrouting
wr
ON wo.workorderid = wr.workorderid
WHERE wo.workorderid = @workorderid
GROUP BY wo. workorderid,
wr.productid)
BEGIN
--this will run if exists is true
IF ( @workorderid = @workorderid
AND @allworkerid = ' ' )
SELECT Workorders.workorderid,
productid,
countofoperationsequence,
countoflocationid,
max_operationseq,
max_locationid,
completionduration,
latedays,
category
FROM (SELECT wo.workorderid,
wo.productid,
Count(operationsequence)CountofOperationSequence,
Count(locationid) CountofLocationID,
Max(operationsequence) MAx_operationSeq,
Max(locationid) Max_LocationID
FROM adventureworks2019.production.workorder WO
INNER JOIN
adventureworks2019.production.workorderrouting
WR
ON WO.workorderid = WR.workorderid
GROUP BY wo.workorderid,
wo.productid)Workorders
INNER JOIN (SELECT workorderid,
Datediff(day, startdate, enddate)
CompletionDuration ,
Datediff(day, duedate,
enddate) LateDays,
CASE
WHEN enddate > duedate THEN
'Delayed Completion of Work Order'
WHEN enddate = duedate THEN
'On time Completion of Work Order'
WHEN enddate < duedate THEN
'Advance Completion of Work Order'
END AS
Category
FROM adventureworks2019.production.workorder)
DaysforCompletingwork
ON Workorders.workorderid =
DaysforCompletingwork.workorderid
WHERE Workorders.workorderid = @workorderid
end
ELSE--Else it will execute this
BEGIN
PRINT 'invalid number'
END
--it is used to check only one parameter which is string ,whatever the first parameter it ignores it ,it will check only yes.
IF ( @allworkerid = 'yes ' or @allworkerid=' ' )
--calling a second procedure which is saved as a seperate file
EXEC Sp_workorders2
@workorderid=@workorderid,
@allworkerid=@allworkerid
ELSE
PRINT 'invalid character'
END
--Scenarios:
--exec Sp_workorders 13,'' -- working fine
-- exec Sp_workorders 13,'yes'--working fine
-- exec Sp_workorders 13,'no'--working fine
-- exec Sp_workorders 137777,'yes '--working fine whatever workorderidnumber it should give only precedence to only yes
-- exec Sp_workorders 137788,'no' -- not working ,showing both the messages here i want to print message as invalid character
Aucun commentaire:
Enregistrer un commentaire