Beautifier Enhancement

[Covina]

Hello,

Your formatter is a annoying. Here is my sql script:
SELECT ppf.person_id
,ppf.full_name
,NVL(ppf.employee_number, ppf.attribute1) emp_num
,UPPER(SUBSTR(ppf.email_address,1,INSTR(ppf.email_address,'@')-1)) email_ID
FROM apps.per_all_assignments_f paa
,apps.per_person_types ppt
,apps.per_all_people_f ppf
,apps.per_assignment_status_types pst
WHERE ppf.person_id = paa.person_id
AND paa.primary_flag = 'Y'
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 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

not like this:
SELECT ppf.person_id
,ppf.full_name
,NVL(ppf.employee_number, ppf.attribute1) emp_num
,UPPER(SUBSTR(ppf.email_address
,1
,INSTR(ppf.email_address, '@') - 1)) email_ID
FROM apps.per_all_assignments_f paa
,apps.per_person_types ppt
,apps.per_all_people_f ppf
,apps.per_assignment_status_types pst
WHERE ppf.person_id = paa.person_id
AND paa.primary_flag = 'Y'
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 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
-----------------------------
Problem:
1.Item that contains functions or decode in select should be in one line if possible

Here is beautifier options that you should consider having:
Select/Insert/Update
Items without functions
One item per line

Items with functions
On one line if possible

2. In where clause between date1 and date2 should be in same line
3. Beautifier does not format the create table/index statements.

 

[Marco Kalter]

I have added this to the list of enhancement requests.

 

[Stian]

Hi,

Maybe I do not understand the problem here but this works fine for me in 8.0.4.

Here is what I get from the beautifier (my own custom settings):

SELECT ppf.person_id
      ,ppf.full_name
      ,NVL(ppf.employee_number, ppf.attribute1) emp_num
      ,UPPER(SUBSTR(ppf.email_address, 1, INSTR(ppf.email_address, '@') - 1)) email_ID
FROM   apps.per_all_assignments_f       paa
      ,apps.per_person_types            ppt
      ,apps.per_all_people_f            ppf
      ,apps.per_assignment_status_types pst
WHERE  ppf.person_id = paa.person_id
AND    paa.primary_flag = 'Y'
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    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;

I tried some Beautifier settings and I believe the following setting fixes problem 1:
General->Item Lists->Format: On one line if possible

Regards
Stian

 

[Stian]

In case you would like to try my settings here is my beautifying rules file:

Version=1
RightMargin=120
Indent=3
UseTabCharacter=FALSE
TabCharacterSize=3
AlignDeclarationGroups=TRUE
AlignAssignmentGroups=TRUE
KeywordCase=1
IdentifierCase=0
UseSpecialCase=TRUE
ItemList.Format=1
ItemList.Align=TRUE
ItemList.CommaAfter=FALSE
ItemList.AtLeftMargin=FALSE
EmptyLines=1
ThenOnNewLine=FALSE
LoopOnNewLine=FALSE
DML.LeftAlignKeywords=TRUE
DML.LeftAlignItems=TRUE
DML.OnOneLineIfPossible=FALSE
DML.WhereSplitAndOr=TRUE
DML.WhereAndOrAfterExpression=FALSE
DML.WhereAndOrUnderWhere=TRUE
DML.JoinSplitBeforeOn=TRUE
DML.InsertItemList.Format=2
DML.InsertItemList.Align=FALSE
DML.InsertItemList.CommaAfter=FALSE
DML.InsertItemList.AtLeftMargin=FALSE
DML.SelectItemList.Format=2
DML.SelectItemList.Align=TRUE
DML.SelectItemList.CommaAfter=FALSE
DML.SelectItemList.AtLeftMargin=FALSE
DML.UpdateItemList.Format=2
DML.UpdateItemList.Align=TRUE
DML.UpdateItemList.CommaAfter=FALSE
DML.UpdateItemList.AtLeftMargin=FALSE
ParameterDeclarationList.Format=2
ParameterDeclarationList.Align=TRUE
ParameterDeclarationList.CommaAfter=FALSE
ParameterDeclarationList.AtLeftMargin=FALSE
RecordFieldList.Format=1
RecordFieldList.Align=TRUE
RecordFieldList.CommaAfter=FALSE
RecordFieldList.AtLeftMargin=FALSE
SplitAndOr=FALSE
AndOrAfterExpression=FALSE
[SpecialCase]

Regards
Stian