Click here to Skip to main content
15,037,168 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have a view in stored procedure but i am confused of what exactly they are doing,so can anyone help me on this??

Below is the view

SQL
CREATE VIEW [dbo].[V_FLKDD_DiagnosisDetails_NonStandared]
AS
SELECT     FaultNumber, SUBSTRING(MeasurementID, LEN(MachineSetupName + '#') + 1, LEN(MeasurementID)) AS MeasuredDate, Severity, NULL 
                      AS CitedPeakNumber, NULL AS LocationandAxis, NULL AS Amplitude, NULL AS AmplitudeUnit, NULL AS FrequencyInOrders, NULL 
                      AS ReferenceShaft, NULL AS Range, DeviceSerialNumber, MachineSetupName, MeasurementID,
                          (SELECT     TOP (1) AnswerID
                            FROM          dbo.FLKDD_MachineSetup AS MS
                            WHERE      (QuestionID = 6) AND (DeviceSerialNumber = DeviceSerialNumber) AND (MachineSetupName = MachineSetupName)) AS Mounting,
                          (SELECT     COALESCE (DiagnoseText, dbo.FLKDD_Diagnose_Fault.FaultText) AS Expr1
                            FROM          dbo.FLKDD_Mst_Diagnose_LanguageTexts AS L
                            WHERE      (TextID =
                                                       (SELECT     TextID
                                                         FROM          dbo.FLKDD_Mst_DiagnoseTexts AS D
                                                         WHERE      (RTRIM(LTRIM(DiagnoseText)) = RTRIM(LTRIM(dbo.FLKDD_Diagnose_Fault.FaultText))))) AND (LanguageID =
                                                       (SELECT     FlukeValue
                                                         FROM          dbo.FLKDD_System AS S
                                                         WHERE      (FlukeKey = 'LanguageID')))) AS DiagnoseText,
                          (SELECT     TextID
                            FROM          dbo.FLKDD_Mst_Diagnose_LanguageTexts AS L
                            WHERE      (TextID =
                                                       (SELECT     TextID
                                                         FROM          dbo.FLKDD_Mst_DiagnoseTexts AS D
                                                         WHERE      (RTRIM(LTRIM(DiagnoseText)) = RTRIM(LTRIM(dbo.FLKDD_Diagnose_Fault.FaultText))))) AND (LanguageID =
                                                       (SELECT     FlukeValue
                                                         FROM          dbo.FLKDD_System AS S
                                                         WHERE      (FlukeKey = 'LanguageID')))) AS TextID
FROM         dbo.FLKDD_Diagnose_Fault
WHERE     (FaultText = 'NON-STANDARD FAULT DETECTED')



GO



SQL
ALTER PROCEDURE [dbo].[Usp_Flk_GetDiagnosisDetails]
@DeviceId varchar(50),
@MachineId nvarchar(50),
@MeasurementId varchar(150)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @DIAGNOSIS_DETAILS TABLE(
	FaultNumber INT,
	MeasuredDate DATETIME,
	Severity INT, 
	CitedPeakNumber INT, 
	LocationandAxis NVARCHAR(50), 
	Amplitude FLOAT,
	AmplitudeUnit NVARCHAR(20), 
	FrequencyInOrders FLOAT, 
	ReferenceShaft NCHAR(10),  
	Range INT, 
	DeviceSerialNumber NVARCHAR(50), 
	MachineSetupName NVARCHAR(50), 
	MeasurementID NVARCHAR(150),
	Mounting INT,
	DiagnoseText NVARCHAR(MAX),
	TextID SMALLINT
	)

--Modified by Vinoth - 23-Aug-2011- To increase the performance--
--INSERT INTO @DIAGNOSIS_DETAILS SELECT * FROM V_FLKDD_DiagnosisDetails WHERE (FrequencyInOrders IS NOT NULL) AND (DiagnoseText IS NOT NULL)
INSERT INTO @DIAGNOSIS_DETAILS SELECT * FROM V_FLKDD_DiagnosisDetails WHERE (DiagnoseText IS NOT NULL) 
INSERT INTO @DIAGNOSIS_DETAILS SELECT * FROM V_FLKDD_DiagnosisDetails_NonStandared


By this i got to know that they are restricting FaultText = 'NON-STANDARD FAULT DETECTED' value from populating.But what if i want to show even though the FaultText is NON-STANDARD FAULT DETECTED.
Posted
Updated 21-Aug-14 21:22pm
v3
Comments
coded007 1-Sep-14 2:46am
   
Remove that condition in stored procedure
Baliram Suryawanshi 19-Sep-14 8:40am
   
Yes, Remove statement "WHERE (FaultText = 'NON-STANDARD FAULT DETECTED')" form your view definition

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900