Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Help
SQL Query Help [message #292734] Wed, 09 January 2008 14:58 Go to next message
kam123
Messages: 13
Registered: January 2008
Junior Member
Hi Everyone,

I am new to oracle please help me out.

Below is my sql command, now I want to add a field ptobalance for each employee but each employee may have more than one pto (paid time off) plan & I think I have to summarize it.
---------------------------------------------------
SELECT al.process_level,al.deptname,al.department,al.emp_status,al.fte_total,al.employee,prt.hours,prt.per_end_date
FROM lrsuser.v_associatelist al,lawson.prtime prt
where prt.company=al.company
AND
prt.employee=al.employee
AND
al.group_name='G:ACTIVE'
AND
prt.pay_sum_grp IN ('FLP','PTS','PTU')
AND
al.emp_status IN ('CF','E9','EF','N9','NF','CA','EA','NA','CB','EB','NB')

AND
TO_CHAR(prt.per_end_date, 'YYYY') = '2007';

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

Please suggest me.


Thanks in advance!!!!



Re: SQL Query Help [message #292747 is a reply to message #292734] Wed, 09 January 2008 15:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reread what you posted.
Do you think it is clear?
Do you think we can write a query with what you said?

please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want for this test case.

Regards
Michel

Re: SQL Query Help [message #292756 is a reply to message #292747] Wed, 09 January 2008 15:52 Go to previous messageGo to next message
kam123
Messages: 13
Registered: January 2008
Junior Member
Hello Sir,

I am trying to display in my report the pto balances for each employee. pto balance is coming from the field called
"elig_balance" .

For your info the pto plan name is a primary key field. And an employee may have more than one pto plan.

So far I am not sure how my output will be,trying to figure out.

Thank You!!

Re: SQL Query Help [message #292762 is a reply to message #292756] Wed, 09 January 2008 15:59 Go to previous messageGo to next message
kam123
Messages: 13
Registered: January 2008
Junior Member
My sql command is as follows:

SELECT al.process_level,al.deptname,al.department,al.emp_status,al.fte_total,al.employee,prt.hours,prt.per_end_date
FROM lrsuser.v_associatelist al,lawson.prtime prt
where prt.company=al.company
AND
prt.employee=al.employee
AND
al.group_name='G:ACTIVE'
AND
prt.pay_sum_grp IN ('FLP','PTS','PTU')
AND
al.emp_status IN ('CF','E9','EF','N9','NF','CA','EA','NA','CB','EB','NB')

AND
TO_CHAR(prt.per_end_date, 'YYYY') = '2007';

Re: SQL Query Help [message #292763 is a reply to message #292756] Wed, 09 January 2008 16:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

pto balance is coming from the field called
"elig_balance" .

And you add 0 to elig_balance to get pto balance I think.

Quote:

Post a test case: create table and insert statements along with the result you want for this test case.


Regards
Michel
Re: SQL Query Help [message #292766 is a reply to message #292763] Wed, 09 January 2008 16:25 Go to previous messageGo to next message
kam123
Messages: 13
Registered: January 2008
Junior Member
For your info. pto balance field called "elig_balance" is coming from a different table called "emtamastr". I wrote below sql to get the PTO balance only for one employee but I want for each employee & if a employee has more than one pto plan then it should add all.

select plan.company,plan.employee,plan.plan_name,mastr.elig_balance
from v_hr_ptoplan plan inner join lawson.emtamastr mastr
on plan.company=mastr.company
and plan.employee=mastr.employee
where plan.employee=3113456
and plan.plan_name=mastr.plan_name;

And I want to add the above query to my original query,I mean I want it in one sql command.

Sorry for any inconvenience.

Thanks in advance!
Re: SQL Query Help [message #292850 is a reply to message #292766] Thu, 10 January 2008 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still unable to understand you, you know the table structure, you know the column meaning, you the data, good for you.
In addition, you are still unable to provide a test case and are unable to read and follow the guidelines, so I give up.

Regards
Michel

[Updated on: Thu, 10 January 2008 01:00]

Report message to a moderator

Re: SQL Query Help [message #292878 is a reply to message #292734] Thu, 10 January 2008 02:01 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

For your last query , your expected statemennt would be something like ( if i am not mistaken )


select plan.company,plan.employee,plan.plan_name,
       (SELECT COUNT(mastr.elig_balance)
        FROM   lawson.emtamastr mastr 
        WHERE  mastr.company=plan.company
        AND    mastr.employee=plan.employee
        AND    mastr.plan_name=plan.plan_name)"ptobalances"
from v_hr_ptoplan plan 


OR

select DISTINCT plan.company,plan.employee ,
       (SELECT COUNT(mastr.elig_balance)
        FROM   lawson.emtamastr mastr 
        WHERE  mastr.company=plan.company
        AND    mastr.employee=plan.employee)"ptobalances"
from v_hr_ptoplan plan 


I hope this may help you to reach your funal answer .

Thumbs Up
Rajuvan

[Updated on: Thu, 10 January 2008 02:03]

Report message to a moderator

Previous Topic: User Errors
Next Topic: exception DUP_VAL_ON_INDEX
Goto Forum:
  


Current Time: Sat Dec 10 01:19:48 CST 2016

Total time taken to generate the page: 0.10479 seconds