OraFAQ Forum: Forms » A Complex Select Statement

Home » Developer & Programmer » Forms » A Complex Select Statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

A Complex Select Statement [message #116147]

Mon, 18 April 2005 03:02

weekend79
Messages: 198
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

Report message to a moderator

Re: A Complex Select Statement [message #116270 is a reply to message #116147]

Tue, 19 April 2005 00:30

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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.

Report message to a moderator

Re: A Complex Select Statement [message #116433 is a reply to message #116147]

Wed, 20 April 2005 02:09

weekend79
Messages: 198
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

Attachment: MonthlySalary.fmb
(Size: 76.00KB, Downloaded 1207 times)

Report message to a moderator

Re: A Complex Select Statement [message #116437 is a reply to message #116147]

Wed, 20 April 2005 02:25

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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.

Report message to a moderator

Re: A Complex Select Statement [message #116443 is a reply to message #116437]

Wed, 20 April 2005 02:53

weekend79
Messages: 198
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

Report message to a moderator

Re: A Complex Select Statement [message #118563 is a reply to message #116147]

Thu, 05 May 2005 20:19

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator