PL SQL Formatter Enhancement

[Covina]

PLSQL Formatter Enchancement:

Your plsql formatter does not do a very good for in the where clause

I Would like the 'AND', 'OR' to be vertically aligned. The existing format screws up everything.
Also in the sub queries and the 'BETWEEN" Clause

Please Take a look at the following queries and their format:

SELECT ppa.person_analysis_id
,ppa.object_version_number
FROM PER_PERSON_ANALYSES ppa
,PER_ANALYSIS_CRITERIA pac,
(SELECT /*+ INDEX(PAC PER_ANALYSIS_CRITERIA_PK)
INDEX(PPA1 PER_PERSON_ANALYSES_PK) */
ppa1.person_id
,pac1.segment1
,MIN(ppa1.person_analysis_id) person_analysis_id
FROM PER_PERSON_ANALYSES ppa1
,PER_ANALYSIS_CRITERIA pac1
WHERE ppa1.analysis_criteria_id = pac1.analysis_criteria_id
AND ppa1.id_flex_num = lnCertInfoIdFlexNum
AND EXISTS
(SELECT 1
FROM PER_PERSON_ANALYSES ppa2
,PER_ANALYSIS_CRITERIA pac2
WHERE ppa2.analysis_criteria_id = pac2.analysis_criteria_id
AND ppa2.id_flex_num = ppa1.id_flex_num
AND ppa2.person_id = ppa1.person_id
AND pac2.segment1 = pac1.segment1
GROUP BY ppa2.person_id, pac2.segment1
HAVING COUNT(*) > 1)
GROUP BY ppa1.person_id, pac1.segment1) ppp
WHERE ppa.analysis_criteria_id = pac.analysis_criteria_id
AND ppa.id_flex_num = lnCertInfoIdFlexNum
AND ppa.person_analysis_id ppp.person_analysis_id
AND ppa.person_id = ppp.person_id
AND pac.segment1 = ppp.segment1
2nd ex:
SELECT ppf.person_id
,paa.assignment_id
,ppf.first_name
,ppf.last_name
,ppf.middle_names
,ppf.full_name
,upper(substr(ppf.email_address,1,instr(ppf.email_address,'@')-1)) emailid
,ppf.sex
,ppf.per_information1 race
,ppf.current_employee_flag status
,paa.primary_flag
,hrl.location_code
,ppd.segment1 position_title
,pps.date_start hire_date
,wcs_info_handler_pkg.get_license_type(ppf.person_id) certificate_type
,wcs_info_handler_pkg.get_certification_years_exp(ppf.person_id) certificate
,pg.name pay_grade
,ppg.segment1 contract_length
,ppd.segment8 cost_center
FROM apps.per_all_assignments_f paa
,apps.per_all_people_f ppf
,apps.per_position_definitions ppd
,apps.per_all_positions pos
,apps.per_periods_of_service pps
,apps.pay_people_groups ppg
,apps.hr_locations hrl
,apps.per_grades pg
,apps.per_person_types ppt
,apps.per_assignment_status_types pst
WHERE ppf.person_id = 44580
AND ppf.person_id = paa.person_id
AND ppf.person_type_id = ppt.person_type_id
AND ppt.system_person_type LIKE 'EMP%'
AND paa.assignment_status_type_id = pst.assignment_status_type_id
AND pst.per_system_status = 'ACTIVE_ASSIGN'
AND paa.payroll_id IS NOT NULL
AND paa.payroll_id 66
AND paa.location_id = hrl.location_id(+)
AND paa.grade_id = pg.grade_id(+)
AND paa.people_group_id = ppg.people_group_id
AND ppd.segment6 BETWEEN '111' AND '129'
AND (ppd.segment6 BETWEEN '139' AND '145'
OR ppd.segment8 IN ('0820', '0920'))
AND trunc(SYSDATE) BETWEEN paa.effective_start_date AND paa.effective_end_date
AND trunc(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND paa.position_id = pos.position_id
AND pos.position_definition_id = ppd.position_definition_id
AND ppf.person_id = pps.person_id(+)
AND ((pps.person_id IS NULL)
OR (pps.person_id IS NOT NULL
AND pps.date_start = (SELECT MAX(pps1.date_start)
FROM apps.per_periods_of_service pps1
WHERE pps1.person_id = ppf.person_id
AND pps1.date_start

 

[Marco Kalter]

We've had a lot of requests for additional control over conditions and where clauses, so this will definitely be implemented.

------------------
Marco Kalter
Allround Automations

 

[geraldwinslow]

Thanks for implementing this. I was just about to make a request for WHERE clause cleanup in the Beautifier when I saw this thread. I look forward to seeing this feature.

Gerald Winslow

 

[Roberto Amorim]

It's just what I was looking for...

How you will "makeup" this?

where x=1 and (y=2 or z=3) and h=4

after cleanup:

where x=1
and (y=2 or z=3)
and h=4

or this way....

where x=1
and ( y=2
or z=3)
and h=4