Home » SQL & PL/SQL » SQL & PL/SQL » The Subqury Returns So Many Rows (SQL*PLUS)
| The Subqury Returns So Many Rows [message #607417] |
Thu, 06 February 2014 02:35  |
 |
nalrefai
Messages: 2
Registered: February 2014
Location: SAUDI ARABIA
|
Junior Member |
|
|
Dear Geeks,
Can anyone help me in this issue ,
The required is to generate a report to get all the necessary information related to the employee Salary.
Actually the Data required is stored in rows in the Database.
So I generated a query to retrieve it as a columns , but unfortunately it gave me what I wanted but in several rows.
SELECT ASSIGNMENT_NUMBER ,
nvl(Basic_Salary,0) BASIC_SALARY,
NVL(HA_RATE,0) HOUSING,
NVL(TA_RATE,0) TRANSPORTATION,
NVL(SUM_EARNING,0) OTHER_EARNING,
NVL(SUM_DEDUCTION,0) OTHER_DEDUCTION,
nvl(Basic_Salary,0) + NVL(HA_RATE,0) + NVL(TA_RATE,0) + NVL(SUM_EARNING,0) + NVL(SUM_DEDUCTION,0) TOTAL_SALARY
FROM
(SELECT ASSIGNMENT_NUMBER ,
( SELECT prrv1.result_value
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv1
,pay_input_values_x piv
,pay_element_types_x pet
,per_assignments_x paaf
,apps.pay_element_classifications pec
where ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
and paa.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
and paa.ASSIGNMENT_ID = pf.ASSIGNMENT_ID
and ppa.ACTION_TYPE = 'R'
and ppa.ACTION_STATUS ='C'
and prr.ASSIGNMENT_ACTION_ID = paa.ASSIGNMENT_ACTION_ID
and prr.RUN_RESULT_ID = prrv1.RUN_RESULT_ID
and prrv1.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
and pet.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and pet.CLASSIFICATION_ID = pec.CLASSIFICATION_ID
and ppa.EFFECTIVE_DATE >= '01-JAN-2014' and ppa.EFFECTIVE_DATE <= '31-JAN-2014'--:PAYROLL_DATE --'23-FEB-2012'
and prrv1.INPUT_VALUE_ID=prrv.INPUT_VALUE_ID
AND pet.element_name IN ('Basic Salary')
AND pec.CLASSIFICATION_NAME LIKE '%Earnings%'
AND piv.NAME = 'Pay Value'
AND TRUNC(SYSDATE) BETWEEN Paaf.EFFECTIVE_START_DATE AND paaf.EFFECTIVE_END_DATE
) Basic_Salary ,
(SELECT prrv2.result_value
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv2
,pay_input_values_x piv
,pay_element_types_x pet
,per_assignments_x paaf
,apps.pay_element_classifications pec
where ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
and paa.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
and paa.ASSIGNMENT_ID = PF.ASSIGNMENT_ID
and ppa.ACTION_TYPE = 'R'
and prr.ASSIGNMENT_ACTION_ID = paa.ASSIGNMENT_ACTION_ID
and prr.RUN_RESULT_ID = prrv2.RUN_RESULT_ID
and prrv2.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
and pet.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and pet.CLASSIFICATION_ID = pec.CLASSIFICATION_ID
and ppa.EFFECTIVE_DATE >= '01-JAN-2014' and ppa.EFFECTIVE_DATE <= '31-JAN-2014'--:PAYROLL_DATE --'23-FEB-2012'
and prrv2.INPUT_VALUE_ID=prrv.INPUT_VALUE_ID
AND pet.element_name IN ('Housing Allowance')
AND pec.CLASSIFICATION_NAME LIKE '%Earnings%'
AND piv.NAME = 'Pay Value'
AND TRUNC(SYSDATE) BETWEEN Paaf.EFFECTIVE_START_DATE AND paaf.EFFECTIVE_END_DATE
) HA_RATE,
(SELECT prrv.result_value
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv3
,pay_input_values_x piv
,pay_element_types_x pet
,per_assignments_x paaf
,apps.pay_element_classifications pec
where ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
and paa.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
and paa.ASSIGNMENT_ID = PF.ASSIGNMENT_ID
and prrv3.INPUT_VALUE_ID=prrv.INPUT_VALUE_ID
and ppa.ACTION_TYPE = 'R'
and prr.ASSIGNMENT_ACTION_ID = paa.ASSIGNMENT_ACTION_ID
and prr.RUN_RESULT_ID = prrv3.RUN_RESULT_ID
and prrv3.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
and pet.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and pet.CLASSIFICATION_ID = pec.CLASSIFICATION_ID
and ppa.EFFECTIVE_DATE >= '01-JAN-2014' and ppa.EFFECTIVE_DATE <= '31-JAN-2014'--:PAYROLL_DATE --'23-FEB-2012'
AND pet.element_name IN ('Transportation Allowance')
AND pec.CLASSIFICATION_NAME LIKE '%Earnings%'
AND piv.NAME = 'Pay Value'
AND TRUNC(SYSDATE) BETWEEN Paaf.EFFECTIVE_START_DATE AND paaf.EFFECTIVE_END_DATE
) TA_RATE,
(SELECT SUM(prrv.result_value)
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv4
,pay_input_values_x piv
,pay_element_types_x pet
,per_assignments_x paaf
,apps.pay_element_classifications pec
where ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
and paa.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
and paa.ASSIGNMENT_ID = PF.ASSIGNMENT_ID
and ppa.ACTION_TYPE = 'R'
and PRRV.INPUT_VALUE_ID=PRRV4.INPUT_VALUE_ID
and prr.ASSIGNMENT_ACTION_ID = paa.ASSIGNMENT_ACTION_ID
and prr.RUN_RESULT_ID = prrv4.RUN_RESULT_ID
and prrv4.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
and pet.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and pet.CLASSIFICATION_ID = pec.CLASSIFICATION_ID
and ppa.EFFECTIVE_DATE >= '01-JAN-2014' and ppa.EFFECTIVE_DATE <= '31-JAN-2014'--:PAYROLL_DATE --'23-FEB-2012'
and prrv4.INPUT_VALUE_ID=prrv.INPUT_VALUE_ID
AND pet.element_name NOT IN ('Basic Salary','Transportation Allowance','Housing Allowance')
AND pec.CLASSIFICATION_NAME in( 'Earnings','Direct Payment')
AND piv.NAME = 'Pay Value'
AND TRUNC(SYSDATE) BETWEEN Paaf.EFFECTIVE_START_DATE AND paaf.EFFECTIVE_END_DATE
) SUM_EARNING,
(SELECT SUM(prrv.result_value) *(-1)
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv5
,pay_input_values_x piv
,pay_element_types_x pet
,per_assignments_x paaf
,apps.pay_element_classifications pec
where ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
and paa.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
and paa.ASSIGNMENT_ID = PF.ASSIGNMENT_ID
and ppa.ACTION_TYPE = 'R'
and prrv5.INPUT_VALUE_ID=prrv.INPUT_VALUE_ID
and prr.ASSIGNMENT_ACTION_ID = paa.ASSIGNMENT_ACTION_ID
and prr.RUN_RESULT_ID = prrv5.RUN_RESULT_ID
and prrv5.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
and pet.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and pet.CLASSIFICATION_ID = pec.CLASSIFICATION_ID
and ppa.EFFECTIVE_DATE >= '01-JAN-2014' and ppa.EFFECTIVE_DATE <= '31-JAN-2014'--:PAYROLL_DATE --'23-FEB-2012'
AND pet.element_name NOT IN ('Basic Salary','Transportation Allowance','Housing Allowance')
AND pec.CLASSIFICATION_NAME in('Involuntary Deductions','Statutory Deductions','Voluntary Deductions')
AND piv.NAME = 'Pay Value'
AND TRUNC(SYSDATE) BETWEEN paaf.EFFECTIVE_START_DATE AND paaf.EFFECTIVE_END_DATE
) SUM_Deduction
FROM PER_ALL_ASSIGNMENTS_F pf,
pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv
,pay_input_values_x piv
,pay_element_types_x pet
,apps.pay_element_classifications pec
WHERE 1=1
and PF.ASSIGNMENT_NUMBER = '3363'
and ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
and paa.ASSIGNMENT_ID = pf.ASSIGNMENT_ID
and paa.ASSIGNMENT_ID = PF.ASSIGNMENT_ID
and ppa.ACTION_TYPE = 'R'
and prr.ASSIGNMENT_ACTION_ID = paa.ASSIGNMENT_ACTION_ID
and prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID
and prrv.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
and pet.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID
and pet.CLASSIFICATION_ID = pec.CLASSIFICATION_ID
and ppa.EFFECTIVE_DATE >= '01-JAN-2014' and ppa.EFFECTIVE_DATE <= '31-JAN-2014'--:PAYROLL_DATE --'23-FEB-2012'
AND piv.NAME = 'Pay Value'
AND TRUNC(SYSDATE) BETWEEN pf.EFFECTIVE_START_DATE AND pf.EFFECTIVE_END_DATE
ORDER BY ASSIGNMENT_NUMBER)
Please check the picture
-
Attachment: 1.JPG
(Size: 71.72KB, Downloaded 960 times)
|
|
|
|
| Re: The Subqury Returns So Many Rows [message #607419 is a reply to message #607417] |
Thu, 06 February 2014 02:49  |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
By far, the simplest solution might be this:
select
assignment_number,
sum(basic_salary) basic_salary,
sum(housing) housing,
sum(transportation) transportation,
sum(other_earning) other_earning,
sum(other_deduction) other_deduction,
sum(total_salary) total_salary
from (<your query goes here)
group by assignment_number;
Is it optimal? Can't tell, you'll need to wait for someone smarter than me.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 20:05:31 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
