Database

GO
/****** Object:  UserDefinedFunction [dbo].[SplitGETPosFunc]    Script Date: 10/20/2010 15:22:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SplitGETPosFunc](@String varchar(8000), 
@Delimiter char(1),
@POSITION INT)
RETURNS varchar(1000)
WITH EXECUTE AS CALLER
AS
begin     
	declare @idx int;     
    DECLARE @CHARIDX INT;
    DECLARE @INPUTSTR VARCHAR(8000);
    declare @OUTPUT varchar(1000);
    SET @INPUTSTR=@String;
    SET @CHARIDX =0;
    SET @idx=0;
    if (substring(@String,1,1)='S')
		begin
			while @idx < @POSITION   
				begin     
				SET @CHARIDX= CHARINDEX(@Delimiter, @INPUTSTR);
				SET @OUTPUT=SUBSTRING(@INPUTSTR, 1, @CHARIDX - 1)
				SET @INPUTSTR = SUBSTRING(@INPUTSTR,@CHARIDX +1,LEN(@INPUTSTR));
				SET @idx=@idx+1;
			end 
		end
	else
	   set @OUTPUT=@String;
return   @OUTPUT;  
end

WITH 
	ParentChildRels
	(ParentID, ChildID, KeyField, [Path], HierarchyLevel, Doc) 
AS 
(
	SELECT     
		ParentID, ChildID, KeyField, CAST('root/' + Folder AS nvarchar(255)) AS Path, 1 AS HierarchyLevel, Doc 
	FROM         
	(
		SELECT     
			dbo.Folder.ParentID, dbo.Folder.ID AS ChildID, dbo.Folder.Folder, dbo.[Document].DocumentID AS KeyField, dbo.[Document].[Document] AS Doc
		FROM          
			dbo.[Document] 
		RIGHT OUTER JOIN
			dbo.Folder 
		ON 
			dbo.[Document].FolderID = dbo.Folder.ID
	) AS x
	WHERE     
		(ParentID = 0)
	
	UNION ALL


	SELECT     
		r.ParentID, r.ChildID, r.KeyField, CAST(RTRIM(pr.Path) + '/' + r.Folder AS nvarchar(255)) AS Path, pr.HierarchyLevel + 1 AS HierarchyLevel, r.Doc
	FROM         
	(
		SELECT     
			Folder_1.ParentID, Folder_1.ID AS ChildID, Folder_1.Folder, Document_1.DocumentID AS KeyField, Document_1.[Document] AS Doc
		FROM          
			dbo.[Document] AS Document_1 
		INNER JOIN
			dbo.[Folder] AS Folder_1 
		ON
			Document_1.FolderID = Folder_1.ID
	) AS r 
	INNER JOIN
		ParentChildRels AS pr 
	ON r.ParentID = pr.ChildID
)
SELECT DISTINCT TOP (100) PERCENT 
	ParentID, ChildID, KeyField, RTRIM(RTRIM([Path]) + '/' + Doc)
FROM         
	ParentChildRels AS ParentChildRels_1
WHERE Keyfield is not null
ORDER BY 
	RTRIM(RTRIM([Path]) + '/' + Doc), ParentID, ChildID, KeyField

SELECT * FROM TABLE1
WHERE MYFLG =0 
AND MYFLG =(SELECT (CASE WHEN COLUMN1 = 1 AND COLUMN2 = 0 THEN 1 ELSE 0 END) AS MYFLG FROM TABLE1)

SELECT * FROM TABLE1 WHERE (MYFLG = 0 OR (COLUMN1 = 1 AND COLUMN2 = 0))

not in  where  COLUMN1 = 1 AND COLUMN2 = 0

Select * from TABLE1 where PK not in ( Select PK From TABLE1 Where COLUMN1 = 1 AND COLUMN2 =0)

WHERE MYFLG =0 

SELECT CASE WHEN SMPTABLE.RPFLG = 1 AND SMPTABLE.NDFLG= 0 THEN 
1 ELSE 0 END AS MYFLG
FROM  [TBMASTER] ,SMPTABLE,ANLTABLE,WKTABLE
WHERE (SMPTABLE.CERTCODE BETWEEN 1 AND 5 OR
SMPTABLE.CERTCODE BETWEEN 11 AND 15 or
SMPTABLE.CERTCODE = 17) AND
(SMPTABLE.DDATE IS NOT NULL) AND
(SMPTABLE.CERTNNO = 0 OR
SMPTABLE.CERTNNO IS NULL) AND ((SMPTABLE.DIVCODE = 1) OR
(SMPTABLE.DIVCODE = 5))
AND (ANLTABLE.ROWNO = 1) AND (ANLTABLE.KMKCD <> 1998)
AND [TBMASTER].SAMPLECD = SMPTABLE.SAMPLECD
AND ANLTABLE.SHINO=SMPTABLE.SHINO
AND  SMPTABLE.ORDNO = ANLTABLE.ORDNO
AND WKTABLE.ORDNO =SMPTABLE.ORDNO
AND ((WKTABLE.SECT=31) OR (WKTABLE.SECT =51))
AND ([ANLTABLE].ENVFLG = 0)

WITH myTable as 
(
--Put here all your  query
)
 select * from myTable where MYFLAG=0

WITH