0

My code is as follows:

Insert Into dbo.database (Period, Amount)
Select coalesce (date_1, date_2, date_3), Amount FROM Source.dbo.[10]

I'm 100% a value exists in one of the 3 variables: date_1, date_2, date_3, all as strings (var char 100), yet I am still getting blanks when I call Period.

Any help?

bummi
  • 27,123
  • 14
  • 62
  • 101
ZJAY
  • 301
  • 2
  • 4
  • 9
  • Are there date_1 and date_2 and date_3 in source.dbo.[10]? – Amir Keshavarz Oct 16 '13 at 23:48
  • Can you create a SQL fiddle demonstrating your problem? – John Tseng Oct 16 '13 at 23:52
  • 1
    The problem is, I believe the coalesce function is recognizing the date_1 blank as a value, rather than seeing nothing is there and skipping to date_2. All the dates are defined as strings with varchar 100. – ZJAY Oct 17 '13 at 00:00
  • 2
    Well, I think your first problem may be storing dates in string-type fields (and `VARCHAR(100)`? what?), and then using a _blank_ instead of null for 'no value'/'unknown'. Convert them to actual date/time/timestamp fields, and use nulls, and this problem'll clear right up... – Clockwork-Muse Oct 19 '13 at 23:09

1 Answers1

1

Coalesce is designed to return the first NOT NULL field from the list or NULL if none of the fields are NOT NULL, follow the link for full details http://msdn.microsoft.com/en-us/library/ms190349.aspx

I would guess that you have blank values (' ') in one of the columns instead of NULL values. If you are trying to find the first not null non-blank column you can use a case statement.

select 
 case
 when len(rtrim(ltrim(date_1))) > 0 then date_1
 when len(rtrim(ltrim(date_2))) > 0 then date_2
 when len(rtrim(ltrim(date_3))) > 0 then date_3
 else null
 end,
Amount
from Source.dbo.[10]
Kacey
  • 89
  • 1
  • 6
  • I think you have identified the problem, but is there a simpler way to skip over not null blank fields in the coalesce function? – ZJAY Oct 17 '13 at 00:05
  • Not really, coalesce is designed to find the first not null field. You'd have to either write a function to return blanks as NULL or case each field in the coalesce to return a null if the field only has a blank value, I'll update my answer with a link to the coalesce details. – Kacey Oct 17 '13 at 00:09
  • Thanks. The databases I am pulling from are very large. Would it be more efficient to do something like this:http://stackoverflow.com/questions/3701812/convert-empty-space-to-null – ZJAY Oct 17 '13 at 00:50
  • Or how about: SELECT IFNULL(NULLIF(col1,''),col2) – ZJAY Oct 17 '13 at 01:14
  • @Hype: really the best option is to use the date type, or a related type, instead of varchar. That would solve this problem once, instead of you having to solve it in every query. – siride Oct 20 '13 at 00:32