Q
Problem solve Get help with specific problems with your technologies, process and projects.

Producing table output from three tables

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",
       t1.Share_holding  "Shares",
       t2.loan_sanctioned "Amt.Santioned",
       sUM(t3.loan_advanced) " Amt. Advanced",
       COUNT(t3.loan_no)  "Advanced INst",
       t2.loan_purpose "Purpose",
       (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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close