Last post Apr 18, 2014 10:04 PM by dotnetzoom
Apr 11, 2014 05:35 PM|DataOnCrack|LINK
Hi, I have a complex query and I am having trouble figuring out how to add some history records to it.
The existing query has an outer select, multiple sub selects in the select list and a Union All. For example:
When D.RedField = 'A' Then 'Alpha'
When D.RedField = 'B' Then 'Bravo' End) MyType,
(Select Max(CTable.BrownField) From CTable
From ATable, BTable
Where......legacy join city
(Select Max(ETable.BrownField) From ETable
From DTable, ETable
Kinda silly, but say the query above just returns 1 or 2 rows. But now, I need to add a column called ALL_BrownField, for example. So, instead of selecting max of the BrownField like it is doing now, it will pull the 3 previous brown field values. So ending
result list will be perhaps 5 rows. Ideally, all the field values will be the same except for Brownfield values ; changing for each row. Is this possible? Thanks
Apr 18, 2014 10:04 PM|dotnetzoom|LINK
Ideally, all the field values will be the same except for Brownfield values ; changing for each row. Is this possible?
I guess you can replace the query with max(BrownField) with a query containing join clause for all three tables.
From ATable A, BTable B, CTable C
WHERE A.ID=B.ID and C.SomeCondition='Something'
This query should repeat all values from A & B for each BrownField column in C. So, you could add additional conditions to filter out values for TableC according to your requirement.