0

We have a MySQL DB that is consolidated from two other MySQL DB's. This consolidation takes place every morning at about 5am.

When the consolidation task fails, I can see it easily enough with:

select * from progress_log where stoptime = curdate()

Which returns:

oid  |  starttime  |  stoptime  |  Comment
NULL     NULL           NULL         NULL

However i want to return the following when there is no curdate() to allow me to schedule an automated report that is emailed only when there is data:

stoptime  |
Sync failed

Trying the below appears to return a blank (not null) value.

select ifnull(stoptime, 'Sync Failed') as 'Bla' where stoptime = curdate(

What am i doing wrong? I have tried various methods (coalesce, case, if(stoptime is null...)).

Thanks in advance,

Mike

MrrMan
  • 158
  • 1
  • 13
  • You can't select a row where there is no row. You could do some overly complicated logic to conditionally select from `dual` (a built-in table with no columns, that always returns one row), but I'd think this is logic you'd want to do in whatever program you're using to generate the report, not in the query itself. – Josh Eller Jul 31 '19 at 15:01
  • See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) then we have a better feel for that you are trying.. But @JoshEller seams to be right this seams to be better handled by application code if anny. – Raymond Nijland Jul 31 '19 at 15:14
  • *"dual (a built-in table with no columns, that always returns one row)"* @JoshEller the `DUAL` [Reserved word](https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-D) is just a SQL preprocessor instruction included in the MySQL [lexer](https://github.com/mysql/mysql-server/blob/5.5/sql/lex.h#L185)/parser/optimizer it is not considerd to be a table. – Raymond Nijland Jul 31 '19 at 15:26
  • @RaymondNijland Sure, I'm just trying to explain it in a functional way that a beginner could understand – Josh Eller Jul 31 '19 at 15:27
  • *"Sure, I'm just trying to explain it in a functional way that a beginner could understand "* fair enough @JoshEller to make it more easy MySQL also supports `SELECT 1 + 1` for tableless selects the `FROM DUAL` is not required.. See [demo](https://www.db-fiddle.com/f/wD7YbEW5kchcafuN9afemp/0) MySQL handles them the same.. – Raymond Nijland Jul 31 '19 at 15:31
  • You say your SQL, which is **select * from progress_log where stoptime = curdate()** returns a row where column stoptime has a value of **NULL**. I am having a difficult time accepting that since a NULL stoptime value will not be equal to any value, not even another NULL value. – Booboo Jul 31 '19 at 16:30
  • @JoshEller Selecting from dual does *not* always select 1 row: **SELECT 1 from DUAL WHERE 0;** – Booboo Jul 31 '19 at 16:35
  • @joshEller - Thanks, Crystal Server publications can be configured to send notifications based on an automated report containing data. I have tried simply returning the rows and hiding using the report where it doesn't meet the criteria, but Crystal Server sees the report as containing data (albeit hidden data) and so fires off the report anyway. – MrrMan Aug 01 '19 at 08:00
  • @raymondnijland - Thanks for your input. I have been quoted that article before to be fair, but i figured a simple query like select ifnull(column_name, 'text') from table_name is self explanitory....perhaps not. – MrrMan Aug 01 '19 at 08:02
  • @ronaldaaronson - Poor choice of words, i meant it returns NULL. – MrrMan Aug 01 '19 at 08:03

0 Answers0