question

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 ;

" 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

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.