January 28, 2010 at 2:16 am
I'm trying to insert the results of pivoting a table into an existing database table which does not allow null values. Not surprisingly, the insert fails because the pivot table can return a null value.
Can anyone show me the syntax (if it exists) for setting a default value so that the insert will work. I know I could just change the database so that it accepts null for those fields, but I don't want to affect the integrity of the table/database.
Here is the syntax that does the pivot. If I find that, say the [03] column has a null value, the insert command fails.
SELECT
*
FROM (
SELECT
staffID,
activityID,
financialYearFromSAP,
weekFromSAP,
totalHours,
apFromSAP [AP]
FROM @Import) Imported
PIVOT (
SUM(totalHours)
FOR [AP] IN (
[01],
[02],
[03],
[04],
[05],
[06],
[07],
[08],
[09],
[10],
[11],
[12]
)
) PivotTable
All contributions gratefullly received 🙂
January 28, 2010 at 3:42 am
January 28, 2010 at 4:03 am
Thank you Grasshopper - that has done the trick! :satisfied::-D
I suspected I'd have to use 'isnull', but didn't know where. Someone in the office suggested 'coalesce' as an alternative.
Hope this helps others out, as I've seen similar questions go unanswered on other forums (fora).
January 28, 2010 at 4:30 am
My pleasure.
Coalesce will work as well. ISNULL is said to be a tiny bit more efficient, coalesce on the other hand is ANSI standard...
A difference with ISNULL is that coalesce allows more than 2 arguments, e.g.
select coalesce(col1, col2, col3, col4, etc.)
will return the first value (from left to right) that is not NULL (or NULL if they are all NULL).
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply