-
Oct 30th, 2024, 09:09 AM
#1
Thread Starter
PowerPoster
Master Detail data - display data in Master row only if same data all Detail rows
I have a table in which I am creating m/d relationships between the rows.
SubJobControl is the key. SubJobControlParent in detail rows will be that key when they are related master/detail rows.
So let's say I have one master row and 10 detail rows. There is a column called UnitType. If UnitType is the same on all the Details then I want to display that value in the Master column too. If UnitType is a mix of Portable and Mobile for example, then UnitType column in the master row is to display dashes indicating they're not homogeneous.
I get my data from a stored procedure but then the m/d relationship is created in my C# code using DevExpress grids.
I am not sure if UnitType should be determined in the stored procedure or the application, but I am thinking the former, hence I am in this section of this forum!
How I know if it's a master row is if SerialNumber is null. So I'm thinking in the stored procedure I would have a CASE WHEN SerialNumber IS NULL THEN... somehow select the detail rows and determine homogeneous UnitType or not.
Any ideas on the "somehow"?
I am still googling for a solution, but I am stumbling on the right keywords to get a hit.
Thank you!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Oct 30th, 2024, 09:41 AM
#2
Thread Starter
PowerPoster
Re: Master Detail data - display data in Master row only if same data all Detail rows
I am going to propose an alternative solution because this might be beyond my sql skills.
We are starting out with rows in a table that are all "detail" rows; they have no master. Let's call these solo rows.
The user can select one or more solo rows in the application grid and pick a menu option "Create Lot". At that time a master row is created with a SubJobCOntrol that gets trickled down to all the selected solo rows' SUbJobCOntrolParent. So now we have one master row and let's say 5 detail rows that were selected. When I insert the master row into the database, I can see if all selected details have the same UnitType and if not blank it out. Then my stored procedure will need no additional logic.
There are also grid operations to add a solo row(s) to an existing lot. So if we have a master row that is UnitType Portable b/c all the details so far are portable but the solo(s) just chosen are not all Portable, then I would erase the UnitType.
There is a grid operation to remove a detail row from a lot, and again I could see if that would leave details that are homogenous and I would set the master UnitType.
Is the disadvantage of this approach that I am counting on my application to enforce rules on the database data? Or it doesn't matter becasue it is my application that is controlling all the master/detail relationships?
Last edited by MMock; Oct 30th, 2024 at 09:44 AM.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Oct 30th, 2024, 09:53 AM
#3
Re: Master Detail data - display data in Master row only if same data all Detail rows
Tested in SQLite
Code:
CREATE TABLE "tbl_mmock_parent" (
"ID" INTEGER,
"SomeField" TEXT NOT NULL,
PRIMARY KEY("ID")
);
CREATE TABLE "tbl_mmock_detail" (
"ID" INTEGER,
"UnitType" TEXT,
"SubJobControlParent" INTEGER,
PRIMARY KEY("ID"),
FOREIGN KEY("SubJobControlParent") REFERENCES "tbl_mmock_parent"("ID") ON DELETE CASCADE
);
Code:
INSERT INTO "main"."tbl_mmock_parent" ("ID", "SomeField") VALUES ('1', 'Entry 1');
INSERT INTO "main"."tbl_mmock_detail" ("ID", "UnitType", "SubJobControlParent") VALUES ('1', 'Portable ', '1');
INSERT INTO "main"."tbl_mmock_detail" ("ID", "UnitType", "SubJobControlParent") VALUES ('2', 'Portable ', '1');
INSERT INTO "main"."tbl_mmock_detail" ("ID", "UnitType", "SubJobControlParent") VALUES ('3', 'Portable ', '1');
INSERT INTO "main"."tbl_mmock_detail" ("ID", "UnitType", "SubJobControlParent") VALUES ('4', 'Portable ', '1');
INSERT INTO "main"."tbl_mmock_detail" ("ID", "UnitType", "SubJobControlParent") VALUES ('5', 'Portable ', '1');
INSERT INTO "main"."tbl_mmock_detail" ("ID", "UnitType", "SubJobControlParent") VALUES ('6', 'Portable ', '1');
INSERT INTO "main"."tbl_mmock_detail" ("ID", "UnitType", "SubJobControlParent") VALUES ('7', 'Portable ', '1');
INSERT INTO "main"."tbl_mmock_detail" ("ID", "UnitType", "SubJobControlParent") VALUES ('8', 'Portable ', '1');
INSERT INTO "main"."tbl_mmock_detail" ("ID", "UnitType", "SubJobControlParent") VALUES ('9', 'Portable ', '1');
INSERT INTO "main"."tbl_mmock_detail" ("ID", "UnitType", "SubJobControlParent") VALUES ('10', 'Portable ', '1');
Code:
WITH
MMD AS (SELECT DISTINCT SubJobControlParent, UnitType FROM tbl_mmock_detail),
MD As (SELECT SubJobControlParent, COUNT(DISTINCT UnitType) AS CntUT
FROM tbl_mmock_detail GROUP BY SubJobControlParent)
SELECT MP.ID, MP.SomeField, COALESCE(MMD.UnitType,'...') AS UnitType
FROM tbl_mmock_parent AS MP
LEFT JOIN MD
ON MP.ID=MD.SubJobControlParent AND CntUT=1
LEFT JOIN MMD ON MD.SubJobControlParent=MMD.SubJobControlParent
Returns
ID |
SomeField |
UnitType |
1 |
Entry 1 |
Portable |
Now change one UnitTpe to "Mobile" in details
Returns
ID |
SomeField |
UnitType |
1 |
Entry 1 |
... |
EDIT: Just saw your second post:
Just remove the Foreign Key Constraint in Detail, or allow it to be NULL (as it is currently in my Sample)
EDIT2: Changed the SELECT-Statement a bit. Should be a bit more performant
Code:
WITH
MD As (SELECT SubJobControlParent, COUNT(DISTINCT UnitType) AS CntUT
FROM tbl_mmock_detail GROUP BY SubJobControlParent),
MMD AS (SELECT DISTINCT MMD.SubJobControlParent, MMD.UnitType FROM tbl_mmock_detail AS MMD
INNER JOIN MD ON MMD.SubJobControlParent=MD.SubJobControlParent AND MD.CntUT=1)
SELECT MP.ID, MP.SomeField, COALESCE(MMD.UnitType,'...') AS UnitType
FROM tbl_mmock_parent AS MP
LEFT JOIN MMD ON MP.ID=MMD.SubJobControlParent
Same Results
Note: In the "MD" CTE above you could even introduce a WHERE-Clause "WHERE SubJobControlParent IS NOT NULL"
Code:
WITH
MD As (SELECT SubJobControlParent, COUNT(DISTINCT UnitType) AS CntUT
FROM tbl_mmock_detail WHERE SubJobControlParent IS NOT NULL
GROUP BY SubJobControlParent),
MMD AS (SELECT DISTINCT MMD.SubJobControlParent, MMD.UnitType FROM tbl_mmock_detail AS MMD
INNER JOIN MD ON MMD.SubJobControlParent=MD.SubJobControlParent AND MD.CntUT=1)
SELECT MP.ID, MP.SomeField, COALESCE(MMD.UnitType,'...') AS UnitType
FROM tbl_mmock_parent AS MP
LEFT JOIN MMD ON MP.ID=MMD.SubJobControlParent
Explanation:
"MD" prepares all records from Details where there exists a Parent-Record (Noted by SubJobControlParent NOT being NULL), and condenses those records down to just the SubJobControlParent and its Distinct count of UnitType
"MMD" prepares all records from details where you have a homogenous UnitType across all Detail-Records. For this i'm joining to "MD" but cutting off all others WHERE the Count is > 1
The final SELECT is then a simple LEFT JOIN using COALESCE to display either the UnitType (If Count=1) or "..." if Count >1 (which would return a NULL for UnitType inside the COALESCE, forcing COALESCE to display the second offered Text)
Last edited by Zvoni; Oct 30th, 2024 at 10:20 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Oct 30th, 2024, 10:31 AM
#4
Thread Starter
PowerPoster
Re: Master Detail data - display data in Master row only if same data all Detail rows
@Zvoni - ok I tried out this code with my table (vs your test tables) and it is working. I will next incorporate it into my sproc. Thanks!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Oct 30th, 2024, 10:33 AM
#5
Thread Starter
PowerPoster
Re: Master Detail data - display data in Master row only if same data all Detail rows
OK, I think we cross-posted. I will get it working in the bigger picture then see if I need any of the tweaks you added, and thanks for the explanations!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Oct 30th, 2024, 11:25 AM
#6
Thread Starter
PowerPoster
Re: Master Detail data - display data in Master row only if same data all Detail rows
So that is working good but I think I might have a problem.
My main table is xtblSubJob. You may have missed this point in my first post but that table contains both the master and the detail rows, there aren't two tables. You had a CREATE statement for two tables. I don't think that really matters.
So I start out similar to you, like this...
Code:
WITH
MMD AS (SELECT DISTINCT SubJobControlParent, xtblUnitType.UnitType FROM xtblSubJob join xtblUnitType on xtblUnitType.Control = xtblSubJob.UnitType),
MD As (SELECT SubJobControlParent, COUNT(DISTINCT UnitType) AS CntUT
FROM xtblSubJob GROUP BY SubJobControlParent)
SELECT
j.Control AS JobControl,
--j.SONumber AS [Case],
case WHEN u.SerialNumber is not null then j.SONumber else 'LOT' end AS [Case],
j.JobType, j.DateEntered, j.CustNo AS CustomerNumber, j.CustomerName, j.relProjectControl AS ProjectControl, p.ProjectName,
p21.customer_name AS P21Customer, u.EnteredOn,
u.SubJobControl,
u.SubJobControlParent,
u.SubJobLotName,
xtblSaleUnitType.SaleUnitType, xtblSubJobStatuses.StatusName,
--u.SerialNumber,
case WHEN u.SerialNumber is not null then u.SerialNumber else u.SubJobLotName end as SerialNumber,
u.ASN1 AS [ESNIMEI],
COALESCE(MMD.UnitType, '...') AS UnitType,
and then there are more columns I select and lots of tables.
One thing I had to do to get my initial test working is plug in xtblUnitType which has the name "Portable" for UnitType control #2, for example. So that is this line of code:
Code:
MMD AS (SELECT DISTINCT SubJobControlParent, xtblUnitType.UnitType FROM xtblSubJob join xtblUnitType on xtblUnitType.Control = xtblSubJob.UnitType)
and no problem there.
Next up is other columns besides UnitTYpe I need to perform the same operation on. So in addition to UnitType, I want to check for homogeneous FO numbers. The FO (factory order) comes from the same table as UnitType (my main table xtblSubJob) without the need to join to another table to resolve control # to text. Then there will be other columns that come from other tables that I join to in the sproc. So I will give this a try myself but my question is, how extensible is it to go from one colum UnitType to many other columns? Would I need to replicate the MMD and MD CTE's for every column and does that reach a point of craziness where maybe the code should be controlling the creation of the master row and whether or not a column in it gets populated from the detail/children?
Thank you and sorry for the extra info at this late time. I just realized it myself and asked the company prez what else she wants to "bubble up" in this manner.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Oct 30th, 2024, 12:02 PM
#7
Thread Starter
PowerPoster
Re: Master Detail data - display data in Master row only if same data all Detail rows
Wait! There is another problem. Can we go back to your CTE's, with my substitution of the real table:
Code:
WITH
MMD AS (SELECT DISTINCT SubJobControlParent, UnitType FROM xtblSubJob),
MD As (SELECT SubJobControlParent, COUNT(DISTINCT UnitType) AS CntUT
FROM xtblSubJob GROUP BY SubJobControlParent)
SELECT MP.SubJobControl, COALESCE(MMD.UnitType,-1) AS UnitType
FROM xtblSubJob AS MP
LEFT JOIN MD
ON MP.SubJobControl=MD.SubJobControlParent AND CntUT=1
LEFT JOIN MMD ON MD.SubJobControlParent=MMD.SubJobControlParent
The problem with this is it is returning 144,238 rows which is every row from xtblSubJob. That in itself is fine. But the very first row returned is SubJobCOntrol = 1 and UnitType = -1. Because this row is not a master or a detail, it should display its UnitTYpe not -1. It is a "solo" row, not in a MD relations hip yet. Does that make sense? Initially, all of our rows are solo and we are going to be selecting groups of them that we want to create a master for. I will work on this but wanted to let you know this isn't quite right to begin with so I am backing up before I add more columns besides UnitType.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Oct 30th, 2024, 12:10 PM
#8
Thread Starter
PowerPoster
Re: Master Detail data - display data in Master row only if same data all Detail rows
Let me also say I know this is kind of backwards. @Zvoni you may remember my earlier question, https://www.vbforums.com/showthread....-the-bottom-up. You had posted to that. That is not an accurate description of the specs anymore. We are not working on multi-levels of lots. But we are still approaching this from the bottom up in that all the existing rows are really children rows ( that may or may not have a parent). If they don't have a parent, they are solos and need to display on their own line in the grid that's at the same level as a master row. And they are. But this bubbling up code needs to display the solo's UnitType since it doesn't have to worry about any children under it being in agreement.
Ugh. Sorry so complicated!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Oct 30th, 2024, 03:08 PM
#9
Thread Starter
PowerPoster
Re: Master Detail data - display data in Master row only if same data all Detail rows
I solved this another way. I will explain tomorrow. It's quitting time and I have a headache, LOL. I hope I did not also give you a headache. Thanks for the help.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Oct 31st, 2024, 01:52 AM
#10
Re: Master Detail data - display data in Master row only if same data all Detail rows
With a single table:
Table
Code:
CREATE TABLE "tbl_mmock" (
"SubJobControl" INTEGER,
"UnitType" TEXT,
"SomeField" TEXT,
"SubJobControlParent" INTEGER DEFAULT NULL,
PRIMARY KEY("SubJobControl"),
FOREIGN KEY("SubJobControlParent") REFERENCES "tbl_mmock"("SubJobControl") ON DELETE SET DEFAULT
)
Inserts
Code:
INSERT INTO "main"."tbl_mmock" ("SubJobControl", "UnitType", "SomeField", "SubJobControlParent") VALUES ('1', 'Portable', 'Entry 1', NULL);
INSERT INTO "main"."tbl_mmock" ("SubJobControl", "UnitType", "SomeField", "SubJobControlParent") VALUES ('2', 'Portable', 'Entry 2', NULL);
INSERT INTO "main"."tbl_mmock" ("SubJobControl", "UnitType", "SomeField", "SubJobControlParent") VALUES ('3', 'Mobile', 'Entry 3', '1');
INSERT INTO "main"."tbl_mmock" ("SubJobControl", "UnitType", "SomeField", "SubJobControlParent") VALUES ('4', 'Portable', 'Entry 4', '2');
INSERT INTO "main"."tbl_mmock" ("SubJobControl", "UnitType", "SomeField", "SubJobControlParent") VALUES ('5', 'Mobile', 'Entry 5', '3');
INSERT INTO "main"."tbl_mmock" ("SubJobControl", "UnitType", "SomeField", "SubJobControlParent") VALUES ('6', 'Mobile', 'Entry 6', '3');
INSERT INTO "main"."tbl_mmock" ("SubJobControl", "UnitType", "SomeField", "SubJobControlParent") VALUES ('7', 'Portable', 'Entry 7', '1');
INSERT INTO "main"."tbl_mmock" ("SubJobControl", "UnitType", "SomeField", "SubJobControlParent") VALUES ('8', 'Portable', 'Entry 8', '2');
Query
Code:
WITH
MD As (SELECT SubJobControlParent, COUNT(DISTINCT UnitType) AS CntUT
FROM tbl_mmock WHERE SubJobControlParent IS NOT NULL
GROUP BY SubJobControlParent),
MMD AS (SELECT DISTINCT MMD.SubJobControlParent, MMD.UnitType FROM tbl_mmock AS MMD
INNER JOIN MD ON MMD.SubJobControlParent=MD.SubJobControlParent AND MD.CntUT=1)
SELECT MP.SubJobControl, MP.SomeField, COALESCE(MMD.UnitType,'...') AS UnitType
FROM tbl_mmock AS MP
LEFT JOIN MMD ON MP.SubJobControl=MMD.SubJobControlParent
LEFT JOIN MD ON MP.SubJobControl=MD.SubJobControlParent
WHERE MD.CntUT>0
Returns
SubJobControl |
SomeField |
UnitType |
1 |
Entry 1 |
... |
2 |
Entry 2 |
Portable |
3 |
Entry 3 |
Mobile |
It returns those records, that have at least one detail-record
For above
Entry 1 has 2 Detail-Records: Entry 3 and Entry 7
Entry 2 has 2 Detail-Records: Entry 4 and Entry 8
Entry 3 has 2 Detail-Records: Entry 5 and Entry 6
It even works, if the Parent-Detail-Hierarchy "cascades" (Parent has a Detail, which itself is Parent to another Detail)
Note: It ignores the UnitType of the Parent itself: I'm just resolving the UnitType of the Detail-Records
If you want to include the UnitType of the Parent.....
Change the Parent of Entry 6 to "4" (pointing to Entry 4)
Query
Code:
WITH
MD As (SELECT SubJobControlParent, COUNT(DISTINCT UnitType) AS CntUT
FROM tbl_mmock WHERE SubJobControlParent IS NOT NULL
GROUP BY SubJobControlParent),
MMD AS (SELECT DISTINCT MMD.SubJobControlParent, MMD.UnitType FROM tbl_mmock AS MMD
INNER JOIN MD ON MMD.SubJobControlParent=MD.SubJobControlParent AND MD.CntUT=1)
SELECT MP.SubJobControl, MP.SomeField,
COALESCE(MMD.UnitType,'...') As UnitTypeOnlyDetails,
CASE WHEN COALESCE(MMD.UnitType,'...')=MP.UnitType THEN MP.UnitType ELSE '...' END AS UnitTypeInclParent
FROM tbl_mmock AS MP
LEFT JOIN MMD ON MP.SubJobControl=MMD.SubJobControlParent
LEFT JOIN MD ON MP.SubJobControl=MD.SubJobControlParent
WHERE MD.CntUT>0
Returns
SubJobControl |
SomeField |
UnitTypeOnlyDetails |
UnitTypeInclParent |
1 |
Entry 1 |
... |
... |
2 |
Entry 2 |
Portable |
Portable |
3 |
Entry 3 |
Mobile |
Mobile |
4 |
Entry 4 |
Mobile |
... |
Last edited by Zvoni; Oct 31st, 2024 at 02:01 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Oct 31st, 2024, 08:09 AM
#11
Thread Starter
PowerPoster
Re: Master Detail data - display data in Master row only if same data all Detail rows
Good morning and thank you for that "fix"!
When I incorporated it into my sproc, it was giving me the right results for master rows.
1. The rolling up of common values in the children was correctly bubbling up to the master and if not 100% homogeneous then the ellipses were displaying.
2. The solo rows with no children were correctly displaying their Unit Type.
3. However, children rows were displaying ellipses even though of course they should be displaying their own Unit Type, like solo rows. I fixed that in my sproc with a case statement:
Code:
--COALESCE(MMD.UnitType, '...') AS UnitType,
case WHEN u.SerialNumber is not null then xtblUnitType.UnitType else COALESCE(MMD.UnitType, '...') END AS UnitType,
Because master rows do not have serial numbers, only individual units can have a serial number, so that case differentiates the children from their masters.
So next up is all the other columns for which I need to do this homogeneity test on. There are 26. Does that mean I would need this following code 26 more times, changing UnitType for each of the 26 others?
Code:
WITH
MD As (SELECT SubJobControlParent, COUNT(DISTINCT UnitType) AS CntUT
FROM xtblSubJob WHERE SubJobControlParent IS NOT NULL
GROUP BY SubJobControlParent),
MMD AS (SELECT DISTINCT MMD.SubJobControlParent,
--MMD.UnitType
xtblUnitType.UnitType
FROM xtblSubJob AS MMD
join xtblUnitType on xtblUnitType.Control = MMD.UnitType
INNER JOIN MD ON MMD.SubJobControlParent=MD.SubJobControlParent AND MD.CntUT=1)
I said at the end of the day yesterday I would post my solution that I implemented in code. Here's one column from my sproc. It has the same serial number test I implemented in your code:
case
WHEN u.SerialNumber is not null
then j.JobType
else convert(nvarchar(15), u.SubJobControl)
end AS JobType,
The column in my grid that displays JobType (aka CaseType) is also one I want to roll up. So for that column I implemented a ColumnEdit which is a repositoryitemtextedit (these are devexpress controls) and it has a CustomDisplayText event which goes like this:
Code:
/// <summary>
///
/// 10/30/24 - Roll up common values
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void riCaseType_CustomDisplayText(object sender, CustomDisplayTextEventArgs e)
{
int subJobControlParent;
bool bParsed = int.TryParse(e.Value.ToString(), out subJobControlParent);
if (bParsed)
{
using (dsUnitTemplatesTableAdapters.QueriesTableAdapter qry = new dsUnitTemplatesTableAdapters.QueriesTableAdapter())
{
if ((int)qry.qryCountJobType(subJobControlParent) == 1)
{
e.DisplayText = qry.qryGetJobType(subJobControlParent);
}
else
{
e.DisplayText = "";
}
}
}
// else leave it alone, it's the case type from a child
}
qryCountJobType does a count distinct and if it's 1 I can display that single job type that all rows have in common.
So, the dilemma is extending your code to all 26 columns vs extending my code also 26x because I would need a ri for each one.
Any final thoughts before I pick a winner?
(Either way, I wil use your code for UnitType and my code for JobType and then decide which way to go for the remainder).
And thank you for sticking with this!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|