help with this query!!!!

Rate Topic

Display Mode

Topic Options

Author

Message

curious_sqldba

Posted Sunday, October 28, 2012 10:33 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:13 PM
Points: 1,172, Visits: 2,687

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



DECLARE @CurrentDateTime DATETIME

SET @CurrentDateTime = Getdate()

DECLARE @Nth AS BIT
DECLARE @Aus INT
DECLARE @Ante NVARCHAR(255)
DECLARE @Atid INT
DECLARE @Adid INT
DECLARE @Brid INT
DECLARE @Tid INT
DECLARE @TeID INT
DECLARE @Rkey NVARCHAR(20)

SET @Rkey= '123456'

--Get Exception Info                                                  
SELECT @Tid = Tid
FROM   Synonym_Tes
WHERE  [ID] = @TeID

SELECT TOP(1)@Atid = Tid
FROM   Synonym_Tasks
WHERE  [Name] = 'Azn'

SET @Nth = 0

--  SET @Adid= 64    
SELECT @Adid= brt.Dsid,
       @Brid = Id
FROM   Synonym_Brt brt
WHERE  brt.Rtn = 'Part 1'

---- Is an auth needed?                              
IF EXISTS (SELECT 1
           FROM   tempdb..sysobjects
           WHERE  id = Object_id('tempdb..#Fds'))
  DROP TABLE #Fds

IF EXISTS (SELECT 1
           FROM   tempdb..sysobjects
           WHERE  id = Object_id('tempdb..#Rls'))
  DROP TABLE #Rls

IF 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 @CidINT
DECLARE @flag INT
DECLARE @Count INT

SET @flag = 1

SELECT @Count = Count(Rid)
FROM   Cgs
WHERE  Rid = @Rkey

