💎SQL Reloaded: Copilot Stories (Part III: Formatting Code via Prompts)

When debugging poorly formatted code, usually it helps to format first the code before doing any other work on it. I observed in the previous post that Copilot can be used to restructure the code based on a set of commands that summarize the best practices. 

Example 1: Simple Query

Let's start with a simple example from the web: 

Prompt:
Format the below code by using the available SQL best practices
1) each join condition is written on its own line
2) the commas are placed at the beginning of each line
3) use 3 letter based aliases for all tables
4) compact the code by reducing the indentation

SELECT po.ExpectedDeliveryDate, po.OrderDate, pol.LastReceiptDate, pol.LastEditedWhen, p.ValidFrom, p.ValidTo FROM Purchasing.PurchaseOrders AS po JOIN Purchasing.PurchaseOrderLines AS pol ON pol.PurchaseOrderID = po.PurchaseOrderID JOIN Application.People AS p ON p.PersonID = po.ContactPersonID WHERE LastReceiptDate > '1/1/2016';

Output:

SELECT po.ExpectedDeliveryDate
    , po.OrderDate
    , pol.LastReceiptDate
    , pol.LastEditedWhen
    , p.ValidFrom
    , p.ValidTo
FROM Purchasing.PurchaseOrders AS po
JOIN Purchasing.PurchaseOrderLines AS pol
    ON pol.PurchaseOrderID = po.PurchaseOrderID
JOIN Application.People AS p
    ON p.PersonID = po.ContactPersonID
WHERE LastReceiptDate > '1/1/2016';

Of course, the colors were added by the hilite.me formatting to better identify the reserved words. The output of the above prompt is not exactly what I intended in 3), so repeating the slightly changed command seems to obtain the expected result. Even if a 3-letter alias was not used for all tables, the query looks better:

Prompt:
Use 3 letter based aliases for all tables.

Output:

SELECT po.ExpectedDeliveryDate
    , po.OrderDate
    , pol.LastReceiptDate
    , pol.LastEditedWhen
    , ppl.ValidFrom
    , ppl.ValidTo
FROM Purchasing.PurchaseOrders AS po
JOIN Purchasing.PurchaseOrderLines AS pol
    ON pol.PurchaseOrderID = po.PurchaseOrderID
JOIN Application.People AS ppl
    ON ppl.PersonID = po.ContactPersonID
WHERE LastReceiptDate > '1/1/2016';

The script looks good, though it would be great if we could align the constraints with the order of the tables.

Prompt:
Rewrite the query by using the first table from the join in the join constraint.

Output:

SELECT po.ExpectedDeliveryDate
    , po.OrderDate
    , pol.LastReceiptDate
    , pol.LastEditedWhen
    , ppl.ValidFrom
    , ppl.ValidTo
FROM Purchasing.PurchaseOrders AS po
JOIN Purchasing.PurchaseOrderLines AS pol
    ON po.PurchaseOrderID = pol.PurchaseOrderID
JOIN Application.People AS ppl
    ON po.ContactPersonID = ppl.PersonID
WHERE LastReceiptDate > '1/1/2016';

As observed in the last post, multiple scripts can be transformed withing the same prompt, especially when the statements were generated by Copilot as well. 

Example 2: Complex Dynamics AX Query

How about a more complex query? 
Let's consider the below query written for Dynamics AX:

Prompt:
Format the below code by using the available SQL best practices
1) each join condition is written on its own line
2) the commas are placed at the beginning of each line
3) use 3 letter based aliases for all tables
4) compact the code by reducing the indentation

