help with this query!!!!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » help with this query!!!!

13 posts, Page 2 of 2

««1 2

help with this query!!!!

Rate Topic

Display Mode

Topic Options

Author

Message

Sean Lange

Posted Wednesday, October 31, 2012 9:30 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233

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

Post #1379393

ChrisM@Work

Posted Wednesday, October 31, 2012 1:30 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:00 AM
Points: 5,609, Visits: 10,971

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

Sean Lange

Posted Wednesday, October 31, 2012 1:50 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233

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

Post #1379512

« Prev Topic | Next Topic »

13 posts, Page 2 of 2

««1 2

Permissions