question

sunthea avatar image
sunthea asked

complex sql query - what does it mean?

Hello Could you please have a look on the attached query and explain me what it means? Thank you.
sqlsqlquery
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

No offence, but please can you copy the contents of the ODT text file into the post. I don't mind images but I don't want to be opening random files :D Hope you understand!
1 Like 1 ·
I've deleted the attached file. Please copy and paste your code into the site or paste an image file, not ODT.
1 Like 1 ·
Sorry no, sql query is a picture not a plain text. Could you please advise? I do not know why inner joins and inner selects are used here. Thank you
0 Likes 0 ·
sunthea avatar image
sunthea answered
SELECT DISTINCT CURRENT_DATE AS AKTUALNI_DATUM ,L2.LOAN_NUM AS CISLO_SMLOUVY ,P2.NAME AS PORTFOLIO ,SG2.STS_NAME AS STAV_SMLOUVY ,SBS4.DNU_PO_SPLATNOSTI AS DPD ,AA2.AMOUNT AS PO_SPLATNOSTI ,SBS4.DL_JISTINA+SBS4.DL_JISTINA_PO AS DO_SPLATNOSTI ,AA2.AMOUNT+SBS4.DL_JISTINA+SBS4.DL_JISTINA_PO AS POHLEDAVKA_CELKEM ,W2.WORKFLOW_NAME AS WORKFLOW ,WS2.STATUS_NAME AS WF_STAV ,HW2L4.HST_DAY AS WF_STAV_DATUM ,(U2.FIRSTNAME || ' ' || U2.SURNAME) AS WF_UZIVATEL FROM (SELECT L.LOAN_ID, L.LOAN_NUM, L.PORTFOLIO_ID, L.STS_ID FROM LS_LOAN L WHERE L.PORTFOLIO_ID IN (2, 3)) L2 --WSS, WHB INNER JOIN (SELECT P.PORTFOLIO_ID, P.NAME FROM LS_PORTFOLIO P) P2 ON L2.PORTFOLIO_ID = P2.PORTFOLIO_ID INNER JOIN (SELECT S.STS_ID, S.STS_GRP_ID FROM LS_STATUS S) S2 ON L2.STS_ID = S2.STS_ID INNER JOIN (SELECT SG.STS_GRP_ID, SG.STS_NAME FROM LS_STATUS_GRP SG) SG2 ON S2.STS_GRP_ID = SG2.STS_GRP_ID LEFT JOIN (SELECT AA.LOAN_ID, AA.AMOUNT FROM LS_TEMP_LOAN_ACTUAL_AMOUNT AA) AA2 ON L2.LOAN_ID = AA2.LOAN_ID LEFT JOIN (SELECT SBS.CISLO_SMLOUVY, MAX(SBS.EXPORT_ID) AS EXPORT_ID FROM SB_SMLOUVA SBS WHERE SBS.DATUM_VYPORADANI IS NULL GROUP BY SBS.CISLO_SMLOUVY) SBS2 ON L2.LOAN_NUM = SBS2.CISLO_SMLOUVY LEFT JOIN (SELECT SBS3.CISLO_SMLOUVY, SBS3.EXPORT_ID, SBS3.DNU_PO_SPLATNOSTI, SBS3.DL_JISTINA, SBS3.DL_JISTINA_PO FROM SB_SMLOUVA SBS3 WHERE SBS3.DATUM_VYPORADANI IS NULL) SBS4 ON (SBS2.CISLO_SMLOUVY = SBS4.CISLO_SMLOUVY AND SBS2.EXPORT_ID = SBS4.EXPORT_ID) INNER JOIN (SELECT MAX(HW2L.HST_TIME) AS HST_TIME, HW2L.LOAN_ID, HW2L.WKF_INSTANCE_ID, HW2L.HST_USR FROM HST_WORKFLOW2LOAN HW2L GROUP BY HW2L.LOAN_ID, HW2L.WKF_INSTANCE_ID, HW2L.HST_USR) HW2L2 ON L2.LOAN_ID = HW2L2.LOAN_ID INNER JOIN (SELECT HW2L3.HST_TIME, HW2L3.LOAN_ID, HW2L3.HST_DAY FROM HST_WORKFLOW2LOAN HW2L3) HW2L4 ON (HW2L2.HST_TIME = HW2L4.HST_TIME AND HW2L2.LOAN_ID = HW2L4.LOAN_ID) INNER JOIN (SELECT WI.WKF_INSTANCE_ID, WI.WORKFLOW_ID, WI.STATUS_ID, WI.END_FLAG FROM LS_WORKFLOW_INSTANCES WI WHERE WI.END_FLAG = 0 AND WI.WORKFLOW_ID = 65) WI2 ON --AAA - Nevymáhat standardně HW2L2.WKF_INSTANCE_ID = WI2.WKF_INSTANCE_ID INNER JOIN (SELECT W.WORKFLOW_ID, W.WORKFLOW_NAME FROM LS_WORKFLOW W) W2 ON WI2.WORKFLOW_ID = W2.WORKFLOW_ID INNER JOIN (SELECT WS.STATUS_ID, WS.STATUS_NAME FROM LS_WORKFLOW_STATUS WS) WS2 ON WI2.STATUS_ID = WS2.STATUS_ID LEFT JOIN (SELECT U.USR_ID, U.FIRSTNAME, U.SURNAME FROM LS_USERS U WHERE U.USR_ID <> 124) U2 ON --STJ HW2L2.HST_USR = U2.USR_ID ORDER BY 4 DESC, 5 ;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WRBI avatar image
WRBI answered
" I do not know why inner joins and inner selects are used here. Thank you" **Why INNER joins:** INNER joins select data where both tables have matched records and leaves everything else out. See [W3Schools INNER JOIN][1] for a simplified explanation. Or for a technical aspect [MSDN FROM][2] - the MSDN references for the FROM keyword, but includes all types of JOINs. **INNER SELECT (Sub Queries)** Some of these are wrote badly i.e. INNER JOIN (SELECT P.PORTFOLIO_ID, P.NAME FROM LS_PORTFOLIO P) P2 ON L2.PORTFOLIO_ID = P2.PORTFOLIO_ID should be wrote: INNER JOIN LS_PORTFOLIO as P2 ON L2.PORTFOLIO_ID = P2.PORTFOLIO_ID There's no need for the selection of the columns that you need to return. The one's with functions and WHERE clauses in, ie: INNER JOIN (SELECT **MAX(**HW2L.HST_TIME) AS HST_TIME, HW2L.LOAN_ID, HW2L.WKF_INSTANCE_ID, HW2L.HST_USR FROM HST_WORKFLOW2LOAN HW2L GROUP BY HW2L.LOAN_ID, HW2L.WKF_INSTANCE_ID, HW2L.HST_USR) HW2L2 ON L2.LOAN_ID = HW2L2.LOAN_ID LEFT JOIN (SELECT U.USR_ID, U.FIRSTNAME, U.SURNAME FROM LS_USERS U **WHERE** U.USR_ID <> 124) U2 ON --STJ HW2L2.HST_USR = U2.USR_ID These are done this way because they're filtering data out or rolling data up before it's being used. It's best not to order via this method: ORDER BY 4 DESC, 5 ; You should use the actual column names, if someone changed the order of the columns then you'd return the data in a order you didn't want. [1]: https://www.w3schools.com/sql/sql_join_inner.asp [2]: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sunthea avatar image
sunthea answered
Hello. Many thanks for your help, would you please advise me further? 1. How to simply this statement? INNER JOIN (SELECT MAX(HW2L.HST_TIME) AS HST_TIME, HW2L.LOAN_ID, HW2L.WKF_INSTANCE_ID, HW2L.HST_USR FROM HST_WORKFLOW2LOAN HW2L GROUP BY HW2L.LOAN_ID, HW2L.WKF_INSTANCE_ID, HW2L.HST_USR) HW2L2 ON L2.LOAN_ID = HW2L2.LOAN_ID 2. In this statement the are two joins "ON", it it correct please? INNER JOIN (SELECT HW2L3.HST_TIME, HW2L3.LOAN_ID, HW2L3.HST_DAY FROM HST_WORKFLOW2LOAN HW2L3) HW2L4 ON (HW2L2.HST_TIME = HW2L4.HST_TIME AND HW2L2.LOAN_ID = HW2L4.LOAN_ID) 1. This statement joins badly, right? INNER JOIN (SELECT WI.WKF_INSTANCE_ID, WI.WORKFLOW_ID, WI.STATUS_ID, WI.END_FLAG FROM LS_WORKFLOW_INSTANCES WI WHERE WI.END_FLAG = 0 AND WI.WORKFLOW_ID = 65) WI2 ON --AAA - Nevymáhat standardně HW2L2.WKF_INSTANCE_ID = WI2.WKF_INSTANCE_ID Your help is much appreciated.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.