rbrooker
Member³
Seems case statements within a merge statement cause the beautifier all kinds of grief ...
then there are the instances where some case statements are beautified and some are not ...
my beautifier settings are :
Are you able to look at these please? i can send you the packages in their entirety if you would like ...
Thank you.
Code:
MERGE INTO eit_client_technology_report a
USING (SELECT pi_idclient id_client
,pi_idtechnology id_technology
,to_date(pi_idmonth, 'DD-MON-YYYY') id_month
,pi_writer report_writer
,pi_qa report_qa
FROM dual) b
ON (a.id_client = b.id_client AND a.id_technology = b.id_technology AND a.id_month = b.id_month)
WHEN MATCHED THEN
UPDATE
SET a.report_writer = CASE b.report_writer
WHEN 0 THEN
a.report_writer
ELSE
b.report_writer
END
,a.report_write_complete = CASE b.report_writer
WHEN 0 THEN
a.report_write_complete
ELSE
NULL
END
,a.report_qa = CASE b.report_qa
WHEN 0 THEN
a.report_qa
ELSE
b.report_qa
END
,a.report_qa_complete = CASE b.report_qa
WHEN 0 THEN
a.report_qa_complete
ELSE
NULL
END WHEN NOT MATCHED THEN INSERT(id_client, id_technology, id_month, report_assigned, report_writer, report_qa) VALUES(b.id_client, b.id_technology, b.id_month, v('ID_USR'), b.report_writer, b.report_qa);
Code:
CASE pi_location
WHEN '---' THEN
DELETE war_location
WHERE id_staff = v('ID_USR')
AND war_date = to_date(pi_date, 'DD-MON-YYYY');
ELSE
MERGE INTO war_location a
USING (SELECT v('ID_USR') id_staff
,to_date(pi_date, 'DD-MON-YYYY') war_date
,pi_scope war_scope
,pi_location war_location
,pi_comment war_comment
,SYSDATE udts
,v('ID_USR') udby
FROM dual) b
ON (a.id_staff = b.id_staff AND a.war_date = b.war_date)
WHEN MATCHED THEN
UPDATE
SET a.war_location_am = CASE b.war_scope
WHEN 'A' THEN
b.war_location
WHEN 'W' THEN
b.war_location
ELSE
a.war_location_am
END
,a.war_location_pm = CASE b.war_scope
WHEN 'P' THEN
b.war_location
WHEN 'W' THEN
b.war_location
ELSE
a.war_location_pm
END
,a.udts = b.udts
,a.udby = b.udby DELETE
WHERE nvl(a.war_location_am, '---') = '---'
AND nvl(a.war_location_pm, '---') = '---' WHEN NOT MATCHED THEN INSERT(id_staff, war_date, war_location_am, war_location_pm, war_comment, udts, udby) VALUES(b.id_staff, b.war_date,CASE b.war_scope
WHEN 'A' THEN
b.war_location
WHEN 'W' THEN
b.war_location
ELSE
NULL
END,CASE b.war_scope
WHEN 'P' THEN
b.war_location
WHEN 'W' THEN
b.war_location
ELSE
NULL
END, b.war_comment, b.udts, b.udby);
END CASE;then there are the instances where some case statements are beautified and some are not ...
Code:
IF (v_currentdate < trunc(current_date + 365))
THEN
IF (r.week_sunday IS NOT NULL)
THEN
INSERT INTO war_location
(id_staff
,war_date
,war_location_am
,war_location_pm
,udts
,udby)
VALUES
(v('ID_USR')
,v_currentdate + 0
,CASE instr(nvl(r.week_sunday, '-'), '/')
WHEN 0 THEN
r.week_sunday
ELSE
get_nthfield(r.week_sunday, '/', 1)
END
,CASE instr(nvl(r.week_sunday, '-'), '/')
WHEN 0 THEN
r.week_sunday
ELSE
get_nthfield(r.week_sunday, '/', 2)
END
,SYSDATE
,v('ID_USR'));
END IF;
IF (r.week_monday IS NOT NULL)
THEN
INSERT INTO war_location
(id_staff
,war_date
,war_location_am
,war_location_pm
,udts
,udby)
VALUES
(v('ID_USR')
,v_currentdate + 1
,CASE instr(nvl(r.week_monday, '-'), '/') WHEN 0 THEN r.week_monday ELSE get_nthfield(r.week_monday, '/', 1) END
,CASE instr(nvl(r.week_monday, '-'), '/') WHEN 0 THEN r.week_monday ELSE get_nthfield(r.week_monday, '/', 2) END
,SYSDATE
,v('ID_USR'));
END IF;
there are more entries for each daymy beautifier settings are :
Code:
Version=1
RightMargin=999
Indent=4
UseTabCharacter=FALSE
TabCharacterSize=4
AlignDeclarationGroups=FALSE
AlignAssignmentGroups=FALSE
KeywordCase=1
IdentifierCase=2
UseSpecialCase=TRUE
ItemList.Format=0
ItemList.Align=FALSE
ItemList.CommaAfter=FALSE
ItemList.AtLeftMargin=FALSE
EmptyLines=1
ThenOnNewLine=TRUE
LoopOnNewLine=TRUE
DML.LeftAlignKeywords=TRUE
DML.LeftAlignItems=FALSE
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=FALSE
DML.SelectItemList.CommaAfter=FALSE
DML.SelectItemList.AtLeftMargin=FALSE
DML.UpdateItemList.Format=2
DML.UpdateItemList.Align=FALSE
DML.UpdateItemList.CommaAfter=FALSE
DML.UpdateItemList.AtLeftMargin=FALSE
ParameterDeclarationList.Format=2
ParameterDeclarationList.Align=FALSE
ParameterDeclarationList.CommaAfter=FALSE
ParameterDeclarationList.AtLeftMargin=TRUE
RecordFieldList.Format=1
RecordFieldList.Align=FALSE
RecordFieldList.CommaAfter=FALSE
RecordFieldList.AtLeftMargin=FALSE
SplitAndOr=TRUE
AndOrAfterExpression=FALSE
[SpecialCase]Are you able to look at these please? i can send you the packages in their entirety if you would like ...
Thank you.
