Help needed with a database.

Hi, I need some help at my company database, but I don't understand how this works. I want to convert all cases to something more understandable. I have access and can share, of course only if it is needed. Can somebody please help me if that is possible?


SELECT *
FROM (SELECT A.TYPE,
CATEGORY = CASE WHEN TYPE = 'PCPA-NAL' AND LEVEL = 4 THEN '01-DOR LEVEL 4 EDITS'
WHEN TYPE = 'PCPA-NAL' AND LEVEL = 3 THEN '02-DOR LEVEL 3 EDITS'
WHEN TYPE = 'PCPA-NAL' AND LEVEL = 2 THEN '03-DOR LEVEL 2 EDITS'
WHEN TYPE = 'PCPA-NAL' AND LEVEL = 1 THEN '04-DOR LEVEL 1 EDITS'
WHEN TYPE = 'PCPA-HX' THEN '05-DOR CAPPING (HX)'
WHEN TYPE = 'PCPA-NHX' THEN '06-DOR CAPPING (NHX)'
WHEN TYPE = 'PCPA-SDF' THEN '07-DOR SALES EDITS'
WHEN TYPE = 'PCPA-NAP' THEN '08-DOR TPP EDITS'
WHEN TYPE = 'PCPA-RE-EDIT' THEN '09-PCPA RE EDITS'
WHEN TYPE = 'PCPA-TPP-EDIT' THEN '10-PCPA TPP EDITS'
ELSE 'XXXX' END,
A. LEVEL,
A.EDIT,
A.DEPT,
A.STRAP,
A.MSG,
A.DSCR
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY LEVEL, EDIT ORDER BY TYPE, LEVEL DESC) AS 'ROWNUMBER',
TYPE,
LEVEL,
EDIT,
DEPT,
STRAP,
MSG,
DSCR
FROM PCPA_DATA_CHECK A WITH (NOLOCK)
WHERE TYPE NOT IN ('PCPA-RE-EDIT', 'PCPA-TPP-EDIT', 'PCPA-NAP','PCPA-SDF')
AND TYPE + CAST(LEVEL AS VARCHAR) + CAST(EDIT AS VARCHAR) NOT IN ('PCPA-NAL411', 'PCPA-NAL412', 'PCPA-NAL413')
AND NOT EXISTS (SELECT NULL
FROM T_NOTE WITH (NOLOCK)
WHERE STRAP = A.STRAP
AND ISNULL(CD, '') = '90'
AND DATEPART(YYYY, DT) =(SELECT INT_VALUE FROM SYSTEM_SETTINGS WITH (NOLOCK) WHERE SETTING = 'TAX_YEAR'))) A
WHERE ROWNUMBER <= 25
UNION ALL
SELECT A.TYPE, CATEGORY =
CASE WHEN TYPE = 'PCPA-NAL' AND LEVEL = 4 THEN '01-DOR LEVEL 4 EDITS'
WHEN TYPE = 'PCPA-NAL' AND LEVEL = 3 THEN '02-DOR LEVEL 3 EDITS'
WHEN TYPE = 'PCPA-NAL' AND LEVEL = 2 THEN '03-DOR LEVEL 2 EDITS'
WHEN TYPE = 'PCPA-NAL' AND LEVEL = 1 THEN '04-DOR LEVEL 1 EDITS'
WHEN TYPE = 'PCPA-HX' THEN '05-DOR CAPPING (HX)'
WHEN TYPE = 'PCPA-NHX' THEN '06-DOR CAPPING (NHX)'
WHEN TYPE = 'PCPA-SDF' THEN '07-DOR SALES EDITS'
WHEN TYPE = 'PCPA-NAP' THEN '08-DOR TPP EDITS'
WHEN TYPE = 'PCPA-RE-EDIT' THEN '09-PCPA RE EDITS'
WHEN TYPE = 'PCPA-TPP-EDIT' THEN '10-PCPA TPP EDITS' ELSE 'XXXX' END,
A. LEVEL, A.EDIT, A.DEPT, A.STRAP, A.MSG, A.DSCR
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY LEVEL, EDIT
ORDER BY TYPE, LEVEL DESC) AS 'ROWNUMBER', TYPE, LEVEL, EDIT, DEPT, STRAP, MSG, DSCR
FROM PCPA_DATA_CHECK A WITH (NOLOCK)
WHERE TYPE NOT IN ('PCPA-RE-EDIT', 'PCPA-TPP-EDIT', 'PCPA-NAP','PCPA-SDF') AND TYPE + CAST(LEVEL AS VARCHAR) + CAST(EDIT AS VARCHAR) IN ('PCPA-NAL411',
'PCPA-NAL412', 'PCPA-NAL413')) A
WHERE ROWNUMBER <= 25
UNION ALL
SELECT A.TYPE, CATEGORY = CASE WHEN TYPE = 'PCPA-NAL' AND LEVEL = 4 THEN '01-DOR LEVEL 4 EDITS' WHEN TYPE = 'PCPA-NAL' AND
LEVEL = 3 THEN '02-DOR LEVEL 3 EDITS' WHEN TYPE = 'PCPA-NAL' AND LEVEL = 2 THEN '03-DOR LEVEL 2 EDITS' WHEN TYPE = 'PCPA-NAL' AND
LEVEL = 1 THEN '04-DOR LEVEL 1 EDITS' WHEN TYPE = 'PCPA-HX' THEN '05-DOR CAPPING (HX)' WHEN TYPE = 'PCPA-NHX' THEN '06-DOR CAPPING (NHX)'
WHEN TYPE = 'PCPA-SDF' THEN '07-DOR SALES EDITS' WHEN TYPE = 'PCPA-NAP' THEN '08-DOR TPP EDITS' WHEN TYPE = 'PCPA-RE-EDIT' THEN '09-PCPA RE EDITS'
WHEN TYPE = 'PCPA-TPP-EDIT' THEN '10-PCPA TPP EDITS' ELSE 'XXXX' END, A. LEVEL, A.EDIT, A.DEPT, A.STRAP, A.MSG, A.DSCR
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY LEVEL, EDIT
ORDER BY TYPE, LEVEL DESC) AS 'ROWNUMBER', TYPE, LEVEL, EDIT, DEPT, STRAP, MSG, DSCR
FROM PCPA_DATA_CHECK A WITH (NOLOCK)
WHERE TYPE IN ('PCPA-SDF')) A
WHERE ROWNUMBER <= 25
UNION ALL
SELECT A.TYPE, CATEGORY = CASE WHEN TYPE = 'PCPA-NAL' AND LEVEL = 4 THEN '01-DOR LEVEL 4 EDITS' WHEN TYPE = 'PCPA-NAL' AND
LEVEL = 3 THEN '02-DOR LEVEL 3 EDITS' WHEN TYPE = 'PCPA-NAL' AND LEVEL = 2 THEN '03-DOR LEVEL 2 EDITS' WHEN TYPE = 'PCPA-NAL' AND
LEVEL = 1 THEN '04-DOR LEVEL 1 EDITS' WHEN TYPE = 'PCPA-HX' THEN '05-DOR CAPPING (HX)' WHEN TYPE = 'PCPA-NHX' THEN '06-DOR CAPPING (NHX)'
WHEN TYPE = 'PCPA-SDF' THEN '07-DOR SALES EDITS' WHEN TYPE = 'PCPA-NAP' THEN '08-DOR TPP EDITS' WHEN TYPE = 'PCPA-RE-EDIT' THEN '09-PCPA RE EDITS'
WHEN TYPE = 'PCPA-TPP-EDIT' THEN '10-PCPA TPP EDITS' ELSE 'XXXX' END, A. LEVEL, A.EDIT, A.DEPT, A.STRAP, A.MSG, A.DSCR
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY LEVEL, EDIT
ORDER BY TYPE, LEVEL DESC) AS 'ROWNUMBER', TYPE, LEVEL, EDIT, DEPT, STRAP, MSG, DSCR
FROM PCPA_DATA_CHECK A WITH (NOLOCK)
WHERE TYPE IN ('PCPA-RE-EDIT', 'PCPA-TPP-EDIT', 'PCPA-NAP')) A
WHERE ROWNUMBER <= 25
) X JOIN
(SELECT TYPE, LEVEL, EDIT, COUNT(STRAP) CNT
FROM PCPA_DATA_CHECK WITH (NOLOCK)
WHERE TYPE NOT IN ('PCPA-RE-EDIT', 'PCPA-TPP-EDIT', 'PCPA-NAP','PCPA-SDF') AND TYPE + CAST(LEVEL AS VARCHAR) + CAST(EDIT AS VARCHAR)
NOT IN ('PCPA-NAL411', 'PCPA-NAL412', 'PCPA-NAL413') AND NOT EXISTS
(SELECT NULL
FROM T_NOTE WITH (NOLOCK)
WHERE STRAP = PCPA_DATA_CHECK.STRAP AND ISNULL(CD, '') = '90' AND DATEPART(YYYY, DT) =
(SELECT INT_VALUE
FROM SYSTEM_SETTINGS WITH (NOLOCK)
WHERE SETTING = 'TAX_YEAR'))
GROUP BY TYPE, LEVEL, EDIT
UNION ALL
SELECT TYPE, LEVEL, EDIT, COUNT(STRAP) CNT
FROM PCPA_DATA_CHECK WITH (NOLOCK)
WHERE TYPE NOT IN ('PCPA-RE-EDIT', 'PCPA-TPP-EDIT', 'PCPA-NAP','PCPA-SDF') AND TYPE + CAST(LEVEL AS VARCHAR) + CAST(EDIT AS VARCHAR) IN ('PCPA-NAL411',
'PCPA-NAL412', 'PCPA-NAL413')
GROUP BY TYPE, LEVEL, EDIT
UNION ALL
SELECT TYPE, LEVEL, EDIT, COUNT(STRAP) CNT
FROM PCPA_DATA_CHECK WITH (NOLOCK)
WHERE TYPE IN ('PCPA-SDF')
GROUP BY TYPE, LEVEL, EDIT
UNION ALL
SELECT TYPE, LEVEL, EDIT, COUNT(STRAP) CNT
FROM PCPA_DATA_CHECK WITH (NOLOCK)
WHERE TYPE IN ('PCPA-RE-EDIT', 'PCPA-TPP-EDIT', 'PCPA-NAP')
GROUP BY TYPE, LEVEL, EDIT) Y ON X. LEVEL = Y. LEVEL AND X.EDIT = Y.EDIT
ORDER BY category, X. LEVEL, X.edit, dept, strap

>>1
Someone, please provide consistent MySQL criticism copipe to spam threads like OP. Some in the form of
1. SQL strives to do X, but produces Y.
2. SQL has a Jew in core dev team.
3. SQL is based on Jewish set theory.
4. SQL hard for newbies
5. SQL is evil, because static typing consider harmful
6. SQL code unmaintable
7. SQL databases are slow
8. etc..