April 27, 2009 at 1:54 pm
This is strictly not SQL-Server issue.
Here is the situation.
We import and store 7 months data, one table for one month based on transaction date. The table number and the corresponding month values are stored in a table. Depending on the transaction date, the data gets loaded into the corresponding table.
There is a query which determines the table number and loads it into the import table. The problem I am having is if the transaction date is outside the range of 7 tables, then for that record, a null value is assigned but I want to move spaces instead.
Here is the query
UPDATE s
SET s.ProdTableSuffix =
(SELECT r.DataTable
FROM FSDataMartReference.dbo.tblRefDataTableLocations r
WHERE r.DataSourceCode = 4
AND CAST(r.DataMonth AS INTEGER) = MONTH(s.TranDate)
AND r.DataYear = YEAR(s.TranDate))
FROM tblFalcon002DebitImport AS s
I tried using "(SELECT isnull(r.DataTable, '') as DataTable" but it didn't work.
I can live with null value but curious to know if there is any way the null value can be replaced with spaces or if the select statement can be tweaked so that only if there is a match for trandate, the import table is updated. The current query updates the import table even when there is no match for the trandate.
Thanks in advance for your time.
Peter
April 27, 2009 at 2:38 pm
Peter,
Try this:
UPDATE s
SET s.ProdTableSuffix = ISNULL((SELECT
r.DataTable
FROM
FSDataMartReference.dbo.tblRefDataTableLocations r
WHERE
r.DataSourceCode = 4 AND
CAST(r.DataMonth AS INTEGER) = MONTH(s.TranDate) AND
r.DataYear = YEAR(s.TranDate)), '')
FROM
tblFalcon002DebitImport AS s
You had the ISNULL inside the SELECT and the SELECT was NOT returning any rows which is why you were getting nulls. By wrapping the SELECT with ISNULL you get the spaces when no rows are returned.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2009 at 7:39 am
Thank you very much. It worked like a charm.
Peter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply