Lets Create A Sticky

[MajP]

There are some really good sql formatters out there and we should point users to using them when posting sql strings. So recently a user posted this and asked for help
and posted this. This is just an example and not to criticize the OP.

INSERT INTO BatchedFilesTable ( [Nz(PensionNo)], [Nz(SurnameInitials)], [Nz(ChildID)], [Nz(Type)], [Nz(TDate)], [Nz(ResCode)], [Nz(District)], [Nz(LocalOffice)], [Nz(BRMBarcode)], [Nz(BatchNo)], [Nz(UserID)] )
SELECT Forms!frmFileCapture!TxtPension AS Expr1, Forms!frmFileCapture!TxtSurname AS Expr2, Forms!frmFileCapture!TxtChildID AS Expr3, Forms!frmFileCapture!TxtType AS Expr4, Forms!frmFileCapture!TxtDate AS Expr5, Forms!frmFileCapture!TxtRescode AS Expr6, Forms!frmFileCapture!TxtDistrict AS Expr7, Forms!frmFileCapture!TxtLocalOffice AS Expr8, [Forms]![frmFileCapture]![TxtBRMBarcodeNo] AS Expr9, [Forms]![frmFileCapture]![TxtBatchNo] AS Expr10, [Forms]![frmFileCapture]![TxtUserID] AS Expr11

I cannot read in that format but if I use my favorite http://www.dpriver.com/pp/sqlformat.htm I drop it in and get this

INSERT INTO batchedfilestable

            ([nz(pensionno)],

             [nz(surnameinitials)],

             [nz(childid)],

             [nz(type)],

             [nz(tdate)],

             [nz(rescode)],

             [nz(district)],

             [nz(localoffice)],

             [nz(brmbarcode)],

             [nz(batchno)],

             [nz(userid)])

SELECT forms ! frmfilecapture ! txtpension            AS Expr1,

       forms ! frmfilecapture ! txtsurname            AS Expr2,

       forms ! frmfilecapture ! txtchildid            AS Expr3,

       forms ! frmfilecapture ! txttype               AS Expr4,

       forms ! frmfilecapture ! txtdate               AS Expr5,

       forms ! frmfilecapture ! txtrescode            AS Expr6,

       forms ! frmfilecapture ! txtdistrict           AS Expr7,

       forms ! frmfilecapture ! txtlocaloffice        AS Expr8,

       [forms] ! [frmfilecapture] ! [txtbrmbarcodeno] AS Expr9,

       [forms] ! [frmfilecapture] ! [txtbatchno]      AS Expr10,

       [forms] ! [frmfilecapture] ! [txtuserid]       AS Expr11

 

[MajP]

Maybe a better example. The OP said here is my query where I am having an issue

SELECT [IATA Code].IATACode, Weight.Weight, UPSMY_9428EA_Rates.Rates, Fedex_478457529_Priority.Rates, Fedex_300541852_Priority.Rates, DHLTCM_Rates.Rates, IIf([DHLTCM_Rates]![Rates]>[Fedex_300541852_Priority]![Rates],"FDXMY_300541852_Priority",(IIf([DHLTCM_Rates]![Rates]>[Fedex_478457529_Priority]![Rates],"FDXMY_478457529_Priority",(IIf([DHLTCM_Rates]![Rates]>[UPSMY_9428EA_Rates]![Rates],"UPSMY_9428EA","DHLTCM"))))) AS Carrier, IIf([SFexpress_Rates]![Rates]>[UPSMY_9428EA_Rates]![Rates],"UPSMY_9428EA",(IIf([SFexpress_Rates]![Rates]>[Fedex_478457529_Priority]![Rates],"FDXYMY_478457529_Priority",(IIf([SFexpress_Rates]![Rates]>[Fedex_300541852_Priority]![Rates],"FDXMY_300541852_Priority",(IIf([SFexpress_Rates]![Rates]>[DHLTCM_Rates]![Rates],"DHLTCM","SFexpressMY"))))))) AS carrier2
FROM (SFexpress_Zoning INNER JOIN ((((Fedex_300541852_Priority INNER JOIN ((((((([IATA Code] INNER JOIN ADS_Zoning ON [IATA Code].IATACode = ADS_Zoning.[IATA Code]) INNER JOIN ADS_Rates ON ADS_Zoning.Zone = ADS_Rates.Zone) INNER JOIN Weight ON ADS_Rates.Weight = Weight.Weight) INNER JOIN UPS_Zoning ON [IATA Code].IATACode = UPS_Zoning.[IATA Code]) INNER JOIN UPSMY_9428EA_Rates ON (Weight.Weight = UPSMY_9428EA_Rates.Weight) AND (UPS_Zoning.Zone = UPSMY_9428EA_Rates.Zone)) INNER JOIN Fedex_Zoning ON [IATA Code].IATACode = Fedex_Zoning.[IATA Code]) INNER JOIN Fedex_478457529_Priority ON (Fedex_478457529_Priority.Weight = Weight.Weight) AND (Fedex_Zoning.Zone = Fedex_478457529_Priority.Zone)) ON Fedex_300541852_Priority.Weight = Weight.Weight) INNER JOIN Fedex_Zoning AS Fedex_Zoning_1 ON (Fedex_Zoning_1.Zone = Fedex_300541852_Priority.Zone) AND ([IATA Code].IATACode = Fedex_Zoning_1.[IATA Code])) INNER JOIN DHLTCM_Zoning ON [IATA Code].IATACode = DHLTCM_Zoning.[IATA Code]) INNER JOIN DHLTCM_Rates ON (DHLTCM_Rates.Weight = Weight.Weight) AND (DHLTCM_Zoning.Zone = DHLTCM_Rates.Zone)) ON SFexpress_Zoning.[IATA Code] = [IATA Code].IATACode) INNER JOIN SFexpress_Rates ON (SFexpress_Rates.Weight = Weight.Weight) AND (SFexpress_Zoning.Zone = SFexpress_Rates.Zone)
WHERE ((([IATA Code].IATACode)="CA") AND ((Weight.Weight)=2))

ELECT     [IATA Code].iatacode,
           weight.weight,
           upsmy_9428ea_rates.rates,
           fedex_478457529_priority.rates,
           fedex_300541852_priority.rates,
           dhltcm_rates.rates,
           Iif(
                      [DHLTCM_Rates]![Rates]>[Fedex_300541852_Priority]![Rates],"FDXMY_300541852_Priority",(
                      Iif(
                                 [DHLTCM_Rates]![Rates]>[Fedex_478457529_Priority]![Rates],"FDXMY_478457529_Priority",(
                                 Iif(
                                            [DHLTCM_Rates]![Rates]>[UPSMY_9428EA_Rates]![Rates],"UPSMY_9428EA","DHLTCM"))))) AS carrier,
           Iif(
                      [SFexpress_Rates]![Rates]>[UPSMY_9428EA_Rates]![Rates],"UPSMY_9428EA",(
                      Iif(
                                 [SFexpress_Rates]![Rates]>[Fedex_478457529_Priority]![Rates],"FDXYMY_478457529_Priority",(
                                 Iif(
                                            [SFexpress_Rates]![Rates]>[Fedex_300541852_Priority]![Rates],"FDXMY_300541852_Priority",(
                                            Iif(
                                                       [SFexpress_Rates]![Rates]>[DHLTCM_Rates]![Rates],"DHLTCM","SFexpressMY"))))))) AS carrier2
FROM       (sfexpress_zoning
INNER JOIN ((((fedex_300541852_priority
INNER JOIN ((((((([IATA Code]
INNER JOIN ads_zoning
ON         [IATA Code].iatacode = ads_zoning.[IATA Code])
INNER JOIN ads_rates
ON         ads_zoning.zone = ads_rates.zone)
INNER JOIN weight
ON         ads_rates.weight = weight.weight)
INNER JOIN ups_zoning
ON         [IATA Code].iatacode = ups_zoning.[IATA Code])
INNER JOIN upsmy_9428ea_rates
ON         (
                      weight.weight = upsmy_9428ea_rates.weight)
AND        (
                      ups_zoning.zone = upsmy_9428ea_rates.zone))
INNER JOIN fedex_zoning
ON         [IATA Code].iatacode = fedex_zoning.[IATA Code])
INNER JOIN fedex_478457529_priority
ON         (
                      fedex_478457529_priority.weight = weight.weight)
AND        (
                      fedex_zoning.zone = fedex_478457529_priority.zone))
ON         fedex_300541852_priority.weight = weight.weight)
INNER JOIN fedex_zoning AS fedex_zoning_1
ON         (
                      fedex_zoning_1.zone = fedex_300541852_priority.zone)
AND        (
                      [IATA Code].iatacode = fedex_zoning_1.[IATA Code]))
INNER JOIN dhltcm_zoning
ON         [IATA Code].iatacode = dhltcm_zoning.[IATA Code])
INNER JOIN dhltcm_rates
ON         (
                      dhltcm_rates.weight = weight.weight)
AND        (
                      dhltcm_zoning.zone = dhltcm_rates.zone))
ON         sfexpress_zoning.[IATA Code] = [IATA Code].iatacode)
INNER JOIN sfexpress_rates
ON         (
                      sfexpress_rates.weight = weight.weight)
AND        (
                      sfexpress_zoning.zone = sfexpress_rates.zone)
WHERE      (((
                                            [IATA Code].iatacode)="CA")
           AND        ((
                                            weight.weight)=2))[frmFileCapture]![TxtBatchNo] AS expr10,
           [Forms]![frmFileCapture]![TxtUserID]                                             AS expr11

I at least could give it a try.

 

[The_Doc_Man]

yeesh! A 13-way INNER JOIN. The worse I had ever seen was 15 for the Navy's personnel database. As a matter of fact, that was the worst I was ever GOING to see because at the time we were using a proprietary SQL that had a 15-JOIN limit. The salespeople for ShareBase looked at that and visibly swallowed in anticipation of it blowing up their software. But it actually ran.

The only issue I would see is proper permission and attribution. Also, it USED to be the case that Jon would try to not push someone else's product on the forum without it becoming a source of an advertising revenue stream. After all, there ARE ads associated with the forum. But that topic of ad revenue hasn't come up lately so I don't know what he would say. I definitely would pass that by Jon for consideration.

 

[MajP]

The only issue I would see is proper permission and attribution. Also, it USED to be the case that Jon would try to not push someone else's product on the forum without it becoming a source of an advertising revenue stream. After all, there ARE ads associated with the forum. But that topic of ad revenue hasn't come up lately so I don't know what he would say. I definitely would pass that by Jon for consideration.

That is a good point. Maybe keep it generic like "Here is an example of formatted code and there are formatters out there." Let the user Google it. Or maybe @Jon has some relation with another site (not sure how that works).

 

[Jon]

Mention the product, I'm happy with that. Just don't link to it. The problem is, it is a slippery slope. You end up with anyone who wants to get a back-link to their website coming here and making a post, not to contribute, but to assist with their marketing.

 

[jdraw]

I know there are several SQL formatters -- I use Poor Sql which is free. I saw it referenced in a thread one time and have used it when posting since.

Also https://www.freeformatter.com/sql-formatter.html has options for other languages.