Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

help with this query!!!! Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 9:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 11,941, Visits: 10,975
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 Moden's 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)
Post #1379393
Posted Wednesday, October 31, 2012 1:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 6,748, Visits: 12,843
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1379503
Posted Wednesday, October 31, 2012 1:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 11,941, Visits: 10,975
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 Moden's 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)
Post #1379512
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse