Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm attempting to compile a long Oracle SQL where one fo the sub SQLs is returning multiple lines with the same transaction reference. I would like to amend the SQL so that only the lines with the latest date (max date) for each transaction is reflected in the output. I know I can do this in Power Query, but I need the solution to be SQL based.
The subset of the current SQL is as follows:
SELECT distinct (LOAN_MSTR.I_LOAN_NUM), LOAN_STAT.C_LOAN_STAT, LOAN_STAT.D_END
FROM LOAN_MSTR
INNER JOIN AUD_LOAN on LOAN_MSTR.I_LOAN_ID = AUD_LOAN.I_LOAN_ID
INNER JOIN DPT on AUD_LOAN.I_DPT_ID = DPT.I_DPT_ID
INNER JOIN LOAN_HIST on LOAN_MSTR.I_LOAN_NUM = LOAN_HIST.I_LOAN_NUM
INNER JOIN LOAN_STAT on LOAN_HIST.I_LOAN_OMNI_ID = LOAN_STAT.I_LOAN_OMNI_ID
WHERE (AUD_LOAN.D_CAL >= TO_DATE ('01/11/2019', 'DD/MM/YYYY') and AUD_LOAN.D_CAL <= TO_DATE ('05/11/2019', 'DD/MM/YYYY') and DPT.C_RGN = 'US' and LOAN_STAT.D_END <= TO_DATE ('05/11/2019', 'DD/MM/YYYY'))
Order by LOAN_MSTR.I_LOAN_NUM
The output for the above is as follows:
Using the results in the red box from the above example, I would like to only see the entry for 11 April for that particular transaction reference.
Thanks
Solved! Go to Solution.
To those who might be interested in knowing the solution: Solution posted on the Oracle SQL & PL/SQL Community blog, Thread ID 4312816, title "Oracle SQL - filter ouput for max date via SQL".
To those who might be interested in knowing the solution: Solution posted on the Oracle SQL & PL/SQL Community blog, Thread ID 4312816, title "Oracle SQL - filter ouput for max date via SQL".
You might want to consult an Oracle form as this form is for Power Query and Power BI. Someone that is deeply knowledgable about composing native SQL statements in Oracle may stop by but an Oracle forum would be faster.
That said, not sure what the SQL you provided is doing, but if you can do it in Power Query against the Oracle server, Power Query can fold many query statements. Do it in Power Query then right-click on the last step and see if "View Native Query" is showing. If it is, that is your SQL statement.
Not all transformations Power Query does though are folded, so it might be greyed out.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
50 | |
19 | |
12 | |
11 |