Home » Developer & Programmer » Forms » A Complex Select Statement
A Complex Select Statement [message #116147] Mon, 18 April 2005 03:02 Go to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Summery Of Problem
[
Master table have date column.
Child table don’t have date column.

Need a Select statement which return sum of a Child column for a specific month of year.
]


Details Of Problem (If you have time for detail Answer)
[

Master Table
Sale_Sheet_No (PK)
Date
SalesMan_Empno(FK)

Child Table
Sno (PK)
Sale_Sheet_No (FK)
Item_No (FK form Item_Details)
Qty_Sold

A third table Item_Details have column of commission.

I want to calculate commission of salesman for every month and want to add it in table of Monthly_Salary which as follows.

Monthly_Salary
Sno (PK)
Empno (FK from Employee)
Salary
Commission
loan
TotalSalary

Wishes
Jawad

Re: A Complex Select Statement [message #116270 is a reply to message #116147] Tue, 19 April 2005 00:30 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Sno from Child is NOT Sno from Monthly_Salary. You need a month (date) on Monthly_Salary or some other way of associating a month/year with a particular entry.

Insert the normal monthly record with Salary and Loan[Repayment] (as a positive number)

update Monthly_Salary ms
   set Commission = (select sum (nvl (itm.Commission, 0) * ch.Qty_Sold)
                       from Item_Details itm, Child ch, Master mst
                      where 1 = 1
                        and itm.Item_No = ch.Item_No
                        and ch.Sale_Sheet_No = ms.Sale_Sheet_No
                        and ms.Empno = mst.SalesMan_Empno
                        and to_char (mst.Date, 'YYYYMM') = '200503'),
       TotalSalary = Salary
                     + (select sum (nvl (itm.Commission, 0) * ch.Qty_Sold)
                          from Item_Details itm, Child ch, Master mst
                         where 1 = 1
                           and itm.Item_No = ch.Item_No
                           and ch.Sale_Sheet_No = ms.Sale_Sheet_No
                           and ms.Empno = mst.SalesMan_Empno
                           and to_char (mst.Date, 'YYYYMM') = '200503')
                     - loan
 where to_char (ms.Date, 'YYYYMM') = '200503'; -- you have to be able to select which Monthly_Salary entries are to be updated.


Give it a go and tell us the result.
Re: A Complex Select Statement [message #116433 is a reply to message #116147] Wed, 20 April 2005 02:09 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Dear Dj Martin

It really worked with slight alteration.
Thank you very much.
I pray GOD will give you more knowledge to help your self and others.

--------------------------------------------

Summery of Solution:-
I have used the 1st half of your code for commission column.
Populate the total salary from (salary + Commission)

For date (i.e. month/year) I have create two separate columns in monthly salary.

--------------------------------------------

Detail of Solution:-
(Table Names are different, I have published them on OraFaq with change names for batter understanding.)

I have also attached complete fmb file (MonthlySalary.fmb) for everyones perusal.

Code for commission calculation of form.
begin;
select (sum (nvl (itm.PackComm, 0) * ch.sale)) into :MONTHLYSALARY.MONTHLYCOMM
from Pack itm, SaleSheetChd ch, SaleSheet mst
where 1 = 1
and itm.PackName = ch.PackName
and ch.SaleSheetNo = mst.SaleSheetNo
and :MONTHLYSALARY.Empno = mst.Empno
and to_char (mst.SaleDate, 'YYYY') = :MONTHLYSALARY.SALARYYEAR
and to_char (mst.SaleDate, 'MM') = :MONTHLYSALARY.SALARYMONTH ;
end;

Code For Total Salary
:MONTHLYSALARY.TOTALMONTHLYSALARY :=
(:MONTHLYSALARY.MONTHLYSALARY + nvl(:MONTHLYSALARY.MONTHLYCOMM,0));

For more see MonthlySalary.fmb.

Wishes
Jawad

Re: A Complex Select Statement [message #116437 is a reply to message #116147] Wed, 20 April 2005 02:25 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I prefer to use a single date field and store either the first date of the month (prefer) or the last day of the month (messy). This way I can compare dates to dates. When working with a LARGE database you may have to have your indexes working to get the SQL to run in 'less than geological time' and doing date conversions can stop the query being able to use an index on the date field.
Re: A Complex Select Statement [message #116443 is a reply to message #116437] Wed, 20 April 2005 02:53 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Dear Dj Martin

Thanks for your help, Advise and most of all your time once again.

In back of mind I was also feeling the same that two columns for months are not looking good according to software engineering techniques.

+ Now as you inform that date conversions are not advisable.

But this was all I can do to get things going with my limited capabilities of Oracle.

I will do it in my next prototype.

Love
Jawad
Re: A Complex Select Statement [message #118563 is a reply to message #116147] Thu, 05 May 2005 20:19 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
You can always save the 'date' field as either a 6 character or 6 digit number field. That is, '200505' or 200505. Then you can do greater than, less than, and monthly equals.

The problem is that if the organisation decides to introduce fortnightly or weekly pay cycles. But you can increase the length of the field and append 'pay number' to the year and month. For example, '200505A' or '2005051' or '20050501', and if numeric 2005051 or 20050501. That's where using the 'first' of the month gives you the 'first' pay of the month.

Alternatively, have a table called 'pay_cycle' comprising unique primary key using a sequence and pay date and even a 'type of pay' (monthly, weekly, etc). Then use the unique key in your payments table.

David
Re: A Complex Select Statement [message #118574 is a reply to message #116147] Fri, 06 May 2005 01:43 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Dear David

Thanks it look most easy and simple.

Wishes
Jawad
Re: A Complex Select Statement [message #416485 is a reply to message #116147] Mon, 03 August 2009 01:50 Go to previous message
bradcapo2
Messages: 1
Registered: August 2009
Junior Member
I am not so knowledgeable about this matter. So i have to learn it. Thanks for the post.

Previous Topic: Unable to Connect Forms6i to Oracle 10g
Next Topic: Reading data from Serial port: Exception 305500 Un-handled
Goto Forum:
  


Current Time: Sat Dec 10 11:01:26 CST 2016

Total time taken to generate the page: 0.08004 seconds