protected void BuildColumnExpression(ISqlExpression expr, string alias, ref bool addAlias, bool wrapParameter) { var wrap = false; if (expr.SystemType == typeof(bool)) { if (expr is SelectQuery.SearchCondition) { wrap = true; } else { var ex = expr as SqlExpression; wrap = ex != null && ex.Expr == "{0}" && ex.Parameters.Length == 1 && ex.Parameters[0] is SelectQuery.SearchCondition; } } if (wrapParameter) { if (expr is SqlParameter) { if (((SqlParameter)expr).Name != null) { var dataType = SqlDataType.GetDataType(expr.SystemType); expr = new SqlFunction(expr.SystemType, dataType.DataType.ToString(), expr); } } else if ((expr is SqlValue) && ((SqlValue)expr).Value == null) { string colType = "CHAR"; if (expr.SystemType != null) { var actualType = SqlDataType.GetDataType(expr.SystemType); colType = DB2iSeriesMappingSchema.GetiSeriesType(actualType); } expr = new SqlExpression(expr.SystemType, "Cast({0} as {1})", Precedence.Primary, expr, new SqlExpression(colType, Precedence.Primary)); } } if (wrap) { StringBuilder.Append("CASE WHEN "); } base.BuildColumnExpression(expr, alias, ref addAlias); if (wrap) { StringBuilder.Append(" THEN 1 ELSE 0 END"); } }
private string NameWithCast(SqlDataType dataType, string value) { string colType = "CHAR"; if (dataType != null) { var actualType = SqlDataType.GetDataType(dataType.Type); colType = DB2iSeriesMappingSchema.GetiSeriesType(actualType); } return(string.Format("CAST({0} AS {1})", value, colType)); }
protected override void BuildInsertOrUpdateQueryAsMerge(string fromDummyTable) { var table = SelectQuery.Insert.Into; var targetAlias = Convert(SelectQuery.From.Tables[0].Alias, ConvertType.NameToQueryTableAlias).ToString(); var sourceAlias = Convert(GetTempAliases(1, "s")[0], ConvertType.NameToQueryTableAlias).ToString(); var keys = SelectQuery.Update.Keys; AppendIndent().Append("MERGE INTO "); BuildPhysicalTable(table, null); StringBuilder.Append(' ').AppendLine(targetAlias); AppendIndent().Append("USING (SELECT "); ExtractMergeParametersIfCannotCombine(keys); for (var i = 0; i < keys.Count; i++) { var key = keys[i]; var expr = key.Expression; if (expr is SqlParameter || expr is SqlValue) { var exprType = SqlDataType.GetDataType(expr.SystemType); var asType = DB2iSeriesMappingSchema.GetiSeriesType(exprType); StringBuilder.Append("CAST("); BuildExpression(expr, false, false); StringBuilder.AppendFormat(" AS {0})", asType); } else { BuildExpression(expr, false, false); } StringBuilder.Append(" AS "); BuildExpression(key.Column, false, false); if (i + 1 < keys.Count) { StringBuilder.Append(", "); } } if (!string.IsNullOrEmpty(fromDummyTable)) { StringBuilder.Append(' ').Append(fromDummyTable); } StringBuilder.Append(") ").Append(sourceAlias).AppendLine(" ON"); AppendIndent().AppendLine("("); Indent++; for (var i = 0; i < keys.Count; i++) { var key = keys[i]; AppendIndent(); StringBuilder.Append(targetAlias).Append('.'); BuildExpression(key.Column, false, false); StringBuilder.Append(" = ").Append(sourceAlias).Append('.'); BuildExpression(key.Column, false, false); if (i + 1 < keys.Count) { StringBuilder.Append(" AND"); } StringBuilder.AppendLine(); } Indent--; AppendIndent().AppendLine(")"); AppendIndent().AppendLine("WHEN MATCHED THEN"); Indent++; AppendIndent().AppendLine("UPDATE "); BuildUpdateSet(); Indent--; AppendIndent().AppendLine("WHEN NOT MATCHED THEN"); Indent++; BuildInsertClause("INSERT", false); Indent--; while (EndLine.Contains(StringBuilder[StringBuilder.Length - 1])) { StringBuilder.Length--; } }