InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 16:23:55 GMT20http://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspx[quote][b]ChrisM@Work (10/31/2012)[/b][hr][quote][b]sqldba_newbie (10/31/2012)[/b][hr][quote][b]ChrisM@Work (10/31/2012)[/b][hr][quote][b]sqldba_newbie (10/28/2012)[/b][hr]my bad...i have updated the code now...thanks[/quote]Where? Without knowing how #sid is populated, it's impossible to do what you are asking.[/quote] CREATE TABLE #Sid ( ID INT ) SET @i = 1WHILE @i <= 8000 BEGIN INSERT INTO #Sid VALUES (@i) SET @i = @i + 1 ENDIt is also in the code.[/quote]It isn't in the code you've posted, in the opening post of this thread. I recommend you post the whole batch again in a new post, in case there's anything else you have missed out, which you can see but we can't. I suspect that what you are trying to do isn't particularly challenging. What's challenging for us is trying to figure out the problem, not the solution.[/quote]It was edited in the quote in the first response back to you. That is exactly why I reposted it because changing the code inside a quote gets totally lost. [quote]What's challenging for us is trying to figure out the problem, not the solution.[/quote]+100 :-DWed, 31 Oct 2012 13:50:19 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspx[quote][b]sqldba_newbie (10/31/2012)[/b][hr][quote][b]ChrisM@Work (10/31/2012)[/b][hr][quote][b]sqldba_newbie (10/28/2012)[/b][hr]my bad...i have updated the code now...thanks[/quote]Where? Without knowing how #sid is populated, it's impossible to do what you are asking.[/quote] CREATE TABLE #Sid ( ID INT ) SET @i = 1WHILE @i <= 8000 BEGIN INSERT INTO #Sid VALUES (@i) SET @i = @i + 1 ENDIt is also in the code.[/quote]It isn't in the code you've posted, in the opening post of this thread. I recommend you post the whole batch again in a new post, in case there's anything else you have missed out, which you can see but we can't. I suspect that what you are trying to do isn't particularly challenging. What's challenging for us is trying to figure out the problem, not the solution.Wed, 31 Oct 2012 13:30:27 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspxYou seem to be long on need of help and very short in supply of details.Here is the code you posted.[code]DECLARE @CurrentDateTime DATETIMESET @CurrentDateTime = Getdate()DECLARE @Nth AS BITDECLARE @Aus INTDECLARE @Ante NVARCHAR(255)DECLARE @Atid INTDECLARE @Adid INTDECLARE @Brid INTDECLARE @Tid INTDECLARE @TeID INTDECLARE @Rkey NVARCHAR(20)SET @Rkey= '123456'--Get Exception Info SELECT @Tid = TidFROM Synonym_TesWHERE [ID] = @TeIDSELECT TOP(1)@Atid = TidFROM Synonym_TasksWHERE [Name] = 'Azn'SET @Nth = 0-- SET @Adid= 64 SELECT @Adid= brt.Dsid, @Brid = IdFROM Synonym_Brt brtWHERE brt.Rtn = 'Part 1'---- Is an auth needed? IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Fds')) DROP TABLE #FdsIF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Rls')) DROP TABLE #RlsIF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Mtcs')) DROP TABLE #Mtcs CREATE TABLE #Sid ( ID INT ) SET @i = 1WHILE @i <= 8000 BEGIN INSERT INTO #Sid VALUES (@i) SET @i = @i + 1 ENDCREATE TABLE #Fds ( Dsid INT, Rid INT, Fnme VARCHAR(100), Fve VARCHAR(255) )CREATE TABLE #Rls ( Dsid INT, Brid INT, Fnme VARCHAR(100), Fve VARCHAR(MAX), FCprs VARCHAR(30), Fopr VARCHAR(10) )CREATE TABLE #Mtcs ( Dsid INT, Rid INT, Brid INT )DECLARE @CidINTDECLARE @flag INTDECLARE @Count INTSET @flag = 1SELECT @Count = Count(Rid)FROM CgsWHERE Rid = @RkeyWHILE ( @flag <= @Count ) BEGIN SELECT @Cid= Id FROM Cgs WHERE Rid = @Rkey AND Cordr = @flag INSERT INTO #Fds (Dsid, Rid, Fnme, Fve) SELECT @AuthDsid, Rid, Fnme, Fve FROM vw_FdsAzn WHERE Rid= @Cid SET @flag = @flag + 1 ENDINSERT INTO #Rls (Dsid, Brid, Fnme, Fve, FCprs, Fopr)SELECT @AuthDsid, br.Id, brc.Fnme, Fve= CASE WHEN brc.FCprs IN ( 'INLE', 'NOT-BLE' ) THEN [dbo].[my_function](brc.Fve, brc.FCprs) ELSE brc.Fve END, brc.FCprs, dsf.FoprFROM Synonym_BRls br JOIN Synonym_Brc brc ON brc.Brid= br.Id JOIN Synonym_Brt brt ON brt.Id = br.Brid JOIN Synonym_DataSourceFds dsf ON dsf.Fnme= brc.Fnme AND dsf.Dsid = brt.DsidWHERE br.Atve= 1 AND br.Brid = @Brid DECLARE @dlm CHARSET @dlm = ','SELECT Rid, Dsid, Brid, Fnme, CompareFrom, FCprs, Fopr, CompareTo, PassedINTO #FCprssFROM (SELECT r.Brid, r.Fnme, r.FveAS CompareTo, r.FCprs, r.Fopr, f.FveAS CompareFrom, f.Rid, f.Dsid, CASE WHEN ( ( r.FCprs = 'EQS' AND ( f.Fve= r.Fve) ) OR ( r.FCprs = 'DOEL' AND r.Fopr<> 'OR' AND ( f.Fve<> r.Fve) ) OR ( r.FCprs = 'COS' AND ( f.FveLIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'DON' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'BETH' AND ( f.FveLIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'DOTH' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'ENH' AND ( f.FveLIKE '%' + r.Fve) ) OR ( r.FCprs = 'DOTH' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE '%' + r.Fve) ) OR ( r.FCprs = 'ISNULL' AND f.FveIS NULL ) OR ( r.FCprs = 'DOAL' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') <> r.Fve) ) OR ( r.FCprs = 'DOEIN' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'DOESITH' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'DOETH' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve) ) OR ( r.FCprs = 'ISNUMERIC' AND Isnumeric(f.Fve) = 1 ) OR ( r.FCprs = 'BETWEEN' AND f.FveBETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) ) OR ( r.FCprs = 'NOTBETWEEN' AND f.FveNOT BETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) ) OR ( r.FCprs = 'NOTIN' AND r.Fopr<> 'OR' AND t.ID IS NULL AND f.FveIS NOT NULL ) OR ( r.FCprs = 'NOTIN' AND r.Fopr= 'OR' AND t.ID IS NULL ) OR ( r.FCprs = 'IN' AND t.ID IS NOT NULL ) OR ( r.FCprs = 'ntble' AND r.Fopr<> 'OR' AND t.ID IS NULL AND f.FveIS NOT NULL ) OR ( r.FCprs = 'ntble' AND r.Fopr= 'OR' AND t.ID IS NULL ) OR ( r.FCprs = 'INTABLE' AND t.ID IS NOT NULL ) ) THEN 1 ELSE 0 END AS Passed FROM #Rls r LEFT JOIN #Fds f ON r.Fnme= f.Fnme LEFT JOIN #Sid t ON r.FCprs IN ( 'IN', 'NOTIN', 'INTABLE', 'ntble' ) AND t.ID <= Len(@dlm + r.Fve+ @dlm) AND Substring(@dlm + r.Fve+ @dlm, t.ID - 1, 1) = @dlm -- i need to move this on the top where there is join on # Sid table. AND Ltrim(Rtrim(Substring(@dlm + r.Fve+ @dlm, t.ID, Charindex(@dlm, @dlm + r.Fve+ @dlm, t.ID) - t.ID))) = f.Fve) a [/code]This is chock full of errors both syntactically and logically, but it seems you want to do something with this line.[code]AND Substring(@dlm + r.Fve+ @dlm, t.ID - 1, 1) = @dlm -- i need to move this on the top where there is join on # Sid table.[/code]What do you want to do with it and why?Wed, 31 Oct 2012 09:30:27 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspx[quote][b]ChrisM@Work (10/31/2012)[/b][hr][quote][b]sqldba_newbie (10/28/2012)[/b][hr]my bad...i have updated the code now...thanks[/quote]Where? Without knowing how #sid is populated, it's impossible to do what you are asking.[/quote] CREATE TABLE #Sid ( ID INT ) SET @i = 1WHILE @i <= 8000 BEGIN INSERT INTO #Sid VALUES (@i) SET @i = @i + 1 ENDIt is also in the code.Wed, 31 Oct 2012 09:02:02 GMTcurious_sqldbahttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspx[quote][b]sqldba_newbie (10/28/2012)[/b][hr]my bad...i have updated the code now...thanks[/quote]Where? Without knowing how #sid is populated, it's impossible to do what you are asking.Wed, 31 Oct 2012 08:09:57 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspx[quote][b]sqldba_newbie (10/30/2012)[/b][hr][quote][b]GSquared (10/30/2012)[/b][hr]I'm not clear on what you're asking for help on. I see the code, I see you want to do something in a Join statement instead of "at the end" (do you mean in a Where clause?), but it's not clear to me what precisely you are asking.As an aside, you can simplify your drop-checks on the temp tables. Instead of "if exists ....", just "if object_id(N'tempdb..#MyTempTable') is not null" works just fine, and takes less steps. Microscopically faster, since you're already checking the object_id function in your Where clause, too.[/quote]I need to either re-write this portion or something else. AND Substring(@dlm + r.Fve+ @dlm, t.ID - 1, 1) = @dlm -- i need to move this on the top where there is join on # Sid table.AND Ltrim(Rtrim(Substring(@dlm + r.Fve+ @dlm, t.ID, Charindex(@dlm, @dlm + r.Fve+ @dlm, t.ID) - t.ID))) = f.Fve[/quote]And what is it you're trying to accomplish by moving it? Just have it in a different part of the query because it'll look better? Enforce some rule on the data? Speed up the query?I'm sure the query is clear to you, but it's not to me. You know what the query is for, you know the business rules that apply to it, you know what end result you want from it and you can look at the tables and data in your database, I don't have any of those things available. Nobody here does.I'd love to help, but I can't even tell where to start.Wed, 31 Oct 2012 06:23:51 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspx[quote][b]sqldba_newbie (10/30/2012)[/b][hr][quote][b]GSquared (10/30/2012)[/b][hr]I'm not clear on what you're asking for help on. I see the code, I see you want to do something in a Join statement instead of "at the end" (do you mean in a Where clause?), but it's not clear to me what precisely you are asking.As an aside, you can simplify your drop-checks on the temp tables. Instead of "if exists ....", just "if object_id(N'tempdb..#MyTempTable') is not null" works just fine, and takes less steps. Microscopically faster, since you're already checking the object_id function in your Where clause, too.[/quote]I need to either re-write this portion or something else. AND Substring(@dlm + r.Fve+ @dlm, t.ID - 1, 1) = @dlm -- i need to move this on the top where there is join on # Sid table.AND Ltrim(Rtrim(Substring(@dlm + r.Fve+ @dlm, t.ID, Charindex(@dlm, @dlm + r.Fve+ @dlm, t.ID) - t.ID))) = f.Fve[/quote]I second Gus here. It is clear to you what you want but we don't know your project and can't see you screen. It is pretty obvious that something is a little off here when you are comparing a substring of a variable and concatenating more stuff to it to see if it is the same as the original variable. But what does "Move this on the top where there is join on # Sid"? What does that mean? This is already part of your join condition.Tue, 30 Oct 2012 13:43:46 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspx[quote][b]GSquared (10/30/2012)[/b][hr]I'm not clear on what you're asking for help on. I see the code, I see you want to do something in a Join statement instead of "at the end" (do you mean in a Where clause?), but it's not clear to me what precisely you are asking.As an aside, you can simplify your drop-checks on the temp tables. Instead of "if exists ....", just "if object_id(N'tempdb..#MyTempTable') is not null" works just fine, and takes less steps. Microscopically faster, since you're already checking the object_id function in your Where clause, too.[/quote]I need to either re-write this portion or something else. AND Substring(@dlm + r.Fve+ @dlm, t.ID - 1, 1) = @dlm -- i need to move this on the top where there is join on # Sid table.AND Ltrim(Rtrim(Substring(@dlm + r.Fve+ @dlm, t.ID, Charindex(@dlm, @dlm + r.Fve+ @dlm, t.ID) - t.ID))) = f.FveTue, 30 Oct 2012 13:16:42 GMTcurious_sqldbahttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspxI'm not clear on what you're asking for help on. I see the code, I see you want to do something in a Join statement instead of "at the end" (do you mean in a Where clause?), but it's not clear to me what precisely you are asking.As an aside, you can simplify your drop-checks on the temp tables. Instead of "if exists ....", just "if object_id(N'tempdb..#MyTempTable') is not null" works just fine, and takes less steps. Microscopically faster, since you're already checking the object_id function in your Where clause, too.Tue, 30 Oct 2012 12:38:13 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspxany thoughts?Tue, 30 Oct 2012 09:52:39 GMTcurious_sqldbahttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspx[quote][b]ChrisM@home (10/28/2012)[/b][hr]Where in this code do you populate table #sid?[quote][b]sqldba_newbie (10/28/2012)[/b][hr]My goal here to apply the condition to the query while joining the table instead of doing it at the end. I have added more details in the code. Bottom section of the code is what i am trying to modify. Thanks[code="sql"]DECLARE @CurrentDateTime DATETIMESET @CurrentDateTime = Getdate()DECLARE @Nth AS BITDECLARE @Aus INTDECLARE @Ante NVARCHAR(255)DECLARE @Atid INTDECLARE @Adid INTDECLARE @Brid INTDECLARE @Tid INTDECLARE @TeID INTDECLARE @Rkey NVARCHAR(20)SET @Rkey= '123456'--Get Exception Info SELECT @Tid = TidFROM Synonym_TesWHERE [ID] = @TeIDSELECT TOP(1)@Atid = TidFROM Synonym_TasksWHERE [Name] = 'Azn'SET @Nth = 0-- SET @Adid= 64 SELECT @Adid= brt.Dsid, @Brid = IdFROM Synonym_Brt brtWHERE brt.Rtn = 'Part 1'---- Is an auth needed? IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Fds')) DROP TABLE #FdsIF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Rls')) DROP TABLE #RlsIF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Mtcs')) DROP TABLE #Mtcs CREATE TABLE #Sid ( ID INT ) SET @i = 1WHILE @i <= 8000 BEGIN INSERT INTO #Sid VALUES (@i) SET @i = @i + 1 ENDCREATE TABLE #Fds ( Dsid INT, Rid INT, Fnme VARCHAR(100), Fve VARCHAR(255) )CREATE TABLE #Rls ( Dsid INT, Brid INT, Fnme VARCHAR(100), Fve VARCHAR(MAX), FCprs VARCHAR(30), Fopr VARCHAR(10) )CREATE TABLE #Mtcs ( Dsid INT, Rid INT, Brid INT )DECLARE @CidINTDECLARE @flag INTDECLARE @Count INTSET @flag = 1SELECT @Count = Count(Rid)FROM CgsWHERE Rid = @RkeyWHILE ( @flag <= @Count ) BEGIN SELECT @Cid= Id FROM Cgs WHERE Rid = @Rkey AND Cordr = @flag INSERT INTO #Fds (Dsid, Rid, Fnme, Fve) SELECT @AuthDsid, Rid, Fnme, Fve FROM vw_FdsAzn WHERE Rid= @Cid SET @flag = @flag + 1 ENDINSERT INTO #Rls (Dsid, Brid, Fnme, Fve, FCprs, Fopr)SELECT @AuthDsid, br.Id, brc.Fnme, Fve= CASE WHEN brc.FCprs IN ( 'INLE', 'NOT-BLE' ) THEN [dbo].[my_function](brc.Fve, brc.FCprs) ELSE brc.Fve END, brc.FCprs, dsf.FoprFROM Synonym_BRls br JOIN Synonym_Brc brc ON brc.Brid= br.Id JOIN Synonym_Brt brt ON brt.Id = br.Brid JOIN Synonym_DataSourceFds dsf ON dsf.Fnme= brc.Fnme AND dsf.Dsid = brt.DsidWHERE br.Atve= 1 AND br.Brid = @Brid DECLARE @dlm CHARSET @dlm = ','SELECT Rid, Dsid, Brid, Fnme, CompareFrom, FCprs, Fopr, CompareTo, PassedINTO #FCprssFROM (SELECT r.Brid, r.Fnme, r.FveAS CompareTo, r.FCprs, r.Fopr, f.FveAS CompareFrom, f.Rid, f.Dsid, CASE WHEN ( ( r.FCprs = 'EQS' AND ( f.Fve= r.Fve) ) OR ( r.FCprs = 'DOEL' AND r.Fopr<> 'OR' AND ( f.Fve<> r.Fve) ) OR ( r.FCprs = 'COS' AND ( f.FveLIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'DON' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'BETH' AND ( f.FveLIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'DOTH' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'ENH' AND ( f.FveLIKE '%' + r.Fve) ) OR ( r.FCprs = 'DOTH' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE '%' + r.Fve) ) OR ( r.FCprs = 'ISNULL' AND f.FveIS NULL ) OR ( r.FCprs = 'DOAL' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') <> r.Fve) ) OR ( r.FCprs = 'DOEIN' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'DOESITH' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'DOETH' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve) ) OR ( r.FCprs = 'ISNUMERIC' AND Isnumeric(f.Fve) = 1 ) OR ( r.FCprs = 'BETWEEN' AND f.FveBETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) ) OR ( r.FCprs = 'NOTBETWEEN' AND f.FveNOT BETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) ) OR ( r.FCprs = 'NOTIN' AND r.Fopr<> 'OR' AND t.ID IS NULL AND f.FveIS NOT NULL ) OR ( r.FCprs = 'NOTIN' AND r.Fopr= 'OR' AND t.ID IS NULL ) OR ( r.FCprs = 'IN' AND t.ID IS NOT NULL ) OR ( r.FCprs = 'ntble' AND r.Fopr<> 'OR' AND t.ID IS NULL AND f.FveIS NOT NULL ) OR ( r.FCprs = 'ntble' AND r.Fopr= 'OR' AND t.ID IS NULL ) OR ( r.FCprs = 'INTABLE' AND t.ID IS NOT NULL ) ) THEN 1 ELSE 0 END AS Passed FROM #Rls r LEFT JOIN #Fds f ON r.Fnme= f.Fnme LEFT JOIN #Sid t ON r.FCprs IN ( 'IN', 'NOTIN', 'INTABLE', 'ntble' ) AND t.ID <= Len(@dlm + r.Fve+ @dlm) AND Substring(@dlm + r.Fve+ @dlm, t.ID - 1, 1) = @dlm -- i need to move this on the top where there is join on # Sid table. AND Ltrim(Rtrim(Substring(@dlm + r.Fve+ @dlm, t.ID, Charindex(@dlm, @dlm + r.Fve+ @dlm, t.ID) - t.ID))) = f.Fve) a [/code][/quote][/quote]my bad...i have updated the code now...thanksSun, 28 Oct 2012 19:17:35 GMTcurious_sqldbahttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspxWhere in this code do you populate table #sid?[quote][b]sqldba_newbie (10/28/2012)[/b][hr]My goal here to apply the condition to the query while joining the table instead of doing it at the end. I have added more details in the code. Bottom section of the code is what i am trying to modify. Thanks[code="sql"]DECLARE @CurrentDateTime DATETIMESET @CurrentDateTime = Getdate()DECLARE @Nth AS BITDECLARE @Aus INTDECLARE @Ante NVARCHAR(255)DECLARE @Atid INTDECLARE @Adid INTDECLARE @Brid INTDECLARE @Tid INTDECLARE @TeID INTDECLARE @Rkey NVARCHAR(20)SET @Rkey= '123456'--Get Exception Info SELECT @Tid = TidFROM Synonym_TesWHERE [ID] = @TeIDSELECT TOP(1)@Atid = TidFROM Synonym_TasksWHERE [Name] = 'Azn'SET @Nth = 0-- SET @Adid= 64 SELECT @Adid= brt.Dsid, @Brid = IdFROM Synonym_Brt brtWHERE brt.Rtn = 'Part 1'---- Is an auth needed? IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Fds')) DROP TABLE #FdsIF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Rls')) DROP TABLE #RlsIF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Mtcs')) DROP TABLE #Mtcs CREATE TABLE #Sid ( ID INT ) CREATE TABLE #Fds ( Dsid INT, Rid INT, Fnme VARCHAR(100), Fve VARCHAR(255) )CREATE TABLE #Rls ( Dsid INT, Brid INT, Fnme VARCHAR(100), Fve VARCHAR(MAX), FCprs VARCHAR(30), Fopr VARCHAR(10) )CREATE TABLE #Mtcs ( Dsid INT, Rid INT, Brid INT )DECLARE @CidINTDECLARE @flag INTDECLARE @Count INTSET @flag = 1SELECT @Count = Count(Rid)FROM CgsWHERE Rid = @RkeyWHILE ( @flag <= @Count ) BEGIN SELECT @Cid= Id FROM Cgs WHERE Rid = @Rkey AND Cordr = @flag INSERT INTO #Fds (Dsid, Rid, Fnme, Fve) SELECT @AuthDsid, Rid, Fnme, Fve FROM vw_FdsAzn WHERE Rid= @Cid SET @flag = @flag + 1 ENDINSERT INTO #Rls (Dsid, Brid, Fnme, Fve, FCprs, Fopr)SELECT @AuthDsid, br.Id, brc.Fnme, Fve= CASE WHEN brc.FCprs IN ( 'INLE', 'NOT-BLE' ) THEN [dbo].[my_function](brc.Fve, brc.FCprs) ELSE brc.Fve END, brc.FCprs, dsf.FoprFROM Synonym_BRls br JOIN Synonym_Brc brc ON brc.Brid= br.Id JOIN Synonym_Brt brt ON brt.Id = br.Brid JOIN Synonym_DataSourceFds dsf ON dsf.Fnme= brc.Fnme AND dsf.Dsid = brt.DsidWHERE br.Atve= 1 AND br.Brid = @Brid DECLARE @dlm CHARSET @dlm = ','SELECT Rid, Dsid, Brid, Fnme, CompareFrom, FCprs, Fopr, CompareTo, PassedINTO #FCprssFROM (SELECT r.Brid, r.Fnme, r.FveAS CompareTo, r.FCprs, r.Fopr, f.FveAS CompareFrom, f.Rid, f.Dsid, CASE WHEN ( ( r.FCprs = 'EQS' AND ( f.Fve= r.Fve) ) OR ( r.FCprs = 'DOEL' AND r.Fopr<> 'OR' AND ( f.Fve<> r.Fve) ) OR ( r.FCprs = 'COS' AND ( f.FveLIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'DON' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'BETH' AND ( f.FveLIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'DOTH' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'ENH' AND ( f.FveLIKE '%' + r.Fve) ) OR ( r.FCprs = 'DOTH' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE '%' + r.Fve) ) OR ( r.FCprs = 'ISNULL' AND f.FveIS NULL ) OR ( r.FCprs = 'DOAL' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') <> r.Fve) ) OR ( r.FCprs = 'DOEIN' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'DOESITH' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'DOETH' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve) ) OR ( r.FCprs = 'ISNUMERIC' AND Isnumeric(f.Fve) = 1 ) OR ( r.FCprs = 'BETWEEN' AND f.FveBETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) ) OR ( r.FCprs = 'NOTBETWEEN' AND f.FveNOT BETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) ) OR ( r.FCprs = 'NOTIN' AND r.Fopr<> 'OR' AND t.ID IS NULL AND f.FveIS NOT NULL ) OR ( r.FCprs = 'NOTIN' AND r.Fopr= 'OR' AND t.ID IS NULL ) OR ( r.FCprs = 'IN' AND t.ID IS NOT NULL ) OR ( r.FCprs = 'ntble' AND r.Fopr<> 'OR' AND t.ID IS NULL AND f.FveIS NOT NULL ) OR ( r.FCprs = 'ntble' AND r.Fopr= 'OR' AND t.ID IS NULL ) OR ( r.FCprs = 'INTABLE' AND t.ID IS NOT NULL ) ) THEN 1 ELSE 0 END AS Passed FROM #Rls r LEFT JOIN #Fds f ON r.Fnme= f.Fnme LEFT JOIN #Sid t ON r.FCprs IN ( 'IN', 'NOTIN', 'INTABLE', 'ntble' ) AND t.ID <= Len(@dlm + r.Fve+ @dlm) AND Substring(@dlm + r.Fve+ @dlm, t.ID - 1, 1) = @dlm -- i need to move this on the top where there is join on # Sid table. AND Ltrim(Rtrim(Substring(@dlm + r.Fve+ @dlm, t.ID, Charindex(@dlm, @dlm + r.Fve+ @dlm, t.ID) - t.ID))) = f.Fve) a [/code][/quote]Sun, 28 Oct 2012 11:54:01 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1378039-146-1.aspxMy goal here to apply the condition to the query while joining the table instead of doing it at the end. I have added more details in the code. Bottom section of the code is what i am trying to modify. Thanks[code="plain"]DECLARE @CurrentDateTime DATETIMESET @CurrentDateTime = Getdate()DECLARE @Nth AS BITDECLARE @Aus INTDECLARE @Ante NVARCHAR(255)DECLARE @Atid INTDECLARE @Adid INTDECLARE @Brid INTDECLARE @Tid INTDECLARE @TeID INTDECLARE @Rkey NVARCHAR(20)SET @Rkey= '123456'--Get Exception Info SELECT @Tid = TidFROM Synonym_TesWHERE [ID] = @TeIDSELECT TOP(1)@Atid = TidFROM Synonym_TasksWHERE [Name] = 'Azn'SET @Nth = 0-- SET @Adid= 64 SELECT @Adid= brt.Dsid, @Brid = IdFROM Synonym_Brt brtWHERE brt.Rtn = 'Part 1'---- Is an auth needed? IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Fds')) DROP TABLE #FdsIF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Rls')) DROP TABLE #RlsIF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = Object_id('tempdb..#Mtcs')) DROP TABLE #Mtcs CREATE TABLE #Sid ( ID INT ) CREATE TABLE #Fds ( Dsid INT, Rid INT, Fnme VARCHAR(100), Fve VARCHAR(255) )CREATE TABLE #Rls ( Dsid INT, Brid INT, Fnme VARCHAR(100), Fve VARCHAR(MAX), FCprs VARCHAR(30), Fopr VARCHAR(10) )CREATE TABLE #Mtcs ( Dsid INT, Rid INT, Brid INT )DECLARE @CidINTDECLARE @flag INTDECLARE @Count INTSET @flag = 1SELECT @Count = Count(Rid)FROM CgsWHERE Rid = @RkeyWHILE ( @flag <= @Count ) BEGIN SELECT @Cid= Id FROM Cgs WHERE Rid = @Rkey AND Cordr = @flag INSERT INTO #Fds (Dsid, Rid, Fnme, Fve) SELECT @AuthDsid, Rid, Fnme, Fve FROM vw_FdsAzn WHERE Rid= @Cid SET @flag = @flag + 1 ENDINSERT INTO #Rls (Dsid, Brid, Fnme, Fve, FCprs, Fopr)SELECT @AuthDsid, br.Id, brc.Fnme, Fve= CASE WHEN brc.FCprs IN ( 'INLE', 'NOT-BLE' ) THEN [dbo].[my_function](brc.Fve, brc.FCprs) ELSE brc.Fve END, brc.FCprs, dsf.FoprFROM Synonym_BRls br JOIN Synonym_Brc brc ON brc.Brid= br.Id JOIN Synonym_Brt brt ON brt.Id = br.Brid JOIN Synonym_DataSourceFds dsf ON dsf.Fnme= brc.Fnme AND dsf.Dsid = brt.DsidWHERE br.Atve= 1 AND br.Brid = @Brid DECLARE @dlm CHARSET @dlm = ','SELECT Rid, Dsid, Brid, Fnme, CompareFrom, FCprs, Fopr, CompareTo, PassedINTO #FCprssFROM (SELECT r.Brid, r.Fnme, r.FveAS CompareTo, r.FCprs, r.Fopr, f.FveAS CompareFrom, f.Rid, f.Dsid, CASE WHEN ( ( r.FCprs = 'EQS' AND ( f.Fve= r.Fve) ) OR ( r.FCprs = 'DOEL' AND r.Fopr<> 'OR' AND ( f.Fve<> r.Fve) ) OR ( r.FCprs = 'COS' AND ( f.FveLIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'DON' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'BETH' AND ( f.FveLIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'DOTH' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'ENH' AND ( f.FveLIKE '%' + r.Fve) ) OR ( r.FCprs = 'DOTH' AND r.Fopr<> 'OR' AND ( f.FveNOT LIKE '%' + r.Fve) ) OR ( r.FCprs = 'ISNULL' AND f.FveIS NULL ) OR ( r.FCprs = 'DOAL' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') <> r.Fve) ) OR ( r.FCprs = 'DOEIN' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve+ '%' ) ) OR ( r.FCprs = 'DOESITH' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE r.Fve+ '%' ) ) OR ( r.FCprs = 'DOETH' AND r.Fopr= 'OR' AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve) ) OR ( r.FCprs = 'ISNUMERIC' AND Isnumeric(f.Fve) = 1 ) OR ( r.FCprs = 'BETWEEN' AND f.FveBETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) ) OR ( r.FCprs = 'NOTBETWEEN' AND f.FveNOT BETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) ) OR ( r.FCprs = 'NOTIN' AND r.Fopr<> 'OR' AND t.ID IS NULL AND f.FveIS NOT NULL ) OR ( r.FCprs = 'NOTIN' AND r.Fopr= 'OR' AND t.ID IS NULL ) OR ( r.FCprs = 'IN' AND t.ID IS NOT NULL ) OR ( r.FCprs = 'ntble' AND r.Fopr<> 'OR' AND t.ID IS NULL AND f.FveIS NOT NULL ) OR ( r.FCprs = 'ntble' AND r.Fopr= 'OR' AND t.ID IS NULL ) OR ( r.FCprs = 'INTABLE' AND t.ID IS NOT NULL ) ) THEN 1 ELSE 0 END AS Passed FROM #Rls r LEFT JOIN #Fds f ON r.Fnme= f.Fnme LEFT JOIN #Sid t ON r.FCprs IN ( 'IN', 'NOTIN', 'INTABLE', 'ntble' ) AND t.ID <= Len(@dlm + r.Fve+ @dlm) AND Substring(@dlm + r.Fve+ @dlm, t.ID - 1, 1) = @dlm -- i need to move this on the top where there is join on # Sid table. AND Ltrim(Rtrim(Substring(@dlm + r.Fve+ @dlm, t.ID, Charindex(@dlm, @dlm + r.Fve+ @dlm, t.ID) - t.ID))) = f.Fve) a [/code]Sun, 28 Oct 2012 10:33:33 GMTcurious_sqldba