I am a newbie in Oracle. I am currently working on project that has three tables: a Customer table (having one record per customer), a Loan Sanction table (having one record per loan sanctioned, and Loan Advanced table (having multiple instalments of the Loan Sanctioned table). The fisrt two tables are linked to each other based on customer number, whereas the second table is linked with the third table (Loan Advanced) on loan number. I am looking for a SQL query that should give me the Output as the following columns (from left to right): Customer Number, Customer Name, Customer Address, Shares, Amount Sanctioned, Amount Advanced, Advanced Inst., Purpose, and Remarks. The Amount Advanced should be the sum of all the advancements made against the loan number. The Installment should be the count of all the advancements made against the loan number. The Amount Advanced should be checked against the Amount Sanctioned, and in case of a deficit, it should output "*****" in the Remarks column. Is is possible to present this output from a single SQL?
Absolutely and here is the SQL. I will assume you meant the customer number to be 1289 PHP in the SANCTION table in order to get the desired output.
SELECT t1.customer_NO "Customer No.",
t1.Customer_name " Customer Name",
t1.CUSTOMER_ADDRESS "Customer Address",
sUM(t3.loan_advanced) " Amt. Advanced",
COUNT(t3.loan_no) "Advanced INst",
(case when SUM(t3.loan_advanced) < t2.loan_sanctioned then '*****' else '' end) "Remarks"
FROM CUSTOMER T1, LOAN t2, ADVANCEMENT t3
WHERE T1.CUSTOMER_NO = T2.CUSTOMER_NO
AND T2.LOAN_NO = T3.LOAN_NO
GROUP BY t1.CUSTOMER_NO, CUSTOMER_NAME, CUSTOMER_ADDRESS, SHARE_holding, LOAN_SANCTIONED, LOAN_PURPOSE
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.