WHILE ( @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
  END

INSERT 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.Fopr
FROM   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.Dsid
WHERE  br.Atve= 1
       AND br.Brid = @Brid 
       
                                  



DECLARE @dlm CHAR

SET @dlm = ','

SELECT Rid,
       Dsid,
       Brid,
       Fnme,
       CompareFrom,
       FCprs,
       Fopr,
       CompareTo,
       Passed
INTO   #FCprss
FROM   (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

Post #1378039

ChrisM@home

Posted Sunday, October 28, 2012 11:54 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 921, Visits: 3,747

Where in this code do you populate table #sid?

sqldba_newbie (10/28/2012)



DECLARE @CurrentDateTime DATETIME

SET @CurrentDateTime = Getdate()

DECLARE @Nth AS BIT
DECLARE @Aus INT
DECLARE @Ante NVARCHAR(255)
DECLARE @Atid INT
DECLARE @Adid INT
DECLARE @Brid INT
DECLARE @Tid INT
DECLARE @TeID INT
DECLARE @Rkey NVARCHAR(20)

SET @Rkey= '123456'

--Get Exception Info                                                  
SELECT @Tid = Tid
FROM   Synonym_Tes
WHERE  [ID] = @TeID

SELECT TOP(1)@Atid = Tid
FROM   Synonym_Tasks
WHERE  [Name] = 'Azn'

SET @Nth = 0

--  SET @Adid= 64    
SELECT @Adid= brt.Dsid,
       @Brid = Id
FROM   Synonym_Brt brt
WHERE  brt.Rtn = 'Part 1'

---- Is an auth needed?                              
IF EXISTS (SELECT 1
           FROM   tempdb..sysobjects
           WHERE  id = Object_id('tempdb..#Fds'))
  DROP TABLE #Fds

IF EXISTS (SELECT 1
           FROM   tempdb..sysobjects
           WHERE  id = Object_id('tempdb..#Rls'))
  DROP TABLE #Rls

IF 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 @CidINT
DECLARE @flag INT
DECLARE @Count INT

SET @flag = 1

SELECT @Count = Count(Rid)
FROM   Cgs
WHERE  Rid = @Rkey

WHILE ( @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
  END

INSERT 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.Fopr
FROM   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.Dsid
WHERE  br.Atve= 1
       AND br.Brid = @Brid 
       
                                  



DECLARE @dlm CHAR

SET @dlm = ','

SELECT Rid,
       Dsid,
       Brid,
       Fnme,
       CompareFrom,
       FCprs,
       Fopr,
       CompareTo,
       Passed
INTO   #FCprss
FROM   (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

^{Low-hanging fruit picker and defender of the moggies}

For better assistance in answering your questions, please read this.

Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Post #1378046

curious_sqldba

Posted Sunday, October 28, 2012 7:17 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:13 PM
Points: 1,172, Visits: 2,687

ChrisM@home (10/28/2012)

Where in this code do you populate table #sid?

sqldba_newbie (10/28/2012)



DECLARE @CurrentDateTime DATETIME

SET @CurrentDateTime = Getdate()

DECLARE @Nth AS BIT
DECLARE @Aus INT
DECLARE @Ante NVARCHAR(255)
DECLARE @Atid INT
DECLARE @Adid INT
DECLARE @Brid INT
DECLARE @Tid INT
DECLARE @TeID INT
DECLARE @Rkey NVARCHAR(20)

SET @Rkey= '123456'

--Get Exception Info                                                  
SELECT @Tid = Tid
FROM   Synonym_Tes
WHERE  [ID] = @TeID

SELECT TOP(1)@Atid = Tid
FROM   Synonym_Tasks
WHERE  [Name] = 'Azn'

SET @Nth = 0

--  SET @Adid= 64    
SELECT @Adid= brt.Dsid,
       @Brid = Id
FROM   Synonym_Brt brt
WHERE  brt.Rtn = 'Part 1'

---- Is an auth needed?                              
IF EXISTS (SELECT 1
           FROM   tempdb..sysobjects
           WHERE  id = Object_id('tempdb..#Fds'))
  DROP TABLE #Fds

IF EXISTS (SELECT 1
           FROM   tempdb..sysobjects
           WHERE  id = Object_id('tempdb..#Rls'))
  DROP TABLE #Rls

IF EXISTS (SELECT 1
           FROM   tempdb..sysobjects
           WHERE  id = Object_id('tempdb..#Mtcs'))
  DROP TABLE #Mtcs
  
  
  
 CREATE TABLE #Sid
    (
       ID INT
    )
    

SET @i = 1

WHILE @i <= 8000
  BEGIN
      INSERT INTO #Sid
      VALUES      (@i)

      SET @i = @i + 1
  END

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 @CidINT
DECLARE @flag INT
DECLARE @Count INT

SET @flag = 1

SELECT @Count = Count(Rid)
FROM   Cgs
WHERE  Rid = @Rkey

WHILE ( @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
  END

INSERT 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.Fopr
FROM   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.Dsid
WHERE  br.Atve= 1
       AND br.Brid = @Brid 
       
                                  



DECLARE @dlm CHAR

SET @dlm = ','

SELECT Rid,
       Dsid,
       Brid,
       Fnme,
       CompareFrom,
       FCprs,
       Fopr,
       CompareTo,
       Passed
INTO   #FCprss
FROM   (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

my bad...i have updated the code now...thanks

Post #1378073

curious_sqldba

Posted Tuesday, October 30, 2012 9:52 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:13 PM
Points: 1,172, Visits: 2,687

any thoughts?

Post #1378869

GSquared

Posted Tuesday, October 30, 2012 12:38 PM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1378961

curious_sqldba

Posted Tuesday, October 30, 2012 1:16 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:13 PM
Points: 1,172, Visits: 2,687

GSquared (10/30/2012)

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

Post #1378988

Sean Lange

Posted Tuesday, October 30, 2012 1:43 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 8,620, Visits: 8,261

sqldba_newbie (10/30/2012)

GSquared (10/30/2012)

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.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1379003

GSquared

Posted Wednesday, October 31, 2012 6:23 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

sqldba_newbie (10/30/2012)

GSquared (10/30/2012)

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.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1379277

ChrisM@Work

Posted Wednesday, October 31, 2012 8:09 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

sqldba_newbie (10/28/2012)

my bad...i have updated the code now...thanks

Where? Without knowing how #sid is populated, it's impossible to do what you are asking.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1379329

curious_sqldba

Posted Wednesday, October 31, 2012 9:02 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:13 PM
Points: 1,172, Visits: 2,687

ChrisM@Work (10/31/2012)

sqldba_newbie (10/28/2012)

my bad...i have updated the code now...thanks

Where? Without knowing how #sid is populated, it's impossible to do what you are asking.

CREATE TABLE #Sid
(
ID INT
)

SET @i = 1

WHILE @i <= 8000
BEGIN
INSERT INTO #Sid
VALUES (@i)

SET @i = @i + 1
END

It is also in the code.

Post #1379370

« Prev Topic | Next Topic »