SQL Server Translating T-SQL script to a DB2 equivalent

3z6pesqy 于 5个月前发布在 DB2

关注(0)|答案(2)|浏览(56)

BACKGROUND

We have a large routing table that we use to lookup people based on part number (PN).

Routing table:
| PN | Name |
| ------------ | ------------ |
| 51000 | Bob |
| 52000 | Bob |
| 61000 | John |
| 62000 | John |

A given PN will not match routing table exactly. We want it to find the closest solution possible.

Example: SEARCHED_PART_NUMBER = 51235 -> we want script to answer Bob.

Best solution we have found is to recursively search routing table with a loop until we get a hit.

Here is the T-SQL script:

CREATE TABLE #temp_table 
(
    PN INT,
    name VARCHAR(20),
);

DECLARE @Counter INT
SET @Counter = 5

WHILE (@Counter > 0)
BEGIN
    INSERT INTO #temp_table
        SELECT * 
        FROM Routing
        WHERE LEFT(Routing.PN, @Counter) = LEFT([SEARCHED_PART_NUMBER], @Counter)
    
    IF EXISTS (SELECT * FROM #temp_table)
    BEGIN
        BREAK;
    END
   
    SET @Counter = @Counter - 1
END

SELECT name, COUNT(name) count 
FROM #temp_table
GROUP BY name
ORDER BY count

THE ISSUE

Although this code works, it's in the wrong SQL language... DB2 is what this needs to be in. With a ton of effort we have been unable to convert this into something equivalent in DB2 unfortunately.

THE REQUEST

Convert script into a DB2 equivalent

sql-server

来源：https://stackoverflow.com/questions/77574042/translating-t-sql-script-to-a-db2-equivalent

2条答案

按热度按时间

n8ghc7c11#

I would use a very different approach in both TSQL and DB2, both will be almost (if not exactly) the same:

CREATE TABLE routing (
    PN INTEGER,
    Name VARCHAR(50)
);

CREATE INDEX IX_Routing_PN ON routing (PN);

INSERT INTO routing (PN, Name) 
VALUES (51000, 'Bob'),
       (52000, 'Bob'),
       (61000, 'John'),
       (62000, 'John'),
       (1000,'overlap 1'),
       (1100,'overlap 1'),
       (1010,'overlap 2'),
       (1110,'overlap 2');

CREATE VIEW my_routing AS
SELECT PN
    , LEAD(PN, 1) OVER (
        PARTITION BY Name ORDER BY PN
        ) AS NextPN
    , Name
FROM routing;

SELECT
      name
    , pn
    , nextpn
FROM my_routing
WHERE 51235 between PN and NextPN
   OR 1050 between PN and NextPN
ORDER BY
      name
    , pn
    , nextpn

NAME	PN	NEXTPN
Bob	51000	52000
overlap 1	1000	1100
overlap 2	1010	1110

Amongst other things (like being simple SQL) this approach will locate any overlapping ranges (so you can do something about them).

DB2 fiddle

nb you could also store the wanted part numbers in a table structure e.g:

with example as (
    SELECT 51235 AS searchpn FROM sysibm.sysdummy1
    UNION ALL
    SELECT 1050 FROM sysibm.sysdummy1
    )
select 
    eg.searchpn, r.*
from example as eg
join my_routing as r on eg.searchpn between r.PN and r.NextPN

SEARCHPN	PN	NEXTPN	NAME
51235	51000	52000	Bob
1050	1000	1100	overlap 1
1050	1010	1110	overlap 2

2nd DB2 fiddle

& matching TSQL Fiddle

赞(0）回复(0）举报 5个月前

kxe2p93d2#

Equivalent algorithm in Db2 with a single statement.
P - query parameters. 5 is a constant for your 5 digits only PNs.
You may play with different SEARCHED_PART_NUMBER (SPN) at the link below.

WITH 
  Routing (PN, Name) AS
  (
    VALUES 
      (51000, 'Bob ')
    , (52000, 'Bob ')
    , (61000, 'John')
    , (62000, 'John')
  )
, P (COUNTER, SPN) AS (VALUES (5, 51235))
, CTE (COUNTER) AS
  (
    SELECT COUNTER + 1
    FROM P
      UNION ALL
    SELECT T.COUNTER - 1
    FROM CTE T, P
    WHERE T.COUNTER > 1
    AND NOT EXISTS 
    (
      SELECT 1 
      FROM Routing
      WHERE LEFT (Routing.PN, T.Counter - 1) = LEFT (P.SPN, T.Counter - 1)
    )
  )
SELECT Routing.Name, COUNT (Routing.Name) count
FROM Routing
CROSS JOIN P
JOIN 
(
  SELECT MIN (CTE.Counter) - 1 AS Counter
  FROM CTE
) T ON LEFT (Routing.PN, T.Counter) = LEFT (P.SPN, T.Counter)
WHERE T.Counter <> 0
GROUP BY Routing.Name
ORDER BY count

NAME	COUNT
Bob	1

fiddle

赞(0）回复(0）举报 5个月前

我来回答

SQL Server Translating T-SQL script to a DB2 equivalent

2条答案

相关问题

热门标签

最新问答