Question

Using SQL, write a query that will display the vendor name and the amount due for each vendor. The Vendor table has a list of vendor id and vendor name. The invoice table holds invoice num, vendor id, total amt, payment amt, and credit amount. To calculate amount due, you will take the total amt and subtract payment and credit amount to get this final total. Each vendor should only appear once in the display and amount due will be displayed as currency.

Answer 1

Query ---- select vendor_name,TO_CHAR(round(sum(total_amt-credit_amt-payment_amt),2),'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = ''. ''
NLS_CURRENCY = ''$'' ') as AmountDue from vendor v join invoice i on i.vendor_id=v.vendor_id group by vendor_name;

Output

DDL statement for tests

create table vendor(
vendor_id int primary key,
vendor_name varchar(50)
);

create table invoice
(
invoice_id int primary key,
vendor_id int references vendor(vendor_id),
total_amt numeric(10,2),
payment_amt numeric(10,2),
credit_amt numeric(10,2)
);

insert into vendor values(1,'ABC');
insert into vendor values (2,'Dip');
insert into vendor values(3,'Roma');

insert into invoice values(1,1,1000,200,200);
insert into invoice values(2,2,700,300,200);
insert into invoice values(3,3,900,300,200);
insert into invoice values(4,2,800,300,200);
insert into invoice values(5,3,600,300,200);
insert into invoice values(6,2,800,230,200);
insert into invoice values(6,2,800,230,200);
insert into invoice values(7,1,500,230.20,200);

select vendor_name,TO_CHAR(round(sum(total_amt-credit_amt-payment_amt),2),'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = ''. ''
NLS_CURRENCY = ''$'' ') as AmountDue from vendor v join invoice i on i.vendor_id=v.vendor_id group by vendor_name;

Explanation

In Oracle the format for currency is

'NLS_NUMERIC_CHARACTERS = ''dg''
   NLS_CURRENCY = ''text''
   NLS_ISO_CURRENCY = territory  in to_char

dg repesents The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Here it is a .

Here in this case L99G999D99MI(for large values use L999G999D99MI in accordance to the size otherwise you may get a ######)  L represents for local MI for trailing minus

9G999 Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model.

Similarly 99D99 Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.).