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

Please halp

up this is great

up the kike

nikita approves this.

Nikita told I could get help here.

Nikita said I could get help. My name is Vandeberg.

up nikita

up, you should help this guy. Send him an email :)

If it isn't Symta, it's shit.

I agree, every language makes me wanna puke in comparison to Symta.

I agree, I am a faggit, which is often misused as faggot. Obverse me.

Shalom, Hymie ! ! !

Shalom, Hymie ! ! !

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)

no

>>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..

it is just insane that questions and answers like these get so many up votes. I thought this was a technical forum? you can solve someone's problem by providing some difficult to write code and get one or two up votes, yet answer a question like this and get loads of up votes. that's really lame if you ask me.

>>16
Yes.

Is this really how people write SQL?

>>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.

>>20
I LOVE YOU! I LOVE YOUR POST! I READ IT 5 TIMES! PLEASE POST MORE!

>>20
Where are the JEWS, f^riend?

>>22
Inventor of Set Theory, Georg Cantor, was born in 1845 in St. Petersburg, Russia, where his father was a merchant. The father, Georg Waldemar Cantor, had Jewish parents. -- Loren Graham, Naming Infinity, p. 25


http://en.wikipedia.org/wiki/Abraham_Fraenkel
Fraenkel was a fervent Zionist and as such was a member of Jewish National Council and the Jewish Assembly of Representatives He is known for his contributions to axiomatic set theory, especially his addition to Ernst Zermelo's axioms which resulted in Zermelo–Fraenkel axioms.

http://mathforum.org/kb/message.jspa?messageID=69757&tstart=0
In 1935 Zermelo resigned from this position. There is no doubt that it
was caused by the firing and mistreatment of Jewish mathematicianss, e.g.
that of the algebraist and statistician Alfred Loewy who was removed from
his chair at Freiburg.

>>23
Another reason to love JEWS! Shalom, brothers!

check 'em

>>
SELECT *
FROM (SELECT

Oh boy, this is going to be good.

>>19
I've seen (and sometimes written) excessively nested queries that are similar to >>1. It's difficult to imagine something like that making it into a production system, unless the developer was working alone.

>>23
George Cantor died in a insane asylum and spent a good portion of his life trying to prove something which was later proved to be unprovable.

>>27
But he seen Infinity and God spoken to him.

Terry Davis is the modern day Cantor.

GAWWZMACS FLABBERGASTS MY AUDIENCE