Friday, February 12, 2010

using case in where and join clauses

some times we may want to join tables with some cases here is a query...

SELECT case when charindex('_',[T].[NO]) > 0
then substring([T].[NO],0,charindex('_',[T].[NO]))
else [T].[NO] end as [NOHEADER]
,[T].[TITLE]
,[T].[CODE]
,[TEXTENSIONS].[EXTENSION]
FROM [sample].[T]
LEFT OUTER JOIN [TEXTENSIONS]
ON [TEXTENSIONS].[CODE]=2
and [TEXTENSIONS].[PRIMARY]='Y'
and [TEXTENSIONS].[NO]=
case when charindex('_',[T].[NO]) > 0
then substring([T].[NO],0,charindex('_',[T].[NO]))
else [T].[NO] end

further reads
-1
-2

No comments: