Beautifier not working correctly with correlated update statement

[rolland]

I rarely use this syntax, but I have occasion to today and noticed that SET(.....) is all placed on one line instead of a field per line. I know the beautifier rules are working because the select statement for the values is one field per line. So basically in the sample below, the = (SEL... is at column 239.

Ever seen this before? Any workaround?

UPDATE purchase_requisition PRH
    SET    (PRH.oa_requisition_header_id, PRH.oa_creation_date, PRH.oa_created_by, PRH.oa_last_update_date, PRH.oa_last_updated_by, PRH.oa_description, PRH.oa_authorization_status, PRH.oa_closed_code, PRH.modified_by, PRH.modified_date) = (SELECT OAH.requisition_header_id
                                                                                                                                                                                                                                                      ,OAH.creation_date
                                                                                                                                                                                                                                                      ,OAH.created_by
                                                                                                                                                                                                                                                      ,OAH.last_update_date
                                                                                                                                                                                                                                                      ,OAH.last_updated_by
                                                                                                                                                                                                                                                      ,OAH.description
                                                                                                                                                                                                                                                      ,OAH.authorization_status
                                                                                                                                                                                                                                                      ,OAH.closed_code
                                                                                                                                                                                                                                                      ,'system'
                                                                                                                                                                                                                                                      ,SYSDATE
                                                                                                                                                                                                                                                FROM   oa_po_requisition_headers OAH
                                                                                                                                                                                                                                                WHERE  OAH.requisition_header_id =
                                                                                                                                                                                                                                                       l_requisition_header_id)
    WHERE  PRH.Pr_Id = p_pr_id;

 

[Marco Kalter]

There is indeed no explicit support for correlated update statements. This is on the to-do list.

 

[rolland]

Thanks for the reply. So I assume the beautifier ignore tag concept doesn't exist either at this point? I saw some mention it in another post. Any ideas to sort of break the beautifier for this statement?

 

[tvergote]

Sometimes I get similar problem for other constructions that are not supported by the beautifier (like parameters in a cursor). I usually solve this by adding some fake comments to force the beautifier to take a new line.
Your example would become :
UPDATE purchase_requisition prh
SET (prh.oa_requisition_header_id --
, prh.oa_creation_date --
, prh.oa_created_by --
, prh.oa_last_update_date --
, prh.oa_last_updated_by --
, prh.oa_description --
, prh.oa_authorization_status --
, prh.oa_closed_code --
, prh.modified_by --
, prh.modified_date --
) = (SELECT oah.requisition_header_id
,oah.creation_date
,oah.created_by
,oah.last_update_date
,oah.last_updated_by
,oah.description
,oah.authorization_status
,oah.closed_code
,'system'
,SYSDATE
FROM oa_po_requisition_headers oah
WHERE oah.requisition_header_id = l_requisition_header_id)
WHERE prh.pr_id = p_pr_id;

Maybe not a perfect workaround, but at least a lot more readable....

 

[rolland]

Thank you. I ended up changing it to an implicit cursor ...For c1 in (select...) and that formatted. For the amount of data, this approach is fine. Good to know about the comment to fool the beautifier.