Quote from
dandl on March 20, 2020, 12:48 pm
See this link: https://web.njit.edu/~hassadi/Dbase_Courses/CIS631/Ex_03.html. The question is to write code in any TTM/D to answer Q4.
Q4. Get the supplier names for suppliers who supply all parts.
SQL> select distinct s.sname from s where NOT EXISTS (select * from p where NOT EXISTS (select * from sp where sp.s# = s.s# and sp.p# = p.p#) );
The Andl solution that first came to me is to create an RVA for each supplier and check for set equality.
Andl> S .where( ({{*}} join SP) .select{ P# } = P .select{ P# } ) .select{ SNAME }
This is elegant but depends too much on the features of the expression language. What I'm looking for is a solution based on a simplified kind of RA, without using aggregation or RVAs, that I can use for my Knime nodes. I have an idea, but I thought the clever people on this list might find this slightly interesting. (Yes, it's probably in DTATRM but how to find it?)
See this link: https://web.njit.edu/~hassadi/Dbase_Courses/CIS631/Ex_03.html. The question is to write code in any TTM/D to answer Q4.
Q4. Get the supplier names for suppliers who supply all parts.
SQL> select distinct s.sname from s where NOT EXISTS (select * from p where NOT EXISTS (select * from sp where sp.s# = s.s# and sp.p# = p.p#) );
The Andl solution that first came to me is to create an RVA for each supplier and check for set equality.
Andl> S .where( ({{*}} join SP) .select{ P# } = P .select{ P# } ) .select{ SNAME }
This is elegant but depends too much on the features of the expression language. What I'm looking for is a solution based on a simplified kind of RA, without using aggregation or RVAs, that I can use for my Knime nodes. I have an idea, but I thought the clever people on this list might find this slightly interesting. (Yes, it's probably in DTATRM but how to find it?)
Andl - A New Database Language - andl.org
I feel this ought to be simpler, but without much thought:
((EXTEND S {S#}: {AllParts := P {P#}}) JOIN ((SP {S#, P#}) GROUP {ALL BUT S#} AS PartsSupplied)) WHERE PartsSupplied = AllParts
I've attached an image from Rel's visual query editor -- used to write the above -- which I think makes the query clearer.
I feel this ought to be simpler, but without much thought:
((EXTEND S {S#}: {AllParts := P {P#}}) JOIN ((SP {S#, P#}) GROUP {ALL BUT S#} AS PartsSupplied)) WHERE PartsSupplied = AllParts
I've attached an image from Rel's visual query editor -- used to write the above -- which I think makes the query clearer.
Uploaded files:I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
Quote from
dandl on March 21, 2020, 12:18 am
I feel this ought to be simpler, but without much thought:
((EXTEND S {S#}: {AllParts := P {P#}}) JOIN ((SP {S#, P#}) GROUP {ALL BUT S#} AS PartsSupplied)) WHERE PartsSupplied = AllParts
I've attached an image from Rel's visual query editor -- used to write the above -- which I think makes the query clearer.
Thank you, but that solution has an RVA. I'm specifically looking for a solution that does not depend on RVA or aggregation.
I found the answer in DBE p178. It matches the method I had thought of. I've attached the solution in Knime.
I feel this ought to be simpler, but without much thought:
((EXTEND S {S#}: {AllParts := P {P#}}) JOIN ((SP {S#, P#}) GROUP {ALL BUT S#} AS PartsSupplied)) WHERE PartsSupplied = AllParts
I've attached an image from Rel's visual query editor -- used to write the above -- which I think makes the query clearer.
Thank you, but that solution has an RVA. I'm specifically looking for a solution that does not depend on RVA or aggregation.
I found the answer in DBE p178. It matches the method I had thought of. I've attached the solution in Knime.
Uploaded files:Andl - A New Database Language - andl.org
Quote from
dandl on March 21, 2020, 12:18 am
I feel this ought to be simpler, but without much thought:
((EXTEND S {S#}: {AllParts := P {P#}}) JOIN ((SP {S#, P#}) GROUP {ALL BUT S#} AS PartsSupplied)) WHERE PartsSupplied = AllParts
I've attached an image from Rel's visual query editor -- used to write the above -- which I think makes the query clearer.
Thank you, but that solution has an RVA. I'm specifically looking for a solution that does not depend on RVA or aggregation.
That sounds like an unfortunate limitation.
Quote from
dandl on March 21, 2020, 12:18 am
I feel this ought to be simpler, but without much thought:
((EXTEND S {S#}: {AllParts := P {P#}}) JOIN ((SP {S#, P#}) GROUP {ALL BUT S#} AS PartsSupplied)) WHERE PartsSupplied = AllParts
I've attached an image from Rel's visual query editor -- used to write the above -- which I think makes the query clearer.
Thank you, but that solution has an RVA. I'm specifically looking for a solution that does not depend on RVA or aggregation.
That sounds like an unfortunate limitation.
I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
Quote from
dandl on March 21, 2020, 1:18 pm
Why? The solution I have is precisely the one provided by D&D.
For my current purpose having RVAs would require some heavy lifting, and that certainly is a Knime limitation. But really, are they important enough to worry about?
I went through the image relation section in DBE, and I found no compelling examples. Of course you need a 'good enough' aggregation operator, but beyond that?
Having RVAs and TVAs makes perfect sense in the TTM type system, but do we really want nested tables in dataflow? I'm not convinced at present.
Why? The solution I have is precisely the one provided by D&D.
For my current purpose having RVAs would require some heavy lifting, and that certainly is a Knime limitation. But really, are they important enough to worry about?
I went through the image relation section in DBE, and I found no compelling examples. Of course you need a 'good enough' aggregation operator, but beyond that?
Having RVAs and TVAs makes perfect sense in the TTM type system, but do we really want nested tables in dataflow? I'm not convinced at present.
Andl - A New Database Language - andl.org
Quote from
dandl on March 21, 2020, 1:18 pm
Why? The solution I have is precisely the one provided by D&D.
For my current purpose having RVAs would require some heavy lifting, and that certainly is a Knime limitation. But really, are they important enough to worry about?
I find them very useful, but I can appreciate that others might not take the same approaches.
Quote from
dandl on March 21, 2020, 1:18 pm
Why? The solution I have is precisely the one provided by D&D.
For my current purpose having RVAs would require some heavy lifting, and that certainly is a Knime limitation. But really, are they important enough to worry about?
I find them very useful, but I can appreciate that others might not take the same approaches.
I'm the forum administrator and lead developer of Rel. Email me at dave@armchair.mb.ca with the Subject 'TTM Forum'. Download Rel from https://reldb.org
