help with this query!!!!

  • 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

  • Where in this code do you populate table #sid?

    sqldba_newbie (10/28/2012)


    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


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

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

  • ChrisM@home (10/28/2012)


    Where in this code do you populate table #sid?

    sqldba_newbie (10/28/2012)


    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

    )

    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

  • any thoughts?

  • 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

  • GSquared (10/30/2012)


    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.

    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

  • sqldba_newbie (10/30/2012)


    GSquared (10/30/2012)


    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.

    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

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • sqldba_newbie (10/30/2012)


    GSquared (10/30/2012)


    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.

    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

    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

  • 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

  • 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.

  • You seem to be long on need of help and very short in supply of details.

    Here is the code you posted.

    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

    This is chock full of errors both syntactically and logically, but it seems you want to do something with this line.

    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.

    What do you want to do with it and why?

    _______________________________________________________________

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • sqldba_newbie (10/31/2012)


    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.

    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.

    “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

  • ChrisM@Work (10/31/2012)


    sqldba_newbie (10/31/2012)


    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.

    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.

    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.

    What's challenging for us is trying to figure out the problem, not the solution.

    +100 😀

    _______________________________________________________________

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply