1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
WITH codes(code, id, position) AS (SELECT CAST(LTRIM(dbo.PR.PR_ROOT + ISNULL(dbo.PR_H.H_ANUM, 'N/A')) AS VARCHAR(MAX))
+ CASE WHEN SUBSTRING(dbo.PR.PR_ROOT + dbo.PR_H.H_ANUM,
LEN(dbo.PR.PR_ROOT + dbo.PR_H.H_ANUM), 1) = '''' THEN '' ELSE '' + CHAR(10) END AS Expr1,
dbo.PR_H.H_ID, dbo.PR.PR_LEVEL
FROM dbo.PR_H INNER JOIN
dbo.PR ON dbo.PR_H.PR_ID = dbo.PR.PR_ID INNER JOIN
dbo.H ON dbo.H.H_ID = dbo.PR_H.H_ID
WHERE (dbo.PR.PR_LEVEL = 0)
UNION ALL
SELECT codes.code + CAST(LTRIM(dbo.PR.PR_ROOT + ISNULL(suiv.H_ANUM, 'N/A')) AS VARCHAR(MAX))
+ CASE WHEN SUBSTRING(dbo.PR.PR_ROOT + suiv.H_ANUM,
LEN(dbo.PR.PR_ROOT + suiv.H_ANUM), 1) = '''' THEN '' ELSE '' + CHAR(10) END AS CODE,
suiv.H_ID, dbo.PR.PR_LEVEL
FROM dbo.PR_H AS suiv INNER JOIN
dbo.PR ON suiv.PR_ID = dbo.PR.PR_ID INNER JOIN
dbo.H ON dbo.H.H_ID = suiv.H_ID INNER JOIN
codes ON suiv.H_ID = codes.id AND dbo.PR.PR_LEVEL = codes.position + 1), maxphrase AS
(SELECT id, MAX(position) AS maxposition
FROM codes
GROUP BY id)
SELECT P.id, RTRIM(P.code) + CHAR(10) AS code
FROM codes AS P INNER JOIN
maxphrase AS M ON P.id = M.id AND P.position = M.maxposition |
Partager