Home Convert CTE from Postgres to MSSQL
Reply: 1

Convert CTE from Postgres to MSSQL

BennoDual
1#
BennoDual Published in 2017-12-07 10:34:40Z

I hould convert a CTE from Postgresql zu MSSQL. The Problem is, that the Postgresql-Query uses Arrays. I have no idea how to convert them to MSSQL.

Here is the complete Query with creating the data:

CREATE TABLE pairs (
    from_city VARCHAR(255) NOT NULL,
    to_city VARCHAR(255) NOT NULL,
    distance INTEGER NOT NULL,
    PRIMARY KEY(from_city, to_city),
    CHECK (from_city < to_city)
);


INSERT INTO pairs
VALUES
('Bari','Bologna',672),
('Bari','Genova',944),
('Bari','Milano',881),
('Bari','Napoli',257),
('Bari','Palermo',708),
('Bologna','Genova',190),
('Bologna','Milano',200),
('Bologna','Napoli',470),
('Bologna','Palermo',730),
('Bologna','Roma',300), 
('Genova','Milano',120),
('Genova','Napoli',590),
('Genova','Palermo',790),
('Genova','Roma',400),
('Milano','Napoli',660),
('Milano','Palermo',890),
('Milano','Roma',480),
('Napoli','Palermo',310),
('Napoli','Roma',190),
('Palermo','Roma',430);


SELECT * FROM pairs;


-- Paths from Palermo to Milano...
;WITH RECURSIVE both_ways (
    from_city,
    to_city,
    distance
) /* Working Table containing all ways */
AS (
    SELECT
        from_city,
        to_city,
        distance
    FROM pairs
UNION ALL
    SELECT
        to_city AS "from_city",
        from_city AS "to_city",
        distance
    FROM pairs
),
paths (
    from_city,
    to_city,
    distance,
    path
)
AS (
    SELECT
        from_city,
        to_city,
        distance,
        ARRAY[from_city] AS "path"
    FROM both_ways b1
    WHERE b1.from_city = 'Palermo' --<<< Start Node >>>
  UNION ALL
    SELECT
        b2.from_city,
        b2.to_city,
        p.distance + b2.distance,
        p.path + b2.from_city
    FROM both_ways b2
    JOIN paths p
         ON (p.to_city = b2.from_city
             AND b2.from_city <> ALL (p.path[2:array_upper(p.path,1)]) /* Prevent re-tracing */
             AND array_upper(p.path,1) < 6)
)
SELECT
    path || to_city AS "path",
    distance
FROM paths
WHERE to_city = 'Milano'  --<<< End node >>>
AND ARRAY['Napoli','Roma','Bari'] <@ path -- <<< via... >>> - <@ means 'is contained by' 
ORDER BY distance, path 
LIMIT 5;

I hope someone can help me, how to convert the

ARRAY[from_city] AS "path"

in the Definition of paths

This Conditions in the paths-CTE:

AND b2.from_city <> ALL (p.path[2:array_upper(p.path,1)]) /* Prevent re-tracing */
         AND array_upper(p.path,1) < 6)

And this condition of the select which is using the CTE:

AND ARRAY['Napoli','Roma','Bari'] <@ path -- <<< via... >>> - <@ means 'is contained by' 
Leran2002
2#
Leran2002 Reply to 2017-12-07 10:53:37Z

Try the following

-- Paths from Palermo to Milano...
;WITH both_ways (
    from_city,
    to_city,
    distance
) /* Working Table containing all ways */
AS (
    SELECT
        from_city,
        to_city,
        distance
    FROM pairs
UNION ALL
    SELECT
        to_city AS "from_city",
        from_city AS "to_city",
        distance
    FROM pairs
),
paths (
    from_city,
    to_city,
    distance,
    path,
    Step
)
AS (
    SELECT
        from_city,
        to_city,
        distance,
        CAST(CONCAT('[',from_city,']') AS varchar(MAX)) AS "path",
        1 Step
    FROM both_ways b1
    WHERE b1.from_city = 'Palermo' --<<< Start Node >>>
  UNION ALL
    SELECT
        b2.from_city,
        b2.to_city,
        p.distance + b2.distance,
        p.path + CONCAT('[',b2.from_city,']'),
        p.Step+1
    FROM both_ways b2
    JOIN paths p
         ON (p.to_city = b2.from_city
             AND CHARINDEX(CONCAT('[',b2.from_city,']'),p.path)=0 /* Prevent re-tracing */
             AND p.Step < 6)
)
SELECT TOP 5
    REPLACE(REPLACE(REPLACE(path,'][',','),'[',''),']',',') + to_city AS "path",
    distance
FROM paths
WHERE to_city = 'Milano'  --<<< End node >>>

  AND CHARINDEX('[Napoli]',path)>0
  AND CHARINDEX('[Roma]',path)>0
  AND CHARINDEX('[Bari]',path)>0  -- <<< via... >>> - <@ means 'is contained by' 

ORDER BY distance, path

If function CONCAT doesn't work then everywere use concatination with +

CONCAT('[',from_city,']') => '['+from_city+']'
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.335955 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO