Identify wrong links inside a hierarchical tree-structure
-
Hello,
I'm working on an electrical network, edge are link between nodes, insert lines with 'end' are all clients.
The first line is the main generator.My tree is like this :
CREATE TABLE tree
( edge varchar(5),
from_node integer,
to_node integer,
mode character varying(5));INSERT INTO tree(edge, from_node, to_node, mode) VALUES
('A', 1, 4, 'start'),
('B', 4, 2, 'end'), ('C', 5, 3, 'end'),
('D', 4, 5, NULL),
('E', 6, 5, NULL), ('F', 6, 7, 'end');The issue is that the 'E' line is wrong because it should be 5, 6.
Do not take node number as a reference, because in real life, these as varchar like : CODEXXX and increment
Is it possible to help me the query that will return all mismatch start and endpoints ?
Regards
-
Is this T-SQL, or some other SQL dialect? It appears to contain invalid T-SQL syntax.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I update the SQL, it was some postgresql, but I'll copy the structure to obtain the same result at the end
- team.bernard - Sunday, October 22, 2017 7:53 AM
How do we tell, from your data, E is wrong? Is it purely on the basis that it has a start position that isn't an end position?
Perhaps:
SELECT *
FROM tree t
WHERE t.from_node NOT IN (SELECT sq.to_node FROM tree sq)
AND t.from_node != 1;That query will also return F, as it starts from 6, which is the start point of E.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Can you describe the logic which needs to be used to determine invalid rows?
The following code finds 'orphans', I think (ie, 6,7 in your example). Does that help?CREATE TABLE #tree
(
edge VARCHAR(5),
from_node INTEGER,
to_node INTEGER,
mode VARCHAR(5)
);INSERT #tree
(
edge,
from_node,
to_node,
mode
)
VALUES
('A', 1, 4, 'start'),
('B', 4, 2, 'end'),
('C', 5, 3, 'end'),
('D', 4, 5, NULL),
('E', 6, 5, NULL),
('F', 6, 7, 'end');SELECT *
FROM #tree t
WHERE t.mode <> 'start'
AND NOT EXISTS
(
SELECT 1 FROM #tree t2 WHERE t.from_node = t2.to_node
);The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Thom A - Sunday, October 22, 2017 8:07 AM
Funnily enough, I thought the same. But then I tried it, and it works (at least in 2016)!
It seems that CHARACTER VARYING(5) is a synonym for VARCHAR(5).
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Phil Parkin - Sunday, October 22, 2017 8:25 AM
Indeed! I tried it after typing it (SQL 2017) and noticed it as well (so then removed that statement from my post). When I googled "character varying" though, the first link was about PostgreSQL.
I really need a new laptop. The space bar on this thing only works half the time. >_<
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
The logic is that I made a void function to find start and endpoint of each lines.
But the way isn't correct because for the line 'E', the link to go to the node 7 is broken, so it's need to be switched, so the node 7 will have electricity.
The sort order is topological, but I can't find a query to obtain "broken" lines to which prevent to have a working network.
Regards
- team.bernard - Sunday, October 22, 2017 5:41 AMHello,
I'm working on an electrical network, edge are link between nodes, insert lines with 'end' are all clients.
The first line is the main generator.My tree is like this :
CREATE TABLE tree
( edge varchar(5),
from_node integer,
to_node integer,
mode character varying(5));INSERT INTO tree(edge, from_node, to_node, mode) VALUES
('A', 1, 4, 'start'),
('B', 4, 2, 'end'), ('C', 5, 3, 'end'),
('D', 4, 5, NULL),
('E', 6, 5, NULL), ('F', 6, 7, 'end');The issue is that the 'E' line is wrong because it should be 5, 6.
Do not take node number as a reference, because in real life, these as varchar like : CODEXXX and increment
Is it possible to help me the query that will return all mismatch start and endpoints ?
Regards
The logic is pretty simple for such a DAG (Directed Acyclic Graph). The FROM node column should be unique. Any non-unique value is a candidate for possibly being an error.
You should also add a self-referencing FK stipulating that no TO node can exist unless it first exists as a FROM node. That also means that you're listing your nodes backwards if the source of electricity starts at node 1.
To wit, the nodes should be listed as follows as FROM/TO because node 1 is the source of power and should be the root of the tree.
1,NULL
4,1
2,4
5,4
3,5
6,5
7,6--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Phil Parkin - Sunday, October 22, 2017 8:25 AM
Yes, and it always has been. We also have CHARACTER VARYING(n) and NATIONAL CHARACTER VARYING(n). There are some other shorthands that nobody uses.
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Monday, October 23, 2017 3:21 PM
Nobody uses it, because it's not shorthand.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply