SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help with this query!!!!


help with this query!!!!

Author
Message
curious_sqldba
curious_sqldba
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3026 Visits: 3637
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


ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2143 Visits: 10394
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





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
curious_sqldba
curious_sqldba
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3026 Visits: 3637
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
curious_sqldba
curious_sqldba
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3026 Visits: 3637
any thoughts?
GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24639 Visits: 9730
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
curious_sqldba
curious_sqldba
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3026 Visits: 3637
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27128 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24639 Visits: 9730
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17058 Visits: 19557
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
curious_sqldba
curious_sqldba
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3026 Visits: 3637
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search