Query That Is Too Complex For Format Query?

[Oliver Jones]

Hello, I have a class of queries, generated by Crystal Reports, that seem to be too complex for Sqlyog ultimate 8.53 to format correctly.

This is unfortunate, because I'm trying to use sqlyog to do some analysis on how to simplify them.

Can you look into this? Here's one of the ugly queries. Thank you.

Code:

SELECT `REPORT_REFERRAL_VIEW`.`PROVIDER_NAME`, `REPORT_VISIT_VIEW`.`ADMISSION_DATE`, `REPORT_VISIT_VIEW_prev`.`DISCHARGE_DATE`, `REPORT_VISIT_VIEW`.`DISCHARGE_ID`, `REPORT_VISIT_VIEW`.`DISCHARGE_DATE`, `REPORT_VISIT_VIEW`.`PATIENT_LAST_NAME`, `REPORT_VISIT_VIEW`.`ACCOUNT`, `REPORT_VISIT_VIEW`.`MRN`, `REPORT_VISIT_VIEW`.`LENGTH_OF_STAY`, `REPORT_VISIT_VIEW`.`DIAGNOSIS1_DESCRIPTION`, `REPORT_VISIT_VIEW_prev`.`ACCOUNT`, `REPORT_VISIT_VIEW_prev`.`ADMISSION_DATE`, `REPORT_VISIT_VIEW_prev`.`LENGTH_OF_STAY`, `REPORT_VISIT_VIEW_prev`.`DIAGNOSIS1_DESCRIPTION`, `REPORT_VISIT_VIEW_prev`.`DISCHARGE_ID`, `REPORT_VISIT_VIEW`.`FACILITY_ID`, `REPORT_VISIT_VIEW`.`FACILITY_NAME`, `REPORT_VISIT_VIEW`.`REF_PATIENT_CLASS_CODE`, `REPORT_VISIT_VIEW_prev`.`REF_PATIENT_CLASS_CODE`, `REPORT_VISIT_VIEW_prev`.`ATTENDING_DOCTOR_LAST_NAME`, `REPORT_REFERRAL_VIew_prev`.`BOOKING_REQUEST_ID`, `REPORT_VISIT_VIEW`.`UNIT`, `REPORT_REFERRAL_VIEW`.`FINAL_HOSPITAL_STATUS_DESC`, `REPORT_REFERRAL_VIew_prev`.`PROVIDER_NAME`, `REPORT_REFERRAL_VIew_prev`.`FINAL_HOSPITAL_STATUS_DESC`, `REPORT_VISIT_VIEW_prev`.`FACILITY_ID`
FROM ((`CLININTEL`.`REPORT_VISIT_VIEW` `REPORT_VISIT_VIEW` LEFT OUTER JOIN `CLININTEL`.`REPORT_REFERRAL_VIEW` `REPORT_REFERRAL_VIEW` ON `REPORT_VISIT_VIEW`.`DISCHARGE_ID`=`REPORT_REFERRAL_VIEW`.`DISCHARGE_ID`) LEFT OUTER JOIN `CLININTEL`.`REPORT_VISIT_VIEW` `REPORT_VISIT_VIEW_prev` ON `REPORT_VISIT_VIEW`.`MRN`=`REPORT_VISIT_VIEW_prev`.`MRN`) LEFT OUTER JOIN `CLININTEL`.`REPORT_REFERRAL_VIEW` `REPORT_REFERRAL_VIew_prev` ON `REPORT_VISIT_VIEW_prev`.`DISCHARGE_ID`=`REPORT_REFERRAL_VIew_prev`.`DISCHARGE_ID`
WHERE `REPORT_VISIT_VIEW_prev`.`REF_PATIENT_CLASS_CODE` LIKE 'I%' AND `REPORT_VISIT_VIEW`.`REF_PATIENT_CLASS_CODE` LIKE 'I%' AND `REPORT_VISIT_VIEW`.`FACILITY_ID`=289 AND `REPORT_VISIT_VIEW_prev`.`FACILITY_ID`=289 AND `REPORT_VISIT_VIEW_prev`.`DISCHARGE_ID`<>`REPORT_VISIT_VIEW`.`DISCHARGE_ID` AND (`REPORT_VISIT_VIEW`.`DISCHARGE_DATE`>={ts '2010-05-01 00:00:00'} AND `REPORT_VISIT_VIEW`.`DISCHARGE_DATE`<{ts '2010-05-02 00:00:00'}) AND `REPORT_VISIT_VIEW_prev`.`DISCHARGE_DATE`<{ts '2010-05-02 00:00:00'} AND NOT (`REPORT_VISIT_VIEW`.`UNIT`='E1' OR `REPORT_VISIT_VIEW`.`UNIT`='REH' OR `REPORT_VISIT_VIEW`.`UNIT`='SKL')

[Khushboo]

