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

>>16
1. SQL has no lexical scope, not even a modules system: everything is global and every table, procedure, etc. clutters global scope. Absence of encapsulation means you have to really know what you're doing, planning in advance everything about the implementation of your data.
2. SQL is a set theoretic language, emulating mathematical Theory of Sets on computer and inheriting all the problems and complexity of the Set Theory. SQL requires mathematical thinking, because you can't just order database what to do, you have to construct mathematical theorem object and guide database to prove your theorem, akin to Prolog, which also runs on top of tuple-database. Compared to functional or imperative paradigms, declarative programming is disastrously bad for performance (Prolog runs 100-1000 times slower than C/C++), because brute-force searching tuple-space takes a lot of time, so you end up rephrasing queries trying to influence the search. Then most problems are just easier to solve in a imperative manner, so you will have to write them using clunky vendor specific language extensions, like stored procedures, running on top of what was never designed to be used as an imperative language. Finally, developers, untrained in relation algebra, tend to produce inefficient and unmaintainable SQL code; this especially applies to self-educated script kiddies, who had no exposure to set theory.
3. Designing a rational database is a mathematical black art in itself: you can't just unload everything into a single table, you have to be careful, preserving database normalized and well-typed, which is especially painful if you use dynamically typed language, like Lisp, which has no notion of sets or types and indulges mindset of using untyped lists for everything.
4. SQL isn't general purpose language, meaning you have to combine it with some imperative language, like PHP, doubling complexity and making communication a bottleneck, requiring serializing guest language's objects into set-theoretic format. SQL has no encapsulation: you have to know everything about the implementation of your data in advance. Static typing means that if you want to perform the same operation on different tables, you've got to write the code twice.
5. SQL has pseudo natural language syntax, resulting into confusion and misunderstanding of what query supposed to do: think Set Theory, where a profane layman can easily mistake "if and only if" (duplex implication) for just "if" (simplex implication), which would had been easily avoided, if it used symbols like <-> and ->.
6. SQL has been standardized too late and many vendors developed their incompatible language extensions. So SQL ended up in dozens of dialects. That's why applications are not portable and one reason to have a DB abstraction layer.