/// <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> /// 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 static string GetReverseCrossReferenceItemsQuery( SingleReverseCrossReferenceFieldDefinition join, SingleCrossReferenceFieldDefinition referenceField, TableAliasCollection aliasCollection) { var result = new StringBuilder(); if (!join.ShowLatestVersion) { result.AppendFormat( @" {0} AS ( SELECT MIN(""Id"") ""Id"", ""{1}"" FROM ""{2}"" WHERE ""IsRemoved"" = 0 GROUP BY ""{1}"" )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), OracleNamesTranslator.Translate(referenceField.SystemName), OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName)); } else { result.Append(GetResolveVersionMasterQuery(referenceField, aliasCollection)).AppendLine(",").AppendFormat( @" {0} AS ( SELECT MIN(""Id"") ""Id"" ,q1.""{1}"" FROM {2} q1 INNER JOIN ( SELECT MAX(""{3}"") ""{3}"" ,""{1}"" ,""{4}"" FROM {2} GROUP BY ""{1}"", ""{4}"" ) q2 ON q2.""{4}"" = q1.""{4}"" AND q2.""{3}"" = q1.""{3}"" AND q2.""{1}"" = q1.""{1}"" GROUP BY q1.""{1}"" )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), OracleNamesTranslator.Translate(referenceField.SystemName), aliasCollection.GetResolveVersionMasterQueryName(referenceField), OracleNamesTranslator.Translate(Constants.VersionNumber), OracleNamesTranslator.Translate(Constants.VersionMasterId)); } return result.ToString(); }
/// <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( MultiReverseCrossReferenceFieldDefinition join, MultiCrossReferenceFieldDefinition referenceField, TableAliasCollection aliasCollection) { var result = new StringBuilder(); if (referenceField.LinkField != null) { result.AppendFormat( @" {0} AS ( SELECT q1.""{1}"", q2.""{1}"" AS ""{2}"" FROM ""{3}"" q1 INNER JOIN ""{4}"" q2 ON q2.""{5}"" = q1.""{1}"" AND q2.""{6}"" = 0 WHERE q1.""{6}"" = 0 )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), OracleNamesTranslator.Translate(Constants.IdColumnName), OracleNamesTranslator.Translate(referenceField.SystemName), OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName), OracleNamesTranslator.Translate(join.DeclaringProcess.SystemName), OracleNamesTranslator.Translate(referenceField.LinkField.SystemName), OracleNamesTranslator.Translate(Constants.IsRemovedColumnName)); } else { if (!join.ShowLatestVersion) { 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)); } else { result.Append(GetResolveVersionMasterQuery(referenceField, aliasCollection)).AppendLine(","); result.AppendFormat( @" {0} AS ( SELECT MIN(""Id"") ""Id"" ,q1.""{1}"" AS ""{5}"" FROM {2} q1 INNER JOIN ( SELECT MAX(""{3}"") ""{3}"" ,""{1}"" ,""{4}"" FROM {2} GROUP BY ""{1}"", ""{4}"" ) q2 ON q2.""{4}"" = q1.""{4}"" AND q2.""{3}"" = q1.""{3}"" AND q2.""{1}"" = q1.""{1}"" GROUP BY q1.""{1}"", q1.""{4}"" )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), OracleNamesTranslator.Translate(GetJoinTableChildKey(referenceField)), aliasCollection.GetResolveVersionMasterQueryName(referenceField), OracleNamesTranslator.Translate(Constants.VersionNumber), OracleNamesTranslator.Translate(Constants.VersionMasterId), 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(); }
private string GetReverseCrossReferenceItemsQuery( MultiReverseCrossReferenceFieldDefinition join, MultiCrossReferenceFieldDefinition referenceField, TableAliasCollection aliasCollection) { var result = new StringBuilder(); if (referenceField.LinkField != null) { result.AppendFormat( @" {0} AS ( SELECT q1.[{1}], q2.[{1}] AS [{2}] FROM [dbo].[{3}] q1 INNER JOIN [dbo].[{4}] q2 ON q2.[{5}] = q1.[{1}] AND q2.[{6}] = 0 WHERE q1.[{6}] = 0 )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), Constants.IdColumnName, referenceField.SystemName, join.ReferencedProcess.SystemName, join.DeclaringProcess.SystemName, referenceField.LinkField.SystemName, Constants.IsRemovedColumnName); } else { if (!join.ShowLatestVersion) { 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); } else { result.Append(GetResolveVersionMasterQuery(referenceField, aliasCollection)).AppendLine(","); result.AppendFormat( @" {0} AS ( SELECT MIN([Id]) [Id] ,q1.[{1}] AS [{5}] FROM {2} q1 INNER JOIN ( SELECT MAX([{3}]) [{3}] ,[{1}] ,[{4}] FROM {2} GROUP BY [{1}], [{4}] ) q2 ON q2.[{4}] = q1.[{4}] AND q2.[{3}] = q1.[{3}] AND q2.[{1}] = q1.[{1}] GROUP BY q1.[{1}], q1.[{4}] )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), GetJoinTableChildKey(referenceField), aliasCollection.GetResolveVersionMasterQueryName(referenceField), Constants.VersionNumber, Constants.VersionMasterId, referenceField.SystemName); } } return result.ToString(); }
private static string GetReverseCrossReferenceItemsQuery( SingleReverseCrossReferenceFieldDefinition join, SingleCrossReferenceFieldDefinition referenceField, TableAliasCollection aliasCollection) { var result = new StringBuilder(); if (!join.ShowLatestVersion) { result.AppendFormat( @" {0} AS ( SELECT MIN([Id]) [Id], [{1}] FROM [dbo].[{2}] WHERE [IsRemoved] = 0 GROUP BY [{1}] )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), referenceField.SystemName, join.ReferencedProcess.SystemName); } else { result.Append(GetResolveVersionMasterQuery(referenceField, aliasCollection)).AppendLine(",").AppendFormat( @" {0} AS ( SELECT MIN([Id]) [Id] ,q1.[{1}] FROM {2} q1 INNER JOIN ( SELECT MAX([{3}]) [{3}] ,[{1}] ,[{4}] FROM {2} GROUP BY [{1}], [{4}] ) q2 ON q2.[{4}] = q1.[{4}] AND q2.[{3}] = q1.[{3}] AND q2.[{1}] = q1.[{1}] GROUP BY q1.[{1}] )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), referenceField.SystemName, aliasCollection.GetResolveVersionMasterQueryName(referenceField), Constants.VersionNumber, Constants.VersionMasterId); } return result.ToString(); }
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)); }