public void ConfurationIsGood() {
var composer = new PipelineComposer();
var controller = composer.Compose(@"Files\PersonAndPet.xml");
Assert.AreEqual(0, composer.Root.Errors().Count());
Assert.AreEqual(0, composer.Root.Warnings().Count());
}
public void ConfurationIsGood()
{
var composer = new PipelineComposer();
var controller = composer.Compose(@"Files\PersonAndPet.xml");
Assert.AreEqual(0, composer.Root.Errors().Count());
Assert.AreEqual(0, composer.Root.Warnings().Count());
}
public void InventoryTesting()
{
var composer = new PipelineComposer();
var controller = composer.Compose(@"C:\temp\Inventory.xml?Mode=init");
controller.PreExecute();
controller.Execute();
controller.PostExecute();
}
public void InventoryTesting() {
var composer = new PipelineComposer();
var controller = composer.Compose(@"C:\temp\Inventory.xml?Mode=init");
controller.PreExecute();
controller.Execute();
controller.PostExecute();
}
//[Ignore("Integration testing")]
public void NorthwindIntegrationTesting() {
var composer = new PipelineComposer();
var controller = composer.Compose(@"Files\Northwind.xml", LogLevel.Info);
controller.PreExecute();
controller.Execute();
controller.PostExecute();
Assert.IsNotNull(composer);
}
//[Ignore("Integration testing")]
public void NorthwindIntegrationTesting()
{
var composer = new PipelineComposer();
var controller = composer.Compose(@"Files\Northwind.xml", LogLevel.Info);
controller.PreExecute();
controller.Execute();
controller.PostExecute();
Assert.IsNotNull(composer);
}
public void MapTransformAdd() {
var xml = @"
<cfg>
<processes>
<add name='TestProcess'>
<data-sets>
<add name='TestData'>
<rows>
<add Field1='1' Field3='^' />
<add Field1='2' Field3='#' />
<add Field1='3' Field3='$THREE$' />
<add Field1='4' Field3='@' />
</rows>
</add>
</data-sets>
<maps>
<add name='Map'>
<items>
<add from='1' to='One' />
<add from='2' to='Two' />
<add from='3' parameter='Field3' />
</items>
</add>
</maps>
<entities>
<add name='TestData' pipeline='streams'>
<fields>
<add name='Field1' />
<add name='Field2' />
<add name='Field3' />
</fields>
<calculated-fields>
<add name='Map' t='copy(Field1).map(map)' default='None' />
</calculated-fields>
</add>
</entities>
</add>
</processes>
</cfg>";
var composer = new PipelineComposer();
var controller = composer.Compose(xml);
var field = composer.Process.Entities.First().CalculatedFields.First();
var output = controller.Run().ToArray();
Assert.AreEqual("One", output[0][field]);
Assert.AreEqual("Two", output[1][field]);
Assert.AreEqual("$THREE$", output[2][field]);
Assert.AreEqual("None", output[3][field]);
}
public void MapTransformAdd()
{
var xml = @"
<cfg>
<processes>
<add name='TestProcess'>
<data-sets>
<add name='TestData'>
<rows>
<add Field1='1' Field3='^' />
<add Field1='2' Field3='#' />
<add Field1='3' Field3='$THREE$' />
<add Field1='4' Field3='@' />
</rows>
</add>
</data-sets>
<maps>
<add name='Map'>
<items>
<add from='1' to='One' />
<add from='2' to='Two' />
<add from='3' parameter='Field3' />
</items>
</add>
</maps>
<entities>
<add name='TestData' pipeline='streams'>
<fields>
<add name='Field1' />
<add name='Field2' />
<add name='Field3' />
</fields>
<calculated-fields>
<add name='Map' t='copy(Field1).map(map)' default='None' />
</calculated-fields>
</add>
</entities>
</add>
</processes>
</cfg>";
var composer = new PipelineComposer();
var controller = composer.Compose(xml);
var field = composer.Process.Entities.First().CalculatedFields.First();
var output = controller.Run().ToArray();
Assert.AreEqual("One", output[0][field]);
Assert.AreEqual("Two", output[1][field]);
Assert.AreEqual("$THREE$", output[2][field]);
Assert.AreEqual("None", output[3][field]);
}
public void Validator()
{
const string xml = @"
<cfg>
<processes>
<add name='TestSignature'>
<data-sets>
<add name='TestData'>
<rows>
<add args='10,0' />
</rows>
</add>
</data-sets>
<entities>
<add name='TestData'>
<fields>
<add name='args' length='128'>
<transforms>
<add method='fromsplit' separator=','>
<fields>
<add name='TotalWidth' />
<add name='PaddingChar' />
</fields>
</add>
</transforms>
</add>
</fields>
<calculated-fields>
<add name='length' type='int' t='copy(args).splitlength(\,)' />
<add name='TotalWidthCheck' type='string' t='copy(TotalWidth).is(int)' />
</calculated-fields>
</add>
</entities>
</add>
</processes>
</cfg>";
var composer = new PipelineComposer();
var controller = composer.Compose(xml);
var process = composer.Process;
var output = controller.Run().ToArray();
var field = process.Entities.First().CalculatedFields.First(cf => cf.Name == "length");
Assert.AreEqual(2, output[0][field]);
foreach (var row in output)
{
Console.WriteLine(row);
}
}
public void ContainsValidator() {
var xml = @"
<cfg>
<processes>
<add name='TestProcess'>
<data-sets>
<add name='TestData'>
<rows>
<add Field1='11' Field2='12' Field3='13' />
</rows>
</add>
</data-sets>
<entities>
<add name='TestData' pipeline='streams'>
<fields>
<add name='Field1' />
<add name='Field2' />
<add name='Field3' />
</fields>
<calculated-fields>
<add name='c1' type='bool' t='copy(Field1).contains(1)' />
<add name='c2' type='string' t='copy(Field1).contains(2)' />
<add name='c3' type='bool' t='copy(Field2).contains(1).contains(2)' />
<add name='c4' type='bool' t='copy(Field2).contains(1).contains(3)' />
<add name='c5' t='copy(Field2).contains(1).contains(2)' />
<add name='c6' t='copy(Field2).contains(1).contains(3)' />
</calculated-fields>
</add>
</entities>
</add>
</processes>
</cfg>
".Replace('\'', '"');
var composer = new PipelineComposer();
var controller = composer.Compose(xml);
var output = controller.Run().ToArray();
var process = composer.Root.Processes.First();
Assert.AreEqual(true, output[0][process.Entities.First().CalculatedFields.First(cf => cf.Name == "c1")]);
Assert.AreEqual("Field1 does not contain 2.", output[0][process.Entities.First().CalculatedFields.First(cf=>cf.Name=="c2")]);
Assert.AreEqual(true, output[0][process.Entities.First().CalculatedFields.First(cf => cf.Name == "c3")]);
Assert.AreEqual(false, output[0][process.Entities.First().CalculatedFields.First(cf => cf.Name == "c4")]);
Assert.AreEqual(string.Empty, output[0][process.Entities.First().CalculatedFields.First(cf => cf.Name == "c5")]);
Assert.AreEqual("Field2 does not contain 3.", output[0][process.Entities.First().CalculatedFields.First(cf => cf.Name == "c6")]);
}
public void ContainsValidator()
{
var xml = @"
<cfg>
<processes>
<add name='TestProcess'>
<data-sets>
<add name='TestData'>
<rows>
<add Field1='11' Field2='12' Field3='13' />
</rows>
</add>
</data-sets>
<entities>
<add name='TestData' pipeline='streams'>
<fields>
<add name='Field1' />
<add name='Field2' />
<add name='Field3' />
</fields>
<calculated-fields>
<add name='c1' type='bool' t='copy(Field1).contains(1)' />
<add name='c2' type='string' t='copy(Field1).contains(2)' />
<add name='c3' type='bool' t='copy(Field2).contains(1).contains(2)' />
<add name='c4' type='bool' t='copy(Field2).contains(1).contains(3)' />
<add name='c5' t='copy(Field2).contains(1).contains(2)' />
<add name='c6' t='copy(Field2).contains(1).contains(3)' />
</calculated-fields>
</add>
</entities>
</add>
</processes>
</cfg>
".Replace('\'', '"');
var composer = new PipelineComposer();
var controller = composer.Compose(xml);
var output = controller.Run().ToArray();
var process = composer.Root.Processes.First();
Assert.AreEqual(true, output[0][process.Entities.First().CalculatedFields.First(cf => cf.Name == "c1")]);
Assert.AreEqual("Field1 does not contain 2.", output[0][process.Entities.First().CalculatedFields.First(cf => cf.Name == "c2")]);
Assert.AreEqual(true, output[0][process.Entities.First().CalculatedFields.First(cf => cf.Name == "c3")]);
Assert.AreEqual(false, output[0][process.Entities.First().CalculatedFields.First(cf => cf.Name == "c4")]);
Assert.AreEqual(string.Empty, output[0][process.Entities.First().CalculatedFields.First(cf => cf.Name == "c5")]);
Assert.AreEqual("Field2 does not contain 3.", output[0][process.Entities.First().CalculatedFields.First(cf => cf.Name == "c6")]);
}
public void Validator() {
const string xml = @"
<cfg>
<processes>
<add name='TestSignature'>
<data-sets>
<add name='TestData'>
<rows>
<add args='10,0' />
</rows>
</add>
</data-sets>
<entities>
<add name='TestData'>
<fields>
<add name='args' length='128'>
<transforms>
<add method='fromsplit' separator=','>
<fields>
<add name='TotalWidth' />
<add name='PaddingChar' />
</fields>
</add>
</transforms>
</add>
</fields>
<calculated-fields>
<add name='length' type='int' t='copy(args).splitlength(\,)' />
<add name='TotalWidthCheck' type='string' t='copy(TotalWidth).is(int)' />
</calculated-fields>
</add>
</entities>
</add>
</processes>
</cfg>";
var composer = new PipelineComposer();
var controller = composer.Compose(xml);
var process = composer.Process;
var output = controller.Run().ToArray();
var field = process.Entities.First().CalculatedFields.First(cf => cf.Name == "length");
Assert.AreEqual(2, output[0][field]);
foreach (var row in output) {
Console.WriteLine(row);
}
}
public void JavascriptTransformAdd() {
var xml = @"
<cfg>
<processes>
<add name='TestProcess'>
<data-sets>
<add name='TestData'>
<rows>
<add Field1='1' Field2='2' Field3='3' />
</rows>
</add>
</data-sets>
<entities>
<add name='TestData' pipeline='streams'>
<fields>
<add name='Field1' />
<add name='Field2' />
<add name='Field3' />
</fields>
<calculated-fields>
<add name='Format' t='copy(Field1,Field2,Field3).javascript(Field1+Field2+Field3)' />
</calculated-fields>
</add>
</entities>
</add>
</processes>
</cfg>
".Replace('\'', '"');
var composer = new PipelineComposer();
var controller = composer.Compose(xml);
var output = controller.Run().ToArray();
Assert.AreEqual("123", output[0][composer.Process.Entities.First().CalculatedFields.First()]);
}
public void JavascriptTransformAdd()
{
var xml = @"
<cfg>
<processes>
<add name='TestProcess'>
<data-sets>
<add name='TestData'>
<rows>
<add Field1='1' Field2='2' Field3='3' />
</rows>
</add>
</data-sets>
<entities>
<add name='TestData' pipeline='streams'>
<fields>
<add name='Field1' />
<add name='Field2' />
<add name='Field3' />
</fields>
<calculated-fields>
<add name='Format' t='copy(Field1,Field2,Field3).javascript(Field1+Field2+Field3)' />
</calculated-fields>
</add>
</entities>
</add>
</processes>
</cfg>
".Replace('\'', '"');
var composer = new PipelineComposer();
var controller = composer.Compose(xml);
var output = controller.Run().ToArray();
Assert.AreEqual("123", output[0][composer.Process.Entities.First().CalculatedFields.First()]);
}
public void FieldsAreIndexed() {
var composer = new PipelineComposer();
composer.Compose(@"Files\PersonAndPet.xml");
var pet = composer.Process.Entities.First();
var person = composer.Process.Entities.Last();
Assert.AreEqual(0, pet.Fields[0].Index);
Assert.AreEqual(1, pet.Fields[1].Index);
Assert.AreEqual(2, pet.Fields[2].Index);
Assert.AreEqual(3, pet.Fields[3].Index);
Assert.AreEqual(4, pet.Fields[4].Index);
Assert.AreEqual(5, pet.CalculatedFields[0].Index);
Assert.AreEqual(6, pet.CalculatedFields[1].Index);
Assert.AreEqual("Id", person.Fields[0].Name);
Assert.AreEqual(0, person.Fields[0].Index);
Assert.AreEqual(1, person.Fields[1].Index);
Assert.AreEqual(2, person.Fields[2].Index);
Assert.AreEqual(3, person.Fields[3].Index);
Assert.AreEqual(4, person.CalculatedFields[0].Index);
}
public void FieldsAreIndexed()
{
var composer = new PipelineComposer();
composer.Compose(@"Files\PersonAndPet.xml");
var pet = composer.Process.Entities.First();
var person = composer.Process.Entities.Last();
Assert.AreEqual(0, pet.Fields[0].Index);
Assert.AreEqual(1, pet.Fields[1].Index);
Assert.AreEqual(2, pet.Fields[2].Index);
Assert.AreEqual(3, pet.Fields[3].Index);
Assert.AreEqual(4, pet.Fields[4].Index);
Assert.AreEqual(5, pet.CalculatedFields[0].Index);
Assert.AreEqual(6, pet.CalculatedFields[1].Index);
Assert.AreEqual("Id", person.Fields[0].Name);
Assert.AreEqual(0, person.Fields[0].Index);
Assert.AreEqual(1, person.Fields[1].Index);
Assert.AreEqual(2, person.Fields[2].Index);
Assert.AreEqual(3, person.Fields[3].Index);
Assert.AreEqual(4, person.CalculatedFields[0].Index);
}
public void StarSql() {
var composer = new PipelineComposer();
var controller = composer.Compose(@"Files\Northwind.xml");
Assert.AreEqual(0, composer.Root.Errors().Length);
var pipe = new PipelineContext(new TraceLogger(), composer.Process);
var actual = new SqlFormattingManager().Format(pipe.SqlCreateStarView());
Assert.IsNotNull(controller);
const string expected = @"CREATE VIEW [NorthWindStar]
AS
SELECT A.[A1] AS [OrderDetailsDiscount]
,A.[A2] AS [OrderDetailsOrderID]
,A.[A3] AS [OrderDetailsProductID]
,A.[A4] AS [OrderDetailsQuantity]
,A.[A5] AS [OrderDetailsRowVersion]
,A.[A6] AS [OrderDetailsUnitPrice]
,A.[A7] AS [OrderDetailsExtendedPrice]
,A.[A8] AS [TflHashCode]
,A.[A9] AS [CountryExchange]
,A.[B1] AS [OrdersCustomerID]
,A.[C1] AS [CustomersAddress]
,A.[C2] AS [CustomersCity]
,A.[C3] AS [CustomersCompanyName]
,A.[C4] AS [CustomersContactName]
,A.[C5] AS [CustomersContactTitle]
,A.[C6] AS [CustomersCountry]
,A.[C8] AS [CustomersFax]
,A.[C9] AS [CustomersPhone]
,A.[C10] AS [CustomersPostalCode]
,A.[C11] AS [CustomersRegion]
,A.[C12] AS [CustomersRowVersion]
,A.[B2] AS [OrdersEmployeeID]
,A.[E8] AS [ProductsSupplierID]
,A.[E1] AS [ProductsCategoryID]
,A.[B15] AS [OrdersShipVia]
,ISNULL(B.[B3], 0.0) AS [OrdersFreight]
,ISNULL(B.[B4], '12/31/9999 11:59:59 PM') AS [OrdersOrderDate]
,ISNULL(B.[B6], '12/31/9999 11:59:59 PM') AS [OrdersRequiredDate]
,ISNULL(B.[B8], '') AS [OrdersShipAddress]
,ISNULL(B.[B9], '') AS [OrdersShipCity]
,ISNULL(B.[B10], '') AS [OrdersShipCountry]
,ISNULL(B.[B11], '') AS [OrdersShipName]
,ISNULL(B.[B12], '12/31/9999 11:59:59 PM') AS [OrdersShippedDate]
,ISNULL(B.[B13], '') AS [OrdersShipPostalCode]
,ISNULL(B.[B14], '') AS [OrdersShipRegion]
,ISNULL(B.[B16], '12-DEC') AS [TimeOrderMonth]
,ISNULL(B.[B17], '9999-12-31') AS [TimeOrderDate]
,ISNULL(B.[B18], '9999') AS [TimeOrderYear]
,ISNULL(D.[D1], '') AS [EmployeesAddress]
,ISNULL(D.[D2], '12/31/9999 11:59:59 PM') AS [EmployeesBirthDate]
,ISNULL(D.[D3], '') AS [EmployeesCity]
,ISNULL(D.[D4], '') AS [EmployeesCountry]
,ISNULL(D.[D6], '') AS [EmployeesExtension]
,ISNULL(D.[D7], '') AS [EmployeesFirstName]
,ISNULL(D.[D8], '12/31/9999 11:59:59 PM') AS [EmployeesHireDate]
,ISNULL(D.[D9], '') AS [EmployeesHomePhone]
,ISNULL(D.[D10], '') AS [EmployeesLastName]
,ISNULL(D.[D11], '') AS [EmployeesNotes]
,ISNULL(D.[D14], '') AS [EmployeesPostalCode]
,ISNULL(D.[D15], '') AS [EmployeesRegion]
,ISNULL(D.[D17], '') AS [EmployeesTitle]
,ISNULL(D.[D18], '') AS [EmployeesTitleOfCourtesy]
,ISNULL(D.[D19], 0) AS [EmployeesReportsTo]
,ISNULL(D.[D20], '') AS [EmployeesManager]
,ISNULL(D.[D21], '') AS [Employee]
,ISNULL(E.[E2], 0) AS [ProductsDiscontinued]
,ISNULL(E.[E4], '') AS [ProductsProductName]
,ISNULL(E.[E5], '') AS [ProductsQuantityPerUnit]
,ISNULL(E.[E6], 0) AS [ProductsReorderLevel]
,ISNULL(E.[E9], 0.0) AS [ProductsUnitPrice]
,ISNULL(E.[E10], 0) AS [ProductsUnitsInStock]
,ISNULL(E.[E11], 0) AS [ProductsUnitsOnOrder]
,ISNULL(F.[F1], '') AS [SuppliersAddress]
,ISNULL(F.[F2], '') AS [SuppliersCity]
,ISNULL(F.[F3], '') AS [SuppliersCompanyName]
,ISNULL(F.[F4], '') AS [SuppliersContactName]
,ISNULL(F.[F5], '') AS [SuppliersContactTitle]
,ISNULL(F.[F6], '') AS [SuppliersCountry]
,ISNULL(F.[F7], '') AS [SuppliersFax]
,ISNULL(F.[F8], '') AS [SuppliersHomePage]
,ISNULL(F.[F9], '') AS [SuppliersPhone]
,ISNULL(F.[F10], '') AS [SuppliersPostalCode]
,ISNULL(F.[F11], '') AS [SuppliersRegion]
,ISNULL(G.[G2], '') AS [CategoriesCategoryName]
,ISNULL(G.[G3], '') AS [CategoriesDescription]
,ISNULL(H.[H1], '') AS [ShippersCompanyName]
,ISNULL(H.[H2], '') AS [ShippersPhone]
,A.TflBatchId
,A.TflKey
FROM [NorthWindOrder DetailsTable] A WITH (NOLOCK)
LEFT OUTER JOIN [NorthWindOrdersTable] B WITH (NOLOCK) ON (A.A2 = B.B5)
LEFT OUTER JOIN [NorthWindCustomersTable] C WITH (NOLOCK) ON (A.B1 = C.C7)
LEFT OUTER JOIN [NorthWindEmployeesTable] D WITH (NOLOCK) ON (A.B2 = D.D5)
LEFT OUTER JOIN [NorthWindProductsTable] E WITH (NOLOCK) ON (A.A3 = E.E3)
LEFT OUTER JOIN [NorthWindSuppliersTable] F WITH (NOLOCK) ON (A.E8 = F.F13)
LEFT OUTER JOIN [NorthWindCategoriesTable] G WITH (NOLOCK) ON (A.E1 = G.G1)
LEFT OUTER JOIN [NorthWindShippersTable] H WITH (NOLOCK) ON (A.B15 = H.H4);
";
Assert.AreEqual(expected, actual);
}
public void StarSql()
{
var composer = new PipelineComposer();
var controller = composer.Compose(@"Files\Northwind.xml");
Assert.AreEqual(0, composer.Root.Errors().Length);
var pipe = new PipelineContext(new TraceLogger(), composer.Process);
var actual = new SqlFormattingManager().Format(pipe.SqlCreateStarView());
Assert.IsNotNull(controller);
const string expected = @"CREATE VIEW [NorthWindStar]
AS
SELECT A.[A1] AS [OrderDetailsDiscount]
,A.[A2] AS [OrderDetailsOrderID]
,A.[A3] AS [OrderDetailsProductID]
,A.[A4] AS [OrderDetailsQuantity]
,A.[A5] AS [OrderDetailsRowVersion]
,A.[A6] AS [OrderDetailsUnitPrice]
,A.[A7] AS [OrderDetailsExtendedPrice]
,A.[A8] AS [TflHashCode]
,A.[A9] AS [CountryExchange]
,A.[B1] AS [OrdersCustomerID]
,A.[C1] AS [CustomersAddress]
,A.[C2] AS [CustomersCity]
,A.[C3] AS [CustomersCompanyName]
,A.[C4] AS [CustomersContactName]
,A.[C5] AS [CustomersContactTitle]
,A.[C6] AS [CustomersCountry]
,A.[C8] AS [CustomersFax]
,A.[C9] AS [CustomersPhone]
,A.[C10] AS [CustomersPostalCode]
,A.[C11] AS [CustomersRegion]
,A.[C12] AS [CustomersRowVersion]
,A.[B2] AS [OrdersEmployeeID]
,A.[E8] AS [ProductsSupplierID]
,A.[E1] AS [ProductsCategoryID]
,A.[B15] AS [OrdersShipVia]
,ISNULL(B.[B3], 0.0) AS [OrdersFreight]
,ISNULL(B.[B4], '12/31/9999 11:59:59 PM') AS [OrdersOrderDate]
,ISNULL(B.[B6], '12/31/9999 11:59:59 PM') AS [OrdersRequiredDate]
,ISNULL(B.[B8], '') AS [OrdersShipAddress]
,ISNULL(B.[B9], '') AS [OrdersShipCity]
,ISNULL(B.[B10], '') AS [OrdersShipCountry]
,ISNULL(B.[B11], '') AS [OrdersShipName]
,ISNULL(B.[B12], '12/31/9999 11:59:59 PM') AS [OrdersShippedDate]
,ISNULL(B.[B13], '') AS [OrdersShipPostalCode]
,ISNULL(B.[B14], '') AS [OrdersShipRegion]
,ISNULL(B.[B16], '12-DEC') AS [TimeOrderMonth]
,ISNULL(B.[B17], '9999-12-31') AS [TimeOrderDate]
,ISNULL(B.[B18], '9999') AS [TimeOrderYear]
,ISNULL(D.[D1], '') AS [EmployeesAddress]
,ISNULL(D.[D2], '12/31/9999 11:59:59 PM') AS [EmployeesBirthDate]
,ISNULL(D.[D3], '') AS [EmployeesCity]
,ISNULL(D.[D4], '') AS [EmployeesCountry]
,ISNULL(D.[D6], '') AS [EmployeesExtension]
,ISNULL(D.[D7], '') AS [EmployeesFirstName]
,ISNULL(D.[D8], '12/31/9999 11:59:59 PM') AS [EmployeesHireDate]
,ISNULL(D.[D9], '') AS [EmployeesHomePhone]
,ISNULL(D.[D10], '') AS [EmployeesLastName]
,ISNULL(D.[D11], '') AS [EmployeesNotes]
,ISNULL(D.[D14], '') AS [EmployeesPostalCode]
,ISNULL(D.[D15], '') AS [EmployeesRegion]
,ISNULL(D.[D17], '') AS [EmployeesTitle]
,ISNULL(D.[D18], '') AS [EmployeesTitleOfCourtesy]
,ISNULL(D.[D19], 0) AS [EmployeesReportsTo]
,ISNULL(D.[D20], '') AS [EmployeesManager]
,ISNULL(D.[D21], '') AS [Employee]
,ISNULL(E.[E2], 0) AS [ProductsDiscontinued]
,ISNULL(E.[E4], '') AS [ProductsProductName]
,ISNULL(E.[E5], '') AS [ProductsQuantityPerUnit]
,ISNULL(E.[E6], 0) AS [ProductsReorderLevel]
,ISNULL(E.[E9], 0.0) AS [ProductsUnitPrice]
,ISNULL(E.[E10], 0) AS [ProductsUnitsInStock]
,ISNULL(E.[E11], 0) AS [ProductsUnitsOnOrder]
,ISNULL(F.[F1], '') AS [SuppliersAddress]
,ISNULL(F.[F2], '') AS [SuppliersCity]
,ISNULL(F.[F3], '') AS [SuppliersCompanyName]
,ISNULL(F.[F4], '') AS [SuppliersContactName]
,ISNULL(F.[F5], '') AS [SuppliersContactTitle]
,ISNULL(F.[F6], '') AS [SuppliersCountry]
,ISNULL(F.[F7], '') AS [SuppliersFax]
,ISNULL(F.[F8], '') AS [SuppliersHomePage]
,ISNULL(F.[F9], '') AS [SuppliersPhone]
,ISNULL(F.[F10], '') AS [SuppliersPostalCode]
,ISNULL(F.[F11], '') AS [SuppliersRegion]
,ISNULL(G.[G2], '') AS [CategoriesCategoryName]
,ISNULL(G.[G3], '') AS [CategoriesDescription]
,ISNULL(H.[H1], '') AS [ShippersCompanyName]
,ISNULL(H.[H2], '') AS [ShippersPhone]
,A.TflBatchId
,A.TflKey
FROM [NorthWindOrder DetailsTable] A WITH (NOLOCK)
LEFT OUTER JOIN [NorthWindOrdersTable] B WITH (NOLOCK) ON (A.A2 = B.B5)
LEFT OUTER JOIN [NorthWindCustomersTable] C WITH (NOLOCK) ON (A.B1 = C.C7)
LEFT OUTER JOIN [NorthWindEmployeesTable] D WITH (NOLOCK) ON (A.B2 = D.D5)
LEFT OUTER JOIN [NorthWindProductsTable] E WITH (NOLOCK) ON (A.A3 = E.E3)
LEFT OUTER JOIN [NorthWindSuppliersTable] F WITH (NOLOCK) ON (A.E8 = F.F13)
LEFT OUTER JOIN [NorthWindCategoriesTable] G WITH (NOLOCK) ON (A.E1 = G.G1)
LEFT OUTER JOIN [NorthWindShippersTable] H WITH (NOLOCK) ON (A.B15 = H.H4);
";
Assert.AreEqual(expected, actual);
}
