JOIN could be better

[cheick66]

Hey,

I just did my first test with a JOIN. At the end you see the "ON", which could look better with an "\n" infront. < snip > JOIN (SELECT hour(TIMESTAMP) AS HOUR, round(avg(value), 2) AS Home_Consumtion_P FROM history WHERE TIMESTAMP > §timestamp_begin§ AND TIMESTAMP < DATE_ADD(§timestamp_begin§,INTERVAL 1 DAY) AND DEVICE = @generator AND READING = @Home_Consumtion_P AND VALUE > 10 GROUP BY 1) X3 ON X1.HOUR = X2.HOUR AND X1.HOUR = X3.HOUR) X;

You did a graet job! Christian

[andialbrecht]

@cheick66 Could you give us a more detailed example? What is the actual result and what the expected?

[cheick66]

Hi Andi, it's quite simple :-)

That's going in:

SELECT * FROM (SELECT h.TIMESTAMP, h.READING, IF (h.READING LIKE '%Rate%' OR h.READING LIKE '%Autarky%', h.VALUE, cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-31'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING) x1 USING(TIMESTAMP,READING)) x2 UNION ALL SELECT h.TIMESTAMP, CONCAT('WB0',h.READING) AS READING, h.VALUE FROM history h JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE DEVICE = 'WB0' AND READING LIKE 'lp%_kWhCounter_Year' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING) x2 USING(TIMESTAMP,READING) UNION ALL SELECT h.TIMESTAMP, CONCAT('WB1',h.READING) AS READING, h.VALUE FROM history h JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE DEVICE = 'WB1' AND READING LIKE 'lp%_kWhCounter_Year' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING) x2 USING(TIMESTAMP,READING) ;

That's what's coming out of your tool:

SELECT FROM (SELECT h.TIMESTAMP, h.READING, IF (h.READING LIKE '%Rate%' OR h.READING LIKE '%Autarky%', h.VALUE, cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-31'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING)x1USING(TIMESTAMP,READING))**x2 UNION ALL SELECT h.TIMESTAMP, CONCAT('WB0', h.READING) AS READING, h.VALUE FROM history h JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE DEVICE = 'WB0' AND READING LIKE 'lp%_kWhCounter_Year' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING)x2USING(TIMESTAMP,READING) UNION ALL SELECT h.TIMESTAMP, CONCAT('WB1', h.READING) AS READING, h.VALUE FROM history h JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE DEVICE = 'WB1' AND READING LIKE 'lp%_kWhCounter_Year' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING)**x2**USING(TIMESTAMP,READING)* ;

Only have a look to the bold parts:

SELECT FROM (SELECT h.TIMESTAMP, h.READING, IF (h.READING LIKE '%Rate%' OR h.READING LIKE '%Autarky%', h.VALUE, cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-31'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING)*x1 *USING(TIMESTAMP,READING) )**x2 UNION ALL SELECT h.TIMESTAMP, CONCAT('WB0', h.READING) AS READING, h.VALUE FROM history h JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE DEVICE = 'WB0' AND READING LIKE 'lp%_kWhCounter_Year' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING)**x2 USING(TIMESTAMP,READING) UNION ALL SELECT h.TIMESTAMP, CONCAT('WB1', h.READING) AS READING, h.VALUE FROM history h JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE DEVICE = 'WB1' AND READING LIKE 'lp%_kWhCounter_Year' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING )**x2USING(TIMESTAMP,READING)* ;

=========================================================================================== In advance you may think about this:

< snip >

UNION ALL SELECT h.TIMESTAMP, CONCAT('WB0', h.READING) AS READING, h.VALUE FROM history h JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE DEVICE = 'WB0' AND READING LIKE 'lp%_kWhCounter_Year' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING*)**x2 USING(TIMESTAMP,READING) UNION ALL SELECT h.TIMESTAMP, CONCAT('WB1', h.READING) AS READING, h.VALUE FROM history h JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE DEVICE = 'WB1' AND READING LIKE 'lp%_kWhCounter_Year' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING )**x2USING(TIMESTAMP,READING)* ;

This shifting of "UNION" and "JOIN" by 2 blanks will simplify to follow the code.

Just my5cent from Germany :-)

Best regards    Christian

Am 30.12.2022 um 12:17 schrieb Andi Albrecht:

@cheick66 https://github.com/cheick66 Could you give us a more detailed example? What is the actual result and what the expected?

— Reply to this email directly, view it on GitHub https://github.com/andialbrecht/sqlparse/issues/695#issuecomment-1367865636, or unsubscribe https://github.com/notifications/unsubscribe-auth/AX3FAZWRWM3V6GDEU57UY2DWP3AEVANCNFSM6AAAAAAS2G4IGM. You are receiving this because you were mentioned.Message ID: @.***>

[andialbrecht]

ah, so it's more a problem with USING than with ON? However, the behavior is the same. Maybe adding a newline before the ON/USING could be made a new formatter flag...

[cheick66]

It's for the ON and the table end ") name" too.Have good party and a Happy New YearChristian Am 31.12.2022 16:00 schrieb Andi Albrecht @.***>: ah, so it's more a problem with USING than with ON? However, the behavior is the same. Maybe adding a newline before the ON/USING could be made a new formatter flag...

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: @.***>