Using Order by in a Union 1

[tokerago13]

Hi,
I'm trying to use order by in a union statement. My columns are concatenated as I'm spooling to a csv file.
How can I get this to work as the coumns are not mentioned individually. An extract of the sql is below:

SELECT pcak.concatenated_segments||','||
ppf.employee_number||','||
ppf.last_name||' '||ppf.first_name||','||
fpr.fpr_context||','||
fpr.fpr_value||','||
fpr.effective_start_date||','||
TRUNC(fpr.fpr_change_date)||','||
fnd.user_name
FROM fnd_user fnd,
fsahr_pay_review_people fpr,
per_periods_of_service ppos,
per_all_people_f ppf,
per_all_assignments_f paaf,
pay_cost_allocations_f pca,
pay_cost_allocation_keyflex pcak
WHERE fnd.user_id = fpr.fpr_updated_by
AND fpr.fpr_context = 'STARTER'
AND fpr.person_id = ppos.person_id
AND ppos.person_id = ppf.person_id
AND (TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
OR paaf.effective_start_date > TRUNC(SYSDATE))
AND ppf.person_id = paaf.person_id
AND (TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
OR paaf.effective_start_date > TRUNC(SYSDATE))
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.assignment_id = pca.assignment_id
AND (TRUNC(SYSDATE) BETWEEN pca.effective_start_date AND pca.effective_end_date
OR ppf.effective_start_date > TRUNC(SYSDATE))
AND pca.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
UNION
SELECT pcak.concatenated_segments||','||
ppf.employee_number||','||
ppf.last_name||' '||ppf.first_name||','||
fpr.fpr_context||','||
fpr.fpr_value||','||
fpr.effective_start_date||','||
TRUNC(fpr.fpr_change_date)||','||
fnd.user_name
FROM fnd_user fnd,
fsahr_pay_review_people fpr,
per_periods_of_service ppos,
per_all_people_f ppf,
per_all_assignments_f paaf,
pay_cost_allocations_f pca,
pay_cost_allocation_keyflex pcak
WHERE fnd.user_id = fpr.fpr_updated_by
AND SUBSTR(fpr.fpr_context,-6) = 'LEAVER'
AND fpr.person_id = ppos.person_id
AND ppos.person_id = ppf.person_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
--AND TRUNC(fpr.fpr_change_date) = TRUNC(SYSDATE)
AND ppf.person_id = paaf.person_id
AND paaf.effective_end_date = (SELECT MAX(paaf1.effective_end_date)
FROM per_all_assignments_f paaf1
WHERE paaf1.person_id = paaf.person_id
AND paaf1.assignment_type = 'E')
AND paaf.primary_flag = 'Y'
AND paaf.assignment_id = pca.assignment_id
AND pca.effective_end_date = ( SELECT MAX(pca1.effective_end_date)
FROM pay_cost_allocations_f pca1
WHERE pca1.assignment_id = pca.assignment_id)
AND pca.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id;

Hope to hear from someone soon...
Thanks.

T

 

[sem]

What fields do you need to sort it by? Your UNION will be probably sorted by the whole concatenated string. You can not sort UNION subqueries individually. You may consider using SET COLSEP , instead of concatenating all fields. Another way is to select but not show columns you need in ORDER BY clause:

COLUMN EMPLOYEE_NUMBER NOPRINT

SELECT ....,EMPLOYEE_NUMBER
UNION
SELECT ....,EMPLOYEE_NUMBER
OREDR BY EMPLOYEE_NUMBER

It seems to me that you may use OR instead of UNION and single query.



Regards, Dima

http://seminihin.narod.ru

 

[tokerago13]

Thanks a lot Dima.... I used the OR clause instead and made it a single query. This eliminated the problem