/// <summary> /// Gets the reverse cross reference items query. /// </summary> /// <param name="join">The join.</param> /// <param name="referenceField">The reference field.</param> /// <param name="aliasCollection">The alias collection.</param> /// <returns>System.String.</returns> private string GetReverseCrossReferenceItemsQuery( ReverseCrossReferenceFieldDefinition join, ChecklistFieldDefinition referenceField, TableAliasCollection aliasCollection) { var result = new StringBuilder(); result.AppendFormat( @" {0} AS ( SELECT q1.""{1}"", q2.""{2}"" AS ""{7}"" FROM ""{3}"" q1 INNER JOIN ""{4}"" q2 ON q2.""{5}"" = q1.""{1}"" WHERE q1.""{6}"" = 0 )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), OracleNamesTranslator.Translate(Constants.IdColumnName), OracleNamesTranslator.Translate(GetJoinTableChildKey(referenceField)), OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName), OracleNamesTranslator.Translate(GetJoinTableName(referenceField)), OracleNamesTranslator.Translate(GetJoinTableMasterKey(referenceField)), OracleNamesTranslator.Translate(Constants.IsRemovedColumnName), OracleNamesTranslator.Translate(referenceField.SystemName)); return result.ToString(); }
/// <summary> /// Adds the reverse cross reference join. /// </summary> /// <param name="sql">The SQL.</param> /// <param name="join">The join.</param> /// <param name="aliasCollection">The alias collection.</param> private static void AddReverseCrossReferenceJoin( StringBuilder sql, ReverseCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection) { sql.AppendLine() .Append("\t") .AppendFormat( "INNER JOIN {0} ON {0}.\"{1}\" = {2}.\"{3}\"", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), OracleNamesTranslator.Translate(join.ReferenceField.SystemName), aliasCollection.GetTableAlias(join.DeclaringProcess), OracleNamesTranslator.Translate(Constants.IdColumnName)) .AppendLine() .Append("\t") .AppendFormat( "INNER JOIN \"{0}\" {1} ON {1}.\"{2}\" = {3}.\"{2}\"", OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName), aliasCollection.GetTableAlias(join.ReferencedProcess), OracleNamesTranslator.Translate(Constants.IdColumnName), aliasCollection.GetReverseCrossReferenceItemsQueryName(join)); }
private string GetReverseCrossReferenceItemsQuery( ReverseCrossReferenceFieldDefinition join, ChecklistFieldDefinition referenceField, TableAliasCollection aliasCollection) { var result = new StringBuilder(); result.AppendFormat( @" {0} AS ( SELECT q1.[{1}], q2.[{2}] AS [{7}] FROM [dbo].[{3}] q1 INNER JOIN [dbo].[{4}] q2 ON q2.[{5}] = q1.[{1}] WHERE q1.[{6}] = 0 )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), Constants.IdColumnName, GetJoinTableChildKey(referenceField), join.ReferencedProcess.SystemName, GetJoinTableName(referenceField), GetJoinTableMasterKey(referenceField), Constants.IsRemovedColumnName, referenceField.SystemName); return result.ToString(); }
/// <summary> /// Gets the name of the reverse cross reference items query. /// </summary> /// <param name="joinField">The join field.</param> /// <returns>System.String.</returns> public string GetReverseCrossReferenceItemsQueryName(ReverseCrossReferenceFieldDefinition joinField) { if (!_queryNames.ContainsKey(joinField)) _queryNames[joinField] = "RCR" + _queryIndex++; return _queryNames[joinField]; }
private static void AddReverseCrossReferenceJoin( StringBuilder sql, ReverseCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection) { sql.AppendLine() .Append("\t") .AppendFormat( "INNER JOIN {0} ON {0}.[{1}] = {2}.[{3}]", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), join.ReferenceField.SystemName, aliasCollection.GetTableAlias(join.DeclaringProcess), Constants.IdColumnName) .AppendLine() .Append("\t") .AppendFormat( "INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{2}]", join.ReferencedProcess.SystemName, aliasCollection.GetTableAlias(join.ReferencedProcess), Constants.IdColumnName, aliasCollection.GetReverseCrossReferenceItemsQueryName(join)); }