Hi Oliver,

We will look into this issue with high priority and will update you once it gets fixed.

Thank You.

Regards,

Khushboo

[Khushboo]

Hi Oliver,

We have fixed this issue. You can download the special (not release) binary from here.

Please try this special build in your environment and let us know the status.

Also FYI, We have scheduled all other formatter issues for our upcoming releases (post 8.5).

Thank You.

Regards,

Khushboo

[Oliver Jones]

Hello Kushboo. Thanks for the quick turnaround. The situation is much improved.

'Khushboo' wrote on '06:

Please try this special build in your environment and let us know the status.

However, with your version 8.54 n 1 and the nasty query I gave as an example, I still have a problem when sqlyog attempts to format the list of columns.

The first line of the formatted query begins with SELECT, and then contains a long list of column names and other expressions that stretches from Chicago to New York (or, Mumbai to Delhi) 🙂

The subsequent lines, beginning with WHERE, are formatted correctly.

By the way, precisely the same issue shows up when I sql-dump or use your alter-view command on a big view definition;

I look forward to the release where formatting works really well; it will help a lot in my work.

[Khushboo]

Hi Oliver,

We have released SQLyog 8.54 with this fix, which is available publicly!

“Query Formatter was not handling queries of size greater than 2 KB. Now the size is increased to 8 KB.”

Please refer to:

http://www.webyog.com/blog/2010/07/16/sqlyog-mysql-gui-8-54-released/

Thank you for reporting the issue and testing our special build.

Regards,

Khushboo

[Oliver Jones]

'Khushboo' wrote on '16:

Hi Oliver,

We have released SQLyog 8.54 with this fix, which is available publicly!

“Query Formatter was not handling queries of size greater than 2 KB. Now the size is increased to 8 KB.”

This now works fine; thank you. But you might want to increase your limit even more.

[DAE51D]

I have this query that won't format in SQLYog Ultimate 8.5.5

SELECT operator.operator_name, registration.registration_name, archive.id_archive, archive.pipe_name, archive.file_size, FROM_UNIXTIME(load_event.start_time, '%Y-%m-%d') AS data_load_date, load_event.result_code, IF(load_event.result_code = 0, 'PASS', 'FAIL') AS result, load_event.result_string FROM agis_core.archive JOIN load_event ON archive.id_load_event = load_event.id_load_event AND archive.id_operator = 276 AND load_event.result_code <> 0 AND load_event.result_string = '%fdmt%' AND load_event.start_time BETWEEN 1270598400 AND 1281139199 AND archive.pipe_name IN('uploader', 'sita', 'arinc', 'cellmodem') JOIN operator ON archive.id_operator = operator.id_operator JOIN registration ON archive.id_aircraft = registration.id_aircraft ORDER BY data_load_date DESC LIMIT 1000;

[Khushboo]

Hi,

Issue confirmed.

There is a problem with IN operator. Replacing

Quote:

AND archive.pipe_name IN('uploader', 'sita', 'arinc', 'cellmodem')

with

Quote:

AND archive.pipe_name IN('uploader')

works fine.

I have added this issue in our issue tracker:

http://code.google.com/p/sqlyog/issues/detail?id=1174

We will update once we take up this issue to solve.

Thanks & Regards,

Khushboo

[DAE51D]

here's another one that doesn't format…

Code:

select
sla_flight.id_operator,
sla_flight.id_aircraft,
sla_flight.id_sla_flight,
ifnull(dim_seat.id_dim_seat, 1) as id_dim_seat,
case when dim_seat.id_dim_seat is null then 1 when dim_seat.id_dim_seat = 1 then 1 when dim_seat.id_dim_seat <= 105 then 3 else 4 end as id_dim_seat_class,
fmr.id_fmr,
sla_flight_has_fmr.deferred
from sla_flight_has_fmr
join sla_flight on sla_flight.id_sla_flight = sla_flight_has_fmr.id_sla_flight
join fmr on fmr.id_fmr = sla_flight_has_fmr.id_fmr
left join fmr_has_seat on fmr_has_seat.id_fmr = fmr.id_fmr
left join dim_seat on dim_seat.id_dim_seat = fmr_has_seat.id_dim_seat
where (fmr.id_sla_function is not null and fmr.seat_count is not null)

This is kind of ridiculous that something as simple as formatting a string of text is such a challenge for SQLyog Ultimate 8.63…

[Khushboo]

Hi,

Please try formatting with SQLyog 8.7 Beta as we have currently rewritten the Formatter module. Let us know if you still face any issue.

Refer to:

http://www.webyog.com/blog/2010/10/11/sqlyog-mysql-gui-8-7-beta-1-released/

The Query Formatter is now completely rewritten. In principle all types of SQL statements are now supported and all limitations with the old implementation are lifted.

Thank you for your patience and support.

Regards,

Khushboo

[Author]

Posts