Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to diagnose SQL problems. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

The problem diagnosis user responded that an employee could not set his attendance calendar. It is added that our employees go to work from Monday to Friday, leaving work from 8 to 5, but there is also a three-day night shift and two-day rest system, so it is necessary to set up

The employee's work system. Now it is impossible to set up the employee's work system, so the employee will not be able to submit overtime applications and other operations. And when the salary settlement is approaching, if the information such as attendance and overtime can not be submitted in time, it will affect

The final payroll. To deal with this problem in a timely manner, you have to work with the developer to find the logs on the official system. Fortunately, combing through the logic with the developers, we quickly found the possible problematic SQL. But this SQL executes normally, I don't know why it will query out

Unneeded employee information, this is to find out that the employee does not meet the requirements, so the system deletes this illogical data.

This is why DBA intervened to debug this SQL to find the wrong query. The data found in the relevant table is as follows

SELECT APPNT_DATE

, APPNT_CD

, APPNT_SEQ

, ACTI_STTS_CD

FROM MEMP_AP_APPNT_DET

WHERE ten_id='T01'

AND SITE_ID='EZ00'

AND EMP_ID = '2F4EE7C0599E11E0B358975729707EA8'

AND APPNT_DATE = '20180301'

APPNT_DATE APPNT_CD APPNT_SEQ ACTI_STTS_CD

20180301 AB 10 AA

20180301 DC 9 DA

20180301 YB 8 AA

The program will find the maximum APPNT_SEQ value of the table to find out the last status value of the employee during the day.

But the query condition in SQL is like this

AND R.APPNT_DATE | | R.APPNT_SEQ = (SELECT MAX (X1.APPNT_DATE | | X1.APPNT_SEQ))

FROM MEMP_AP_APPNT_DET X1

WHERE X1.TEN_ID = R.TEN_ID

AND X1.SITE_ID = R.SITE_ID

AND X1.EMP_ID = R.EMP_ID

AND X1.APPNT_DATE 2018030110, so the maximum result is 201803019, not 2018030110.

The final modified SQL

SELECT N.TEN_ID

,...

FROM TTNA_TBS_PERSN_MAS N

, MEMP_AP_APPNT_DET R-- CUR

, MEMP_AP_APPNT_DET S-- PAST

WHERE N.TEN_ID = 'T01'

AND N.SITE_ID = 'EZ00'

AND N.EMP_ID = N.EMP_ID--NVL ('null', N.EMP_ID)

AND N.TEN_ID = R.TEN_ID

AND N.SITE_ID = R.SITE_ID

AND N.EMP_ID = R.EMP_ID

AND R.APPNT_DATE BETWEEN TO_CHAR (TO_DATE ('20180329) AND' 20180329')-100noteYYYMMDD'

...

AND TO_NUMBER (R.APPNT_DATE | | R.APPNT_SEQ) = (SELECT MAX (TO_NUMBER (X1.APPNT_DATE | | X1.APPNT_SEQ))-- update

FROM MEMP_AP_APPNT_DET X1

WHERE X1.TEN_ID = R.TEN_ID

AND X1.SITE_ID = R.SITE_ID

AND X1.EMP_ID = R.EMP_ID

AND X1.APPNT_DATE TO_NUMBER (S.APPNT_DATE | | S.APPNT_SEQ)-- update

AND SUBSTR (NVL (R.ACTI_STTS_CD,'), 1, 1) SUBSTR (NVL (S.ACTI_STTS_CD,'), 1, 1) -?

AND TO_NUMBER (S.APPNT_DATE | | S.APPNT_SEQ) = (SELECT MAX (TO_NUMBER (X2.APPNT_DATE | | X2.APPNT_SEQ))-- update

FROM MEMP_AP_APPNT_DET X2

WHERE X2.TEN_ID = S.TEN_ID

AND X2.SITE_ID = S.SITE_ID

AND X2.EMP_ID = S.EMP_ID

AND X2.APPNT_DATE < R.APPNT_DATE)

...

There is a big difference between the comparison of strings and the comparison of numeric types in the process of summary and comparison. There is no need to look at the business logic too much. After all, the operation of SQL in the production environment is guaranteed. Unless there are certain problems in some special cases, we focus on the details of the problem. Here are the technical details.

On how to diagnose SQL problems is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.