I am trying to write a query that groups records based on the local date part only of a UTC datetime field. For example, if my table contains 10/19/2012 2:00:00, then it should get grouped as 10/18/2012, since my local time is EST (-5h) and I'm only interested in the date portion of the field. I know I can use DateAdd(day, DateDiff(day, 0, MyDate), 0) to get the date part only from the datetime field, and I can use DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate) to convert a UTC datetime to a local date time. But combining the two is seriously offending me. Is there a better way than this to get just the Date part of a UTC DateTime field, converted to local time, in SQL Server 2005? SELECT DateAdd(day, DateDiff(day, 0, DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate)), 0) , Count(*) FROM MyTable GROUP BY DateAdd(day, DateDiff(day, 0, DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate)), 0)

I'm back again with more dumb questions. This time I am dealing with refactoring a single select with nested case expressions in the where clause. I'm trying to optimize this, and in attempting to do so, I broke up the query into multiple selects w/ union alls. This does run quite a bit faster, but now I'm seeing more rows return than before, and this does not seem to be due to duplicate rows. There is more to this query, like creating the temp tables, etc. However, as far as I can tell, that has not had an effect on the result set. I have been able to determine that the second select of the refactored query (much of which is omitted because it is repetitive) is pulling in more results than before. So, where did I go wrong when pulling this apart? How does my new query/WHERE clause(s) differ from the original? I super appreciate your help in advance. /*Original*/ select /*some columns*/ from #SmallerRange IPS inner join #LargerRange V on V.AccountNumber !=IPS.AccountNumber and V.InOrOut = IPS.InOrOut and V.LocNum = IPS.LocNum WHERE CASE WHEN IPS.InOrOut = 'I' THEN CASE WHEN IPS.EntryDateTime = V.EntryDateTime AND IPS.ExitDateTime = V.ExitDateTime AND IPS.ProgramID = V.ProgramID THEN 1 WHEN IPS.EntryDateTime = V.EntryDateTime AND IPS.OriginDate=V.OriginDate AND IPS.ProgramID = V.ProgramID THEN 1 WHEN IPS.EntryDateTime = V.EntryDateTime AND IPS.ExitDateTime = V.ExitDateTime AND IPS.OriginDate=V.OriginDate THEN 1 WHEN IPS.ProgramID = V.ProgramID AND IPS.ExitDateTime = V.ExitDateTime AND IPS.OriginDate=V.OriginDate THEN 1 WHEN IPS.Name = V.Name AND IPS.Name 'N/A' AND IPS.ExitDateTime = V.ExitDateTime AND IPS.OriginDate=V.OriginDate THEN 1 ELSE 0 END WHEN IPS.InOrOut = 'O' THEN CASE WHEN IPS.ProgramID = V.ProgramID AND IPS.OriginDate=V.OriginDate AND IPS.DropbyDateTime = V.DropbyDateTime THEN 1 WHEN IPS.ProgramID = V.ProgramID AND IPS.DropbyDateTime = V.DropbyDateTime AND IPS.ARRVLTIME = V.ARRVLTIME THEN 1 WHEN IPS.ProgramID = V.ProgramID AND IPS.OriginDate=V.OriginDate AND IPS.ARRVLTIME = V.ARRVLTIME THEN 1 WHEN IPS.DropbyDateTime = V.DropbyDateTime AND IPS.OriginDate=V.OriginDate AND IPS.ARRVLTIME = V.ARRVLTIME THEN 1 WHEN IPS.DropbyDateTime = V.DropbyDateTime AND IPS.OriginDate=V.OriginDate AND IPS.Name = V.Name THEN 1 ELSE 0 END ELSE 0 END = 1 AND CASE WHEN V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name V.Name THEN 0 WHEN V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name = 'N/A' THEN 0 ELSE 1 END = 1 /*Refactor*/ select /*some columns*/ from #SmallerRange IPS inner join #LargerRange V on V.InOrOut = IPS.InOrOut and V.LocNum = IPS.LocNum and V.AccountNumber != IPS.AccountNumber WHERE IPS.InOrOut = 'I' and IPS.EntryDate = V.EntryDate AND IPS.OriginDate=V.OriginDate AND IPS.ProgramID = V.ProgramID and not (V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name V.Name and IPS.Name = 'N/A') and not (V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name = 'N/A') union all select /*some columns*/ from #SmallerRange IPS inner join #LargerRange V on V.InOrOut = IPS.InOrOut and V.LocNum = IPS.LocNum and V.AccountNumber != IPS.AccountNumber WHERE IPS.InOrOut = 'I' and IPS.EntryDate = V.EntryDate AND IPS.ExitDate = V.ExitDate AND IPS.ProgramID = V.ProgramID and not (V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name V.Name and IPS.Name = 'N/A') and not (V.ProgramID LIKE 'PC%' and V.ProgramID = IPS.ProgramID and IPS.Name = 'N/A')