SELECT ADR.DATAAREAID, DPT.FIRSTNAME, DPT.LASTNAME, DPT.LANGUAGEID, DPR.PartyId, DPR.IsPrimary, CASE DPR.Status WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' END Status, NullIf(Cast(DPR.ValidFromDateTime as Date), '31-12-1900') ValidFromDateTime, NullIf(Cast(DPR.ValidToDateTime as Date), '30-12-2154') ValidToDateTime, DPR.Shared  , ADR.ADDRTABLEID , ADR.ADDRRECID , ADR.TYPE , CASE ADR.TYPE WHEN 0 THEN 'None' WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Delivery'WHEN 3 THEN 'Alt. delivery'WHEN 4 THEN 'SWIFT'WHEN 5 THEN 'Payment' WHEN 6 THEN 'Service' WHEN 7 THEN 'Home' WHEN 8 THEN 'Other' WHEN 9 THEN 'Business' WHEN 10 THEN 'RemitTo' WHEN 11 THEN 'Third-party shipping address' END AddressDescription, ADR.NAME , ADR.ADDRESS , ADR.STREET, ADR.ZIPCODE , ADR.CITY , ADR.STATE , ADR.COUNTY , ADR.COUNTRYREGIONID , ADR.PHONE , ADR.TELEFAX , ADR.URL FROM ADDRESS ADR JOIN DIRPARTYADDRESSRELATIONSHI1066 DAC ON DAC.REFCOMPANYID = ADR.DATAAREAID and DAC.ADDRESSRECID = ADR.RECID and DAC.DATAAREAID = ADR.DATAAREAID JOIN DIRPARTYADDRESSRELATIONSHIP DPR on DPR.RECID = DAC.PARTYADDRESSRELATIONSHIPRECID and DPR.DATAAREAID = DAC.DATAAREAID JOIN DIRPARTYTABLE DPT on DPT.PARTYID = DPR.PARTYID and DPT.DATAAREAID = DPR.DATAAREAID  JOIN EmplTable EMP on DPT.PARTYID = EMP.PARTYID and DPT.DATAAREAID = EMP.DATAAREAID  

Output:

SELECT 
    ADR.DATAAREAID
    , DPT.FIRSTNAME
    , DPT.LASTNAME
    , DPT.LANGUAGEID
    , DPR.PartyId
    , DPR.IsPrimary
    , CASE DPR.Status 
        WHEN 0 THEN 'Active' 
        WHEN 1 THEN 'Inactive' 
      END AS Status
    , NullIf(Cast(DPR.ValidFromDateTime AS DATE), '31-12-1900') AS ValidFromDateTime
    , NullIf(Cast(DPR.ValidToDateTime AS DATE), '30-12-2154') AS ValidToDateTime
    , DPR.Shared
    , ADR.ADDRTABLEID
    , ADR.ADDRRECID
    , ADR.TYPE
    , CASE ADR.TYPE 
        WHEN 0 THEN 'None' 
        WHEN 1 THEN 'Invoice' 
        WHEN 2 THEN 'Delivery'
        WHEN 3 THEN 'Alt. delivery'
        WHEN 4 THEN 'SWIFT'
        WHEN 5 THEN 'Payment' 
        WHEN 6 THEN 'Service' 
        WHEN 7 THEN 'Home' 
        WHEN 8 THEN 'Other' 
        WHEN 9 THEN 'Business' 
        WHEN 10 THEN 'RemitTo' 
        WHEN 11 THEN 'Third-party shipping address' 
      END AS AddressDescription
    , ADR.NAME
    , ADR.ADDRESS
    , ADR.STREET
    , ADR.ZIPCODE
    , ADR.CITY
    , ADR.STATE
    , ADR.COUNTY
    , ADR.COUNTRYREGIONID
    , ADR.PHONE
    , ADR.TELEFAX
    , ADR.URL
FROM 
    ADDRESS ADR
JOIN 
    DIRPARTYADDRESSRELATIONSHI1066 DAC 
    ON DAC.REFCOMPANYID = ADR.DATAAREAID 
    AND DAC.ADDRESSRECID = ADR.RECID 
    AND DAC.DATAAREAID = ADR.DATAAREAID
JOIN 
    DIRPARTYADDRESSRELATIONSHIP DPR 
    ON DPR.RECID = DAC.PARTYADDRESSRELATIONSHIPRECID 
    AND DPR.DATAAREAID = DAC.DATAAREAID
JOIN 
    DIRPARTYTABLE DPT 
    ON DPT.PARTYID = DPR.PARTYID 
    AND DPT.DATAAREAID = DPR.DATAAREAID
JOIN 
    EmplTable EMP 
    ON DPT.PARTYID = EMP.PARTYID 
    AND DPT.DATAAREAID = EMP.DATAAREAID;

The query looks good, though it needs more transformations:

Input (to be run line by line):

Rewrite the query by writing names in camel with the beginning letter in upper case.
Rewrite the query by writing table aliases in upper case.

Output:

SELECT 
      ADR.DataAreaId
    , DPT.FirstName
    , DPT.LastName
    , DPT.LanguageId
    , DPR.PartyId
    , DPR.IsPrimary
    , CASE DPR.Status 
        WHEN 0 THEN 'Active' 
        WHEN 1 THEN 'Inactive' 
      END AS Status
    , NullIf(Cast(DPR.ValidFromDateTime AS DATE), '31-12-1900') AS ValidFromDateTime
    , NullIf(Cast(DPR.ValidToDateTime AS DATE), '30-12-2154') AS ValidToDateTime
    , DPR.Shared
    , ADR.AddrTableId
    , ADR.AddrRecId
    , ADR.Type
    , CASE ADR.Type 
        WHEN 0 THEN 'None' 
        WHEN 1 THEN 'Invoice' 
        WHEN 2 THEN 'Delivery' 
        WHEN 3 THEN 'Alt. Delivery' 
        WHEN 4 THEN 'Swift' 
        WHEN 5 THEN 'Payment' 
        WHEN 6 THEN 'Service' 
        WHEN 7 THEN 'Home' 
        WHEN 8 THEN 'Other' 
        WHEN 9 THEN 'Business' 
        WHEN 10 THEN 'RemitTo' 
        WHEN 11 THEN 'Third-Party Shipping Address' 
      END AS AddressDescription
    , ADR.Name
    , ADR.Address
    , ADR.Street
    , ADR.ZipCode
    , ADR.City
    , ADR.State
    , ADR.County
    , ADR.CountryRegionId
    , ADR.Phone
    , ADR.Telefax
    , ADR.Url
FROM 
    dbo.Address ADR
JOIN 
    dbo.DirPartyAddressRelationship1066 DAC 
    ON DAC.RefCompanyId = ADR.DataAreaId 
    AND DAC.AddressRecId = ADR.RecId 
    AND DAC.DataAreaId = ADR.DataAreaId
JOIN 
    dbo.DirPartyAddressRelationship DPR 
    ON DPR.RecId = DAC.PartyAddressRelationshipRecId 
    AND DPR.DataAreaId = DAC.DataAreaId
JOIN 
    dbo.DirPartyTable DPT 
    ON DPT.PartyId = DPR.PartyId 
    AND DPT.DataAreaId = DPR.DataAreaId
JOIN 
    dbo.EmplTable EMP 
    ON DPT.PartyId = EMP.PartyId 
    AND DPT.DataAreaId = EMP.DataAreaId;

Example 3: Simple X++ Query

How about code written in other languages (e.g. X++)?

I tried to transform the definition behind X++ form from Dynamics 365 for Finance without effect

    InventItemPurchSetup.CalendarDays = NoYes::No;
    InventItemPurchSetup.HighestQty = 0.00;
    InventItemPurchSetup.InventDimId = "AllBlank";
    InventItemPurchSetup.InventDimIdDefault = "AllBlank";
    InventItemPurchSetup.ItemId = "1000";
    InventItemPurchSetup.LeadTime = 0;
    InventItemPurchSetup.LowestQty = 0.00;
    InventItemPurchSetup.MandatoryInventLocation = NoYes::No;
    InventItemPurchSetup.MandatoryInventSite = NoYes::No;
    InventItemPurchSetup.MultipleQty = 0.00;
    InventItemPurchSetup.Override = NoYes::No;
    InventItemPurchSetup.StandardQty = 0.00;
    InventItemPurchSetup.Stopped = NoYes::No;
    InventItemPurchSetup.Sequence = 0;
    InventItemPurchSetup.OverrideDefaultStorageDimensions = NoYes::No;
    InventItemPurchSetup.SysRowVersionNumber = 0;
    InventItemPurchSetup.insert();

Multiple attempts were performed without any progress, though after a break I was able to apply the following transformations:

Prompts:

Replace the word "InventItemPurchSetup") with ", IPS." and remove the text after the equality sign.
Add a comma in front of each line and remove the semicolon at the end.

With a few manual change the code becomes:

SELECT IPS.CalendarDays 
, IPS.HighestQty 
, IPS.InventDimId 
, IPS.InventDimIdDefault 
, IPS.ItemId 
, IPS.LeadTime 
, IPS.LowestQty 
, IPS.MandatoryInventLocation 
, IPS.MandatoryInventSite 
, IPS.MultipleQty 
, IPS.Override 
, IPS.StandardQty 
, IPS.Stopped 
, IPS.Sequence 
, IPS.OverrideDefaultStorageDimensions 
, IPS.SysRowVersionNumber 
FROM dbo.InventItemPurchSetup IPs

The prompts reflect what I was doings in Excel manually via a replace, respectively the use of "Text to columns" functionality".

 

Probably, there's the opportunity to test further various ideas. Frankly, the transformation from upper case to camel could have helped me in the past a lot!

Disclaimer:
The queries were not tested after the changes were performed (because of unavailability of the systems).

Previous Post <<||>> Next Post