Reputation: 15358
As most of you would know, when you save a view in MYSQL and then retrieve it later, it's all in native MYSQL, looks terrible - no line spaces, indents, plenty of UTF encoding tags littered around the place making your code look like sql soup.
Does anyone have a program or recommend a plugin to netbeans that i can use to format this sql. Here's my view, give formatting it a go and repost it as an example if you like :)
/* ALGORITHM=UNDEFINED */ SELECT `e`.`Australia` AS `audit_anps`,`e`.`audit_end_date` AS `audit_auditenddate`,`e`.`BumperIssueDates` AS `audit_bumperissuedates`,`e`.`BumperIssuesLinked` AS `audit_bumperissuelinked`,`e`.`BumperIssues` AS `audit_bumperissues`,`e`.`bundledSales` AS `audit_bundledsales`,`e`.`CoverPrice` AS `audit_coverprice`,`e`.`CoverPrice2` AS `audit_coverprice2`,`e`.`CoverPrice2Day` AS `audit_coverprice2day`,`e`.`CoverPrice3` AS `audit_coverprice3`,`e`.`CoverPrice3Day` AS `audit_coverprice3day`,`e`.`CoverPrice4` AS `audit_coverprice4`,`e`.`CoverPrice4Day` AS `audit_coverprice4day`,`e`.`CoverPriceDay` AS `audit_coverpriceday`,`e`.`timestamp` AS `audit_datecreated`,`e`.`weeksbetween` AS `audit_daysbetween`,_utf8'' AS `audit_dayspublished`,`e`.`AustraliaDigital` AS `audit_digital_anps`,`e`.`BumperIssueDatesD` AS `audit_digital_bumperissuedates`,`e`.`BumperIssuesD` AS `audit_digital_bumperissues`,`e`.`BumperIssuesLinkedD` AS `audit_digital_bumperissueslinked`,`e`.`bundledSalesDigital` AS `audit_digital_bundledsalesdigital`,`e`.`CoverPriceD` AS `audit_digital_coverprice`,`e`.`CoverPrice2D` AS `audit_digital_coverprice2`,`e`.`CoverPrice2DayD` AS `audit_digital_coverprice2day`,`e`.`CoverPrice3D` AS `audit_digital_coverprice3`,`e`.`CoverPrice3DayD` AS `audit_digital_coverprice3day`,`e`.`CoverPrice4D` AS `audit_digital_coverprice4`,`e`.`CoverPrice4DayD` AS `audit_digital_coverprice4day`,`e`.`CoverPriceDayD` AS `audit_digital_coverpriceday`,`e`.`ExcludedDatesD` AS `audit_digital_excludeddates`,`e`.`inclAccomAirlinesSalesDigital` AS `audit_digital_inclaccomairlinesales`,`e`.`inclEducationalSalesDigital` AS `audit_digital_incleducationalsales`,`e`.`inclEventSalesDigital` AS `audit_digital_incleventsales`,`e`.`inclMultiplePublicationSalesDigital` AS `audit_digital_inclmultiplepublicationsales`,`e`.`IssuesD` AS `audit_digital_issues`,`e`.`IssuesAuditedD` AS `audit_digital_issuesaudited`,`e`.`NonPublishingDatesD` AS `audit_digital_nonpublishingdates`,`e`.`RemarksD` AS `audit_digital_remarks`,`e`.`exclAustraliaOther` AS `audit_exclaustraliaother`,`e`.`exclInExcess` AS `audit_exclinexcess`,`e`.`ExcludedDates` AS `audit_excludeddates`,`e`.`Exclusions` AS `audit_exclusions`,`e`.`FrequencyID` AS `audit_frequencyid`,`e`.`inclAccomAirlineSales` AS `audit_inclaccomairlinesales`,`e`.`inclEducationalSales` AS `audit_incleducationalsales`,`e`.`inclEventSales` AS `audit_incleventsales`,`e`.`inclMultiplePublicationSales` AS `audit_inclmultiplepublicationsales`,`e`.`InExcess` AS `audit_inexcess`,`e`.`Issues` AS `audit_issues`,`e`.`IssuesAudited` AS `audit_issuesaudited`,`e`.`NimsAvCopiesDelivered` AS `audit_nimaveragecopiesdelivered`,`e`.`NimsAvCopiesMailed` AS `audit_nimaveragecopiesmailed`,`e`.`NimsAvCopiesPrinted` AS `audit_nimaveragecopiesprinted`,`e`.`NIMCopiesDelivered` AS `audit_nimcopiesdelivered`,`e`.`NIMCopiesMailed` AS `audit_nimcopiesmailed`,`e`.`NIMCopiesPrinted` AS `audit_nimcopiesprinted`,`e`.`NonPublishingDates` AS `audit_nonpublishingdates`,`e`.`NZInExcess` AS `audit_nzinexcess`,`e`.`NewZealand` AS `audit_nznps`,`e`.`OCInExcess` AS `audit_ocinexcess`,`e`.`OSMoney` AS `audit_osmoney`,`e`.`OtherCountries` AS `audit_othercountries`,_utf8'' AS `audit_parentpublication`,`e`.`AuditPeriod` AS `audit_period`,`e`.`periodAdjustment` AS `audit_periodadjustment`,`e`.`periodAdjustmentValue` AS `audit_periodadjustmentvalue`,_utf8'' AS `audit_periodenddate`,_utf8'' AS `audit_periodstartdate`,`e`.`PubID` AS `audit_pubid`,`e`.`comments` AS `audit_remarks`,`e`.`SpecialPublishingDay` AS `audit_specialpublishingday`,`e`.`stageid` AS `audit_stageid`,`e`.`totalIssuesAudited` AS `audit_totalissuesaudited`,`e`.`lAuditType` AS `audit_type`,`e`.`iYear` AS `audit_year`,`e`.`AuditorEmail` AS `deprecated_AuditorEmail`,`e`.`AuditorName` AS `deprecated_AuditorName`,`e`.`dateAuditorApproved` AS `deprecated_dateAuditorApproved`,`e`.`dateSignatoryApproved` AS `deprecated_dateSignatoryApproved`,`e`.`dateSubmitted` AS `deprecated_dateSubmitted`,`e`.`DayPublished` AS `deprecated_dayspublished`,`e`.`FirstSignatoryEmail` AS `deprecated_FirstSignatoryEmail`,`e`.`FirstSignatoryName` AS `deprecated_FirstSignatoryName`,`e`.`FormStatus` AS `deprecated_FormStatus`,`e`.`lodgementstatus` AS `deprecated_lodgementstatus`,`e`.`lodgementtype` AS `deprecated_lodgementtype`,`e`.`nPeriod` AS `deprecated_nPeriod`,`e`.`nYear` AS `deprecated_nYear`,`e`.`s2complete` AS `deprecated_s2complete`,`e`.`s3complete` AS `deprecated_s3complete`,`e`.`SecondSignatoryEmail` AS `deprecated_SecondSignatoryEmail`,`e`.`SecondSignatoryName` AS `deprecated_SecondSignatoryName`
FROM (`auau7859_aba`.`el_abc_stage` `e` LEFT JOIN `auau7859_aba`.`el_abc_status` `s` ON((`s`.`stageid` = `e`.`stageid`)))
WHERE ((`s`.`statusid` = (SELECT `h`.`statusid` AS `statusid`
FROM `auau7859_aba`.`el_abc_status` `h` WHERE (`h`.`stageid` = `s`.`stageid`) ORDER BY `h`.`statusid` DESC
LIMIT 1)) AND (`s`.`currentstatus` = _latin1'complete'))
Ugly, right? Whenever I use code formatting in Netbeans, or in Heidi SQL all it does it put each section (FROM/WHERE/LIMIT/SELECT) on a new line. This is no good because my select statement is so long. I need something that will at the very least put each select field on a new line.
Can You help me?
Thank you.
Upvotes: 3
Views: 4408
Reputation: 958
Done with one click in a free online SQLFormatter
/* ALGORITHM=UNDEFINED */
SELECT
`e`.`Australia` AS `audit_anps`
,`e`.`audit_end_date` AS `audit_auditenddate`
,`e`.`BumperIssueDates` AS `audit_bumperissuedates`
,`e`.`BumperIssuesLinked` AS `audit_bumperissuelinked`
,`e`.`BumperIssues` AS `audit_bumperissues`
,`e`.`bundledSales` AS `audit_bundledsales`
,`e`.`CoverPrice` AS `audit_coverprice`
,`e`.`CoverPrice2` AS `audit_coverprice2`
,`e`.`CoverPrice2Day` AS `audit_coverprice2day`
,`e`.`CoverPrice3` AS `audit_coverprice3`
,`e`.`CoverPrice3Day` AS `audit_coverprice3day`
,`e`.`CoverPrice4` AS `audit_coverprice4`
,`e`.`CoverPrice4Day` AS `audit_coverprice4day`
,`e`.`CoverPriceDay` AS `audit_coverpriceday`
,`e`.`timestamp` AS `audit_datecreated`
,`e`.`weeksbetween` AS `audit_daysbetween`
, _utf8'' AS `audit_dayspublished`
,`e`.`AustraliaDigital` AS `audit_digital_anps`
,`e`.`BumperIssueDatesD` AS `audit_digital_bumperissuedates`
,`e`.`BumperIssuesD` AS `audit_digital_bumperissues`
,`e`.`BumperIssuesLinkedD` AS `audit_digital_bumperissueslinked`
,`e`.`bundledSalesDigital` AS `audit_digital_bundledsalesdigital`
,`e`.`CoverPriceD` AS `audit_digital_coverprice`
,`e`.`CoverPrice2D` AS `audit_digital_coverprice2`
,`e`.`CoverPrice2DayD` AS `audit_digital_coverprice2day`
,`e`.`CoverPrice3D` AS `audit_digital_coverprice3`
,`e`.`CoverPrice3DayD` AS `audit_digital_coverprice3day`
,`e`.`CoverPrice4D` AS `audit_digital_coverprice4`
,`e`.`CoverPrice4DayD` AS `audit_digital_coverprice4day`
,`e`.`CoverPriceDayD` AS `audit_digital_coverpriceday`
,`e`.`ExcludedDatesD` AS `audit_digital_excludeddates`
,`e`.`inclAccomAirlinesSalesDigital` AS `audit_digital_inclaccomairlinesales`
,`e`.`inclEducationalSalesDigital` AS `audit_digital_incleducationalsales`
,`e`.`inclEventSalesDigital` AS `audit_digital_incleventsales`
,`e`.`inclMultiplePublicationSalesDigital` AS `audit_digital_inclmultiplepublicationsales`
,`e`.`IssuesD` AS `audit_digital_issues`
,`e`.`IssuesAuditedD` AS `audit_digital_issuesaudited`
,`e`.`NonPublishingDatesD` AS `audit_digital_nonpublishingdates`
,`e`.`RemarksD` AS `audit_digital_remarks`
,`e`.`exclAustraliaOther` AS `audit_exclaustraliaother`
,`e`.`exclInExcess` AS `audit_exclinexcess`
,`e`.`ExcludedDates` AS `audit_excludeddates`
,`e`.`Exclusions` AS `audit_exclusions`
,`e`.`FrequencyID` AS `audit_frequencyid`
,`e`.`inclAccomAirlineSales` AS `audit_inclaccomairlinesales`
,`e`.`inclEducationalSales` AS `audit_incleducationalsales`
,`e`.`inclEventSales` AS `audit_incleventsales`
,`e`.`inclMultiplePublicationSales` AS `audit_inclmultiplepublicationsales`
,`e`.`InExcess` AS `audit_inexcess`
,`e`.`Issues` AS `audit_issues`
,`e`.`IssuesAudited` AS `audit_issuesaudited`
,`e`.`NimsAvCopiesDelivered` AS `audit_nimaveragecopiesdelivered`
,`e`.`NimsAvCopiesMailed` AS `audit_nimaveragecopiesmailed`
,`e`.`NimsAvCopiesPrinted` AS `audit_nimaveragecopiesprinted`
,`e`.`NIMCopiesDelivered` AS `audit_nimcopiesdelivered`
,`e`.`NIMCopiesMailed` AS `audit_nimcopiesmailed`
,`e`.`NIMCopiesPrinted` AS `audit_nimcopiesprinted`
,`e`.`NonPublishingDates` AS `audit_nonpublishingdates`
,`e`.`NZInExcess` AS `audit_nzinexcess`
,`e`.`NewZealand` AS `audit_nznps`
,`e`.`OCInExcess` AS `audit_ocinexcess`
,`e`.`OSMoney` AS `audit_osmoney`
,`e`.`OtherCountries` AS `audit_othercountries`
, _utf8'' AS `audit_parentpublication`
,`e`.`AuditPeriod` AS `audit_period`
,`e`.`periodAdjustment` AS `audit_periodadjustment`
,`e`.`periodAdjustmentValue` AS `audit_periodadjustmentvalue`
, _utf8'' AS `audit_periodenddate`
, _utf8'' AS `audit_periodstartdate`
,`e`.`PubID` AS `audit_pubid`
,`e`.`comments` AS `audit_remarks`
,`e`.`SpecialPublishingDay` AS `audit_specialpublishingday`
,`e`.`stageid` AS `audit_stageid`
,`e`.`totalIssuesAudited` AS `audit_totalissuesaudited`
,`e`.`lAuditType` AS `audit_type`
,`e`.`iYear` AS `audit_year`
,`e`.`AuditorEmail` AS `deprecated_AuditorEmail`
,`e`.`AuditorName` AS `deprecated_AuditorName`
,`e`.`dateAuditorApproved` AS `deprecated_dateAuditorApproved`
,`e`.`dateSignatoryApproved` AS `deprecated_dateSignatoryApproved`
,`e`.`dateSubmitted` AS `deprecated_dateSubmitted`
,`e`.`DayPublished` AS `deprecated_dayspublished`
,`e`.`FirstSignatoryEmail` AS `deprecated_FirstSignatoryEmail`
,`e`.`FirstSignatoryName` AS `deprecated_FirstSignatoryName`
,`e`.`FormStatus` AS `deprecated_FormStatus`
,`e`.`lodgementstatus` AS `deprecated_lodgementstatus`
,`e`.`lodgementtype` AS `deprecated_lodgementtype`
,`e`.`nPeriod` AS `deprecated_nPeriod`
,`e`.`nYear` AS `deprecated_nYear`
,`e`.`s2complete` AS `deprecated_s2complete`
,`e`.`s3complete` AS `deprecated_s3complete`
,`e`.`SecondSignatoryEmail` AS `deprecated_SecondSignatoryEmail`
,`e`.`SecondSignatoryName` AS `deprecated_SecondSignatoryName`
...
Upvotes: 0
Reputation: 1
Create a stored procedure to re create the view. That keeps your formatting and allows you to add comments to help document the view. I name mine as sp_create_viewname
Upvotes: 3
Reputation: 15358
I use squirrel SQL.
The application itself is a bit hard for me to use over heidi sql (which i swear by) but heidi's sql formatting functionality lack the superiority of squirrel
http://squirrel-sql.sourceforge.net/
It can format a view's horrible code perfectly and without a fuss.
Upvotes: 0