Hi DBA Stack Exchange! Today I tried Apex SQL Refactor and right from the get go, once installed and integrated into my T-SQL dev tool of Choice (SSMS 18.3.1) I noticed that some Keyboard shortcuts stopped working. One example (maybe there are more, but this one I’m pretty sure): After installing and using SQL Refactor for first time (Refactor needs to be used at least once for this to happen) the “Available Databases” functionality in SSMS (which can be invoked by CTRL+U) no longer works. What is worse, this is not a configurable shortcut (like most of the Keyboard shortcuts that can be configured on Tools -> Options -> Keyboard) so once it is broken, the only solution so far (to make it work again) is to uninstall SQL Refactor. This query shortcut (“Available Databases”) is an extremely useful one that people use it over and over again on SQL Sessions, so breaking the shortcut is a major thing for me. I’m running the latest available builds of both SSMS (18.3.1) and Apex SQL Refactor (2018.08.0364) **My question is: have you also experienced this? If so, how did you solve the issue of keyboard shortcuts being irreparably lost after Apex SQL Refactor?** If you need me to provide any additional info about my system or logs that could help you reproduce this issue, just let me know. Thanks to all of you!

I'm creating a new staging database, into which I intend to migrate several tables from the old database. These tables are referenced in functions and stored procedures. If I were doing things manually, I would just migrate the tables, and then modify the functions by replacing the old table name with the new table name. I'm thinking there must be a more automated way of doing this though. Has anyone done something similar; what tools have you used to help refactor?

I have a fairly big postgresql **jobs** table of **(more than 60 attributes)** and one critical query contains joins with this table of million records. and I cannot reduce its size(paritioning by range which is introduced recently in postgresql 10). Only 10 fields are required for this critical query(All other 50 fields when joining are a burden). is there any way that I can only join with smaller table(with 10 fields only, Because I think pg loads all the fields when applying join) when running this critical query but in all of the remaining app, I would be able to fully use this table. E.g jobs table fields critical for query are: cost, id, user_id, params, location and the fields which are not required in this query are quite a lot. Note: If I create two different tables then I had to update a lot of code everywhere

My problem seems like it should have a much simpler solution than what I have come up with. Starting with this data set: **log_table** +--------+-----------+------------------+---------+ | log_id | entity_id | date | comment | +--------+-----------+------------------+---------+ | 1 | A | 2012-10-23 07:50 | foo | | 2 | B | 2012-10-23 07:59 | bar | | 3 | B | 2012-10-23 08:11 | baz | | 4 | A | 2012-10-23 08:23 | bat | +--------+-----------+------------------+---------+ Say I wanted to get the *latest* date of log entries for each entity so that the result looked like: Results: +-----------+------------------+--------------+ | entity_id | last_log_date | last_comment | +-----------+------------------+--------------+ | B | 2012-10-23 08:11 | baz | | A | 2012-10-23 08:23 | bat | +-----------+------------------+--------------+ I'm currently using MySQL that looks something like: SELECT entity_id, date AS last_log_date, comment AS last_comment FROM ( SELECT * FROM log_table ORDER BY date DESC, log_id ASC ) AS ordered_log GROUP BY entity_id This works fine but it doesn't seem very efficient to me, there *has* to be a better way of doing this, surely?

I have the following oracle SQL and its works and all but it's quite ugly with all of the ors. Is there a more concise way of doing this? SELECT * FROM foobar WHERE (SUBJECT ='STAT' and TERM ='111') or (SUBJECT ='STAT' and TERM ='222') or (SUBJECT ='ENGLISH' and TERM ='555') or (SUBJECT ='COMM' and TERM ='444') or (SUBJECT ='COMM' and TERM ='333') or (SUBJECT ='STAT' and TERM ='666') ...

I have a simple table called events It has a date, start time, end time and status I have multiple events for the same date, start and end. To know if someone can make an appointment, I have to check if for that date and start, end I have a line that the status is false (not occupied). The table definition with some example code: CREATE SEQUENCE events_id_seq; CREATE TABLE events( id int4 NOT NULL DEFAULT nextval('events_id_seq'::regclass), event_date date NULL, status bool NULL DEFAULT false, event_start time NULL, event_end time NULL ) ; INSERT INTO events(event_date,status,event_start,event_end) VALUES ('2016-10-10', TRUE, '08:00:00','08:30:00'), ('2016-10-10', TRUE, '08:00:00','08:30:00'), ('2016-10-10', FALSE, '08:00:00','08:30:00'), ('2016-10-10', TRUE, '08:00:00','08:30:00'), ('2016-10-10', TRUE, '08:30:00','09:00:00'), ('2016-10-10', FALSE, '08:30:00','09:00:00'), ('2016-10-10', FALSE, '08:30:00','09:00:00'), ('2016-10-10', FALSE, '08:30:00','09:00:00'); select distinct event_date, event_start, event_end, ( select count(*) from events e1 where e1.status = false and e1.event_date = e.event_date and e1.event_start = e.event_start and e1.event_end = e.event_end ) as free_slots from events e order by event_date, event_start; http://sqlfiddle.com/#!15/698b7 It works, but since I'm not a SQL expert, I am just seeking advice on other forms to solve this, just to learn a little more.

I've inherited a database which contains several procedures which are 1000-1500 lines long, with complex nested sub-selects going up to 7 or 8 levels deep in places. I desperately need to refactor them for my own sanity, but how can I begin to do this with any level of confidence that they still work the same? I would write unit tests if this were .Net - do you recommend a similar approach?

My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any suggestions on indexes would be much appreciated also. SELECT available - ( SELECT COALESCE(sum(quantity), 0) FROM product_locks WHERE sku = 'sku1' ) - ( SELECT COALESCE(sum(quantity), 0) FROM orderlineitems INNER JOIN responses_authnets ON responses_authnets.id = orderlineitems.response_id WHERE sku = 'sku1' AND responses_authnets.salesite_id = 'site_id_1' ) AS free, available AS total, sku, on_hold FROM product_inventories WHERE sku = 'sku1' AND salesite_id = 'site_id_1';

I have the task of re-factoring a SQL script that performs a lot of unions from different tables. These tables have similar information, so the script queries the same columns as in each table, except that the table names are different. For example: customerTransaction2011_Tbl |ID | T.ID | Amount | |---|------|--------| CustomerTransaction2012_Tbl |ID | T.ID | Amount | |---|------|--------| CustomerTransaction2013_Tbl |ID | T.ID | Amount | |---|------|--------| The SQL script I have to re-factor is below: Select ID, Sum(Amount), '2011' as [Year] into #Tbl_threeYear From CustomerTransaction2011_Tbl union all Select ID, Sum(Amount), '2012' as [Year] From CustomerTransaction2012_Tbl union all Select ID, Sum(Amount), '2013' as [Year] From CustomerTransaction2013_Tbl; To re-factor this, I had thought of using

SQL

within a

procedure

, so that I could pass the years (2011, 2012, 2013) as parameters to the stored procedure, which would append these to the Dynamic SQL string. But turns out I can't be given permission to create a stored procedure. So 2 questions, please: If I simply make use of

SQL

like so declare @sqlmain as varchar(500); declare @tblPart as varchar(100); set @tblPart = '2011'; set @sqlmain = 'select ID, sum(Amount) from customerTransaction' + @tblPart + '_Tbl;' How could I perform a union over the results from

(sqlmain)

while simply changing the @tblPart variable - must be possible somehow? Also, would you rather another way of re-factoring such a script? Since this is my first time re-factoring, I would more than welcome any suggestions/criticism. Perhaps anybody could suggest how to re-factor in basic SQL? This is all being done in SQL Server 2005. Much appreciated.

Are these two queries logically equivalent? DECLARE @DateTime DATETIME = GETDATE() Query 1 SELECT * FROM MyTable WHERE Datediff(DAY, LogInsertTime, @DateTime) > 7 Query 2 SELECT * FROM MyTable WHERE LogInsertTime < @DateTime - 7 If they are not logically equivalent, can you give me the logical equivalent of the first query so that the WHERE clause can effectively use an index (i.e. eliminate function wrapping)?

Okay... I have a table that has customers: -- Individual Table * ID (Internal Unique ID) * IndividualID (External Unique Individual Identifier) * Last Name * First Name * Birth Date * SSN * ... The issue is that sometimes a person gets multiple Individual ID's. Say the person doesn't provide a SSN for one Encounter, the last name changes, typo in birthday, etc. So you end up with the same person in the individual table multiple times: 1, Frost, Jack, 1/1/2000, 000-00-0008 2, Frost, Jack, 1/1/2000, 000-00-0003 3, Doe, Jane, 1/1/2000, 000-00-0005 4, Doe, Janet, 1/1/2000, 000-00-0005 5, Frost, Janet, 1/1/2000, 000-00-0005 Those are just some examples. The basic idea is that I need to find individuals that are potential matches, so that the right person can merge the individuals into a single account. The particular query I'm currently on is on SS2008-SP1, but I have other queries on SS2005 through SS2012. Is there any way I can improve this? Initially I had a single select statement (instead of 2 temp tables, 5 inserts and a select statement), but the "This or This or This or..." took many minutes and this takes ~10 seconds. Population of Customers is ~144k (Select count(*) from Data) Current I'm using a simple attempt to try and match four parts: Last Name, First Name, DOB, SSN. If 3 or 4 of them match on different individuals, the need to be inspected closer to determine if they really are the same person. IF object_id('tempdb..#DATA') IS NOT NULL DROP TABLE #DATA; GO CREATE TABLE #DATA ( EXTID VARCHAR(30) NOT NULL , LNAME VARCHAR(30) NULL , FNAME VARCHAR(30) NULL , SSN VARCHAR(11) NULL , DOB VARCHAR(8) NULL ) GO INSERT INTO #DATA SELECT EXTID = D1.EXTERNALID , LNAME = D1.LASTNAME , FNAME = D1.FIRSTNAME , SSN = CASE WHEN D1.SSN = '000-00-0000' THEN NULL ELSE D1.SSN END , DOB = convert(VARCHAR, D1.DOB, 112) FROM Data D1 WHERE Type = 1 and STATUS = 1 GO SELECT D1.*, [Splitter] = 'MATCH', D2.* FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID AND ( D1.LNAME = D2.LNAME AND D1.FNAME = D2.FNAME AND D1.SSN = D2.SSN AND D1.DOB = D2.DOB) UNION SELECT D1.*, 'LName', D2.* FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID AND ( D1.LNAME D2.LNAME AND D1.FNAME = D2.FNAME AND D1.SSN = D2.SSN AND D1.DOB = D2.DOB) UNION SELECT D1.*, 'FName', D2.* FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID AND ( D1.LNAME = D2.LNAME AND D1.FNAME D2.FNAME AND D1.SSN = D2.SSN AND D1.DOB = D2.DOB) UNION SELECT D1.*, 'SSN ', D2.* FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID AND ( D1.LNAME = D2.LNAME AND D1.FNAME = D2.FNAME AND D1.SSN D2.SSN AND D1.DOB = D2.DOB) UNION SELECT D1.*, 'DOB ', D2.* FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID AND ( D1.LNAME = D2.LNAME AND D1.FNAME = D2.FNAME AND D1.SSN = D2.SSN AND D1.DOB D2.DOB); Edit to add Distinct Counts: LName FName SSN DOB Count 36737 14539 115073 34284 144044 Edit: Cleaned up a bit to get rid of second temp table. Poking around the Estimated Execution plan, the above query - broken into 5 parts - uses hash map inner joins and takes about 10 seconds. My initial query, and other variations seem to use loop joins and is still chugging along at 10+ minutes.

I have the following query select ps.id, ps.title , IFNULL(s.likes,0) as num_likes , IFNULL(s.comments,0) as num_comments , IFNULL(s.ratings,0) as num_ratings , IFNULL(s.views,0) as num_views , IFNULL(s.avg_rating,0) as avg_rating from ps left outer join ( select presiid, sum(views) as views, sum(likes) as likes, sum(ratings) as ratings, avg(ratings) as avg_rating, sum(downloads) as downloads, sum(comments) as comments, sum(embeds) as embeds, sum(shares) as shares from tblstatistics group by presiid ) s on s.presiid = ps.id where ps.active = 1 order by datepublished desc LIMIT 0, 12 Running EXPLAIN on the above shows this 1 PRIMARY ps ref active active 1 const 402 Using temporary; Using filesort 1 PRIMARY ALL NULL NULL NULL NULL 334 2 DERIVED tblstatistics ALL NULL NULL NULL NULL 2643 Using temporary; Using filesort As you can see this is not good....tblstatistics is not using any indexes and is scanning the entire table. How can I optimize this query and make it use indexes? There are over 100,000 rows in the DB on the production DB where this query runs. Any help will be appreciated....

I need to find the ids of all rows in a table A without a matching row on table B. Following the answer on this question I'm using a left join like this: select A.id from A left join B on B.id_A = A.id where B.id_A is null; And it works perfectly well. However now I need to do the same query with an additional conditional for the matching, meaning I need to find the ids of all rows in a table A without a matching row on table B whose column c has the value x. If I try something like: select A.id from A left join B on B.id_A = A.id where B.id_A is null and B.c = x; It obviously gives me an empty result set. So far, the only way I figured to do that is using a subquery for B with a "not exists" clause: select A.id from A where not exists (select id from B where B.id_A = A.id and B.c = x); Any ideas on how to do this with a join without using subqueries?

Sorry for the confusing title, I wasn't sure what to write there. I have a table of a few hundred records. I need to assign each record of this table to a much smaller dynamic table of users, and the users should alternate as to what records they get assigned. For example, if TableA is

Row_Number()    Id
1               1
2               2
3               3
4               4
5               5
6               6
7               7
8               8
9               9
10              10

and TableB is

Row_Number()    Id
1               1
2               2
3               3

I need an end result set that is

UserId          RecordId
1               1
2               2
3               3
1               4
2               5
3               6
1               7
2               8
3               9
1               10

I've managed to do something a bit messily using the mod operator, but I was curious if this same query could be run without the temp table and the variable. The temp table is used because TableA is actually a User Defined Function that converts a comma-delimited string to a table, and I need the Count of the objects from the UDF. -- Converts a comma-delimited string into a table SELECT Num as [UserId], Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo] INTO #tmpTest FROM dbo.StringToNumSet('2,3,1', ',') DECLARE @test int SELECT @test = Count(*) FROM #tmpTest SELECT * FROM #tmpTest as T1 INNER JOIN ( SELECT Top 10 Id, Row_Number() OVER (ORDER BY SomeDateTime) as [RowNo] FROM TableA WITH (NOLOCK) ) as T2 ON T1.RowNo = (T2.RowNo % @test) + 1 Its important that the UserIds alternate too. I cannot assign the top 1/3 of the records to User1, second 1/3 of the records to User2, and 3rd 1/3 of the records to User3. Also, the UserIds need to maintain the order in which they were originally entered in, which is why I have a Row_Number() OVER (ORDER BY (SELECT 1)) in the User's table Is there a way of joining these tables in a single query so I won't need to use a temp table and variable? I'm using SQL Server 2005

Note: I work with MSSQL 2008, but I guess it's valid for many others DB engines I have this table "Users":

UserID	User	CountryID
1     user 1	1
2     user 2	2
3     user 3	3
4     user 4	4
5     user 5	4
6     user 6	3

And this table "Countries"

CountryID	Country
1	        MX
2	        USA
3	        CAN
4	        ENGLAND

As you can see, every user belongs to a country. If I want to know, all the different countries where I have at least, one user on the Users table, right now I do this query: select distinct country from Users inner join countries on users.CountryID=countries.CountryID And achieve the next result set:

CAN
ENGLAND
MX
USA 

Which is indeed, all the different countries, where I have at least one user on muy table Users. My doubt is, is possible to achieve the above result set, with out using "DISTINCT", I mean only using JOINS and conditions ? Here it's de DDL scripts:

USE [TEST]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 09/21/2012 16:21:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[UserID] [int] NULL,
	[User] [nvarchar](50) NULL,
	[CountryID] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (1, N'user 1', 1)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (2, N'user 2', 2)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (3, N'user 3', 3)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (4, N'user 4', 4)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (5, N'user 5', 4)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (6, N'user 6', 3)
/****** Object:  Table [dbo].[Countries]    Script Date: 09/21/2012 16:21:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Countries](
	[CountryID] [int] NULL,
	[Country] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (1, N'MX')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (2, N'USA')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (3, N'CAN')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (4, N'ENGLAND')
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (5, N'BRAZIL')

The data is being compared from the local version of the table to one in the contracts linked server. I'm not certain what the goal of this code is in the first place, but I'm trying to get a handle on simplifying it a little. CURSOR c3 IS SELECT bs.billing_service_id, bs.oser_id FROM (SELECT oser_id FROM other_services MINUS SELECT oser_id FROM other_services@contracts) a, billing_service bs WHERE bs.oser_id = a.oser_id AND bs.sent = 0 AND bs.billing_service_id NOT IN ( SELECT bd.billing_service_id FROM billing_details bd); -- snip FOR c_rec3 IN c3 LOOP DELETE FROM billing_service WHERE billing_service_id = c_rec3.billing_service_id; END LOOP; Seems to me that this could be naively refactored (without fooling with any other issues it has like the implicit joins and other garbage) simply as: DELETE FROM billing_service WHERE billing_service_id IN ( SELECT bs.billing_service_id FROM (SELECT oser_id FROM other_services MINUS SELECT oser_id FROM other_services@contracts) a, billing_service bs WHERE bs.oser_id = a.oser_id AND bs.sent = 0 AND bs.billing_service_id NOT IN ( SELECT bd.billing_service_id FROM billing_details bd) ); I don't think it's a significant amount of data in these tables - about 26000 rows on each side, and I don't think the set of differences is that big of a subset of the two. But I was wondering if there are issues with DB Links which make cursors preferable to regular set-oriented methods? This operation is part of a stored procedure which is run from an Oracle job on a daily basis.

I'm struggling with a case statement not working. Simple enough I need to supply a value of 0 where the question marks are. I have several other case statements which handle separate columns, but this is the simplest. I have used case statements with NULLS before and no issue. My best guess is the OUTER APPLY is screwing with things, but I can't find any documentation to back that up. SELECT TOP 20 GIVE_BACK1.CLUB_TOT 'B1_TOT', GC.CLUB_USER_VARBL3 FROM SUMMIT.CLUB_MBRSP GC OUTER APPLY ( SELECT CASE WHEN B1.CLUB_USER_VARBL3 IS NULL THEN '?????????????????????????????' ELSE B1.CLUB_TOT END AS 'CLUB_TOT' FROM SUMMIT.CLUB_MBRSP B1 WHERE GC.ID = B1.ID AND ( ( GC.CLUB_USER_VARBL3 != 'LT' AND B1.CLUB_USER_VARBL3 = LEFT(GC.CLUB_USER_VARBL3,2) + CONVERT(varchar(2),(CONVERT(INT,RIGHT(GC.CLUB_USER_VARBL3,3))-1)) ) ) ) GIVE_BACK1 This query yields this result set NULL FY88 NULL FY10 NULL FY01 NULL FY99 NULL FY97 NULL FY99 NULL FY97 NULL FY02 NULL FY01 17525.00 FY85 NULL FY84 ... Any help getting the case statement working, and explanation of the issue or a refactoring would be most appreciated.