ORA-12704: character set mismatch
you should do select COALESCE (EMAIL, n’NO EMAIL’) from crmuser.accounts to convert the literal to NVARCHAR. eg http://sqlfiddle.com/#!4/73929/1 vs http://sqlfiddle.com/#!4/73929/2
coalesce
?? Null Coalescing Operator –> What does coalescing mean?
I’m tempted to lie and say that English is my second language…but the truth is that I just have no idea what ‘Coalescing’ means. I know what ?? ‘does’ in C#, but the name doesn’t make sense to me. I looked up the word and I understand it to be a synonym for ‘join’. I’d … Read more
Merge rows in a dataframe where the rows are disjoint and contain NAs
You can use aggregate. Assuming that you want to merge rows with identical values in column name: aggregate(x=DF[c(“v1″,”v2″,”v3″,”v4”)], by=list(name=DF$name), min, na.rm = TRUE) name v1 v2 v3 v4 1 Yemen 4 2 3 5 This is like the SQL SELECT name, min(v1) GROUP BY name. The min function is arbitrary, you could also use max … Read more
combine rows in data frame containing NA to make complete row
I haven’t figured out how to put the coalesce_by_column function inside the dplyr pipeline, but this works: coalesce_by_column <- function(df) { return(coalesce(df[1], df[2])) } df %>% group_by(A) %>% summarise_all(coalesce_by_column) ## A B C D E ## <dbl> <dbl> <dbl> <dbl> <dbl> ## 1 1 2 3 2 5 ## 2 2 4 5 3 4 … Read more
What is the “??” operator for? [duplicate]
It’s the null coalescing operator. It was introduced in C# 2. The result of the expression a ?? b is a if that’s not null, or b otherwise. b isn’t evaluated unless it’s needed. Two nice things: The overall type of the expression is that of the second operand, which is important when you’re using … Read more
Using ISNULL vs using COALESCE for checking a specific condition?
This problem reported on Microsoft Connect reveals some differences between COALESCE and ISNULL: an early part of our processing rewrites COALESCE( expression1, expression2 ) as CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. In [this example]: COALESCE ( ( SELECT Nullable FROM Demo WHERE SomeCol = 1 ), 1 ) we generate: … Read more
Best way to check for “empty or null value”
The expression stringexpression = ” yields: TRUE .. for ” (or for any string consisting of only spaces with the data type char(n)) NULL .. for NULL FALSE .. for anything else So to check for: “stringexpression is either NULL or empty”: (stringexpression = ”) IS NOT FALSE Or the reverse approach (may … Read more
How to concatenate columns in a Postgres SELECT?
With string type columns like character(2) (as you mentioned later), the displayed concatenation just works because, quoting the manual: […] the string concatenation operator (||) accepts non-string input, so long as at least one input is of a string type, as shown in Table 9.8. For other cases, insert an explicit coercion to text […] … Read more
How to get the first non-null value in Java?
Apache Commons Lang 3 ObjectUtils.firstNonNull(T…) Java 8 Stream Stream.of(T…).filter(Objects::nonNull).findFirst().orElse(null)