November 23, 2009 at 10:33 pm
Hi,
I have a query to check if any of the databases on the reporting server did not restore. I have a lookup table to compare against with 33 country codes. In case I have only 32 databases with names like 'HotDog__XYZ', the query would still return nothing, which is probably caused by the where clause. If I select the database names into a table variable and then join with the Country table everything works fine, but this is a bit cumbersome way of doing this.
Is there a way to do it in one query like the one below?
Thanks.
SELECT [CountryCode], sd.name
FROM MyDb.dbo.Country c
LEFT JOIN MASTER.dbo.sysdatabases sd (NOLOCK)
ON c.CountryCode = RIGHT(LEFT(sd.NAME, 8), 2)
WHERE sd.NAME LIKE 'HotDog__XYZ' AND sd.name IS NULL
November 23, 2009 at 10:41 pm
I'm confused, how is it you are expecting the name to be equal to something and nothing at the same time? You're comparing the same field to 2 completely different things using AND.
IE, If 1=1 and 1<>1
That uh... isn't ever going to happen.
Can you post the working table variable code so we can see what you're trying to do?
November 23, 2009 at 10:43 pm
Given that the WHERE clause includes criteria for the table on the 'right' side of the 'left' outer join, I think you've created the equivalent of an inner join... savvy?
November 23, 2009 at 10:45 pm
DECLARE @dblist TABLE (dbname NVARCHAR(100))
INSERT INTO @dblist (dbname)
SELECT sd.name FROM MASTER.dbo.sysdatabases sd (NOLOCK)
WHERE sd.NAME LIKE 'HotDog__XYZ'
IF EXISTS (
SELECT [CountryCode]
FROM DBUtilities.dbo.Country c
left JOIN @dblist sd
ON c.CountryCode = RIGHT(LEFT(sd.dbname, 8), 2)
WHERE sd.dbname IS NULL
)
BEGIN
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
PRINT 'Start'
SELECT
@ErrorMessage = 'The job could not restore some or all of the databases',
@ErrorSeverity = 21,
@ErrorState = 1;
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
END
November 23, 2009 at 10:47 pm
SJTerrill (11/23/2009)
Given that the WHERE clause includes criteria for the table on the 'right' side of the 'left' outer join, I think you've created the equivalent of an inner join... savvy?
Yep, it seems to be so, but I need the criteria for the right side table.
November 23, 2009 at 10:49 pm
Garadin (11/23/2009)
I'm confused, how is it you are expecting the name to be equal to something and nothing at the same time? You're comparing the same field to 2 completely different things using AND.IE, If 1=1 and 1<>1
That uh... isn't ever going to happen.
Can you post the working table variable code so we can see what you're trying to do?
Posted it above. The second condition is not essential, the main problem is the "inner" join equivalent.
November 23, 2009 at 10:53 pm
One option is removing the criterion from the WHERE clause and adding it to the JOIN clause as an AND, though I haven't looked closely at your syntax.
--SJT--
P.S. This might make it perform more like a subquery.
November 23, 2009 at 11:00 pm
SJTerrill (11/23/2009)
One option is removing the criterion from the WHERE clause and adding it to the JOIN clause as an AND, though I haven't looked closely at your syntax.--SJT--
P.S. This might make it perform more like a subquery.
Thanks, that worked!
This is a new working version:
SELECT [CountryCode], sd.name
FROM MyDB.dbo.Country c
LEFT JOIN MASTER.dbo.sysdatabases sd (NOLOCK)
ON c.CountryCode = RIGHT(LEFT(sd.NAME, 8), 2) AND sd.NAME LIKE 'HotDog__XYZ'
WHERE sd.name IS NULL
November 23, 2009 at 11:04 pm
DECLARE @dblist TABLE (dbname NVARCHAR(100))
INSERT INTO @dblist (dbname)
SELECT sd.name FROM MASTER.dbo.sysdatabases sd (NOLOCK)
WHERE sd.NAME LIKE 'HotDog__XYZ'
IF EXISTS (
SELECT [CountryCode]
FROM DBUtilities.dbo.Country c
left JOIN @dblist sd
ON c.CountryCode = RIGHT(LEFT(sd.dbname, 8), 2)
WHERE sd.dbname IS NULL
)
Wait a minute... just like t'other poster remarked: there doesn't appear to be any way the EXISTS could return anything but False. Outer join notwithstanding, we have first c.CountyCode = sd.dbname = 'GB' (example) [implicit AND] WHERE sd.dbname IS NULL?
--SJT--
November 23, 2009 at 11:09 pm
That seems wrong to me still. I think it should probably be:
SELECT [CountryCode], sd.name
FROM MyDB.dbo.Country c
LEFT JOIN MASTER.dbo.sysdatabases sd (NOLOCK)
ON c.CountryCode = RIGHT(LEFT(sd.NAME, 8), 2) AND C.somefield LIKE something
WHERE sd.name IS NULL
Edit: The point is, if the left join is coming from anything it needs to come from Country... otherwise the additional criteria is useless, as you're filtering out everything but the nulls anyways. Also, the 'that seems wrong' comment was to a post like 3 up, not the one immediately above. Having internet issues atm.
November 23, 2009 at 11:10 pm
Danger, Roust... your LIKE statement isn't using the % wildcard. Something deeper going on here?
November 23, 2009 at 11:16 pm
SJTerrill (11/23/2009)
Danger, Roust... your LIKE statement isn't using the % wildcard. Something deeper going on here?
What danger? My databases are called similar to HotDogAUXYZ for Australia, HotDogDEXYZ for Germany etc. That is why I am comparing against HotDog__XYZ. % wildcard could return something like HotDogABCXYZ.
November 23, 2009 at 11:26 pm
Well, if your LIKE search isn't using a wildcard, could it not use = 'HomeRun_ZYX' ? If you're not searching string content, that is.
I think the LIKE option/function/operator might introduce a performance issue on larger rowsets whether or not the wildcard is used in the search. Sort of, 'Use it if you have to.'
--SJT--
November 23, 2009 at 11:30 pm
SJTerrill (11/23/2009)
Well, if your LIKE search isn't using a wildcard, could it not use = 'HomeRun_ZYX' ? If you're not searching string content, that is.
Sorry, did not understand this one.
November 23, 2009 at 11:49 pm
Well, my understanding of LIKE parses the entirety of a string, beginning to end, looking for a pattern match given or not given the wildcards... It's entirely possible that the expression WHERE 'THIS' LIKE 'THIS' is equivalent in execution to WHERE 'THIS' = 'THIS'. My everyday usage of LIKE would result in something more like WHERE 'THIS' LIKE '%' + 'thatTHISTheOther' + '%'... which would return True whereas WHERE 'ThIs' LIKE '%' + 'MOO' + '%' would return False. Were I looking for a specific string value I'd use =. 'THIS' = 'THIS' vs. 'THIS' = 'THAT'.
My gut tells me that using LIKE might result in a table scan of varchar or nvarchar values whereas an equality (=) operator might use an available index... but I haven't researched it.
--SJT--
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply