3

I have got similar issue with my SQL query. Convert UNION selects to single select with loop in MySQL

I have "complicated" SELECT and I UNION ALL SELECTs as in example above.

My question is that maybe it can be done in LOOP?

  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 30 THEN

      SELECT //my select
      UNION ALL

      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;

Could you please tell me if it will gonna work in such way? I try to write calendar with periodic events, and I would like to iterate date by date for example 7 days and collect results from SELECTs

Thank you and Regards.

elpasso
  • 99
  • 6
  • *" I try to write calendar with periodic events, and I would like to iterate date by date for example 7 days and collect results from SELECTs"* research calendar tables. – Raymond Nijland Jul 18 '19 at 15:29
  • More or less like [Generating a series of dates](https://stackoverflow.com/questions/14105018/generating-a-series-of-dates) – Raymond Nijland Jul 18 '19 at 15:32
  • You'll have to insert the results of each iteration's SELECT into a table of some to collect the results together (rather than actually using UNION ALL), and then SELECT from that table after the loop. Doing this in a stored procedure without the aforementioned inserting would just yield multiple result sets. – Uueerdo Jul 18 '19 at 16:29
  • Hi I have created DAY table only with ID field, I have filled this table from 1 to 366. Now when I want to 7 days iteration I am doing this: SET [@]date := '2014-06-26'; SELECT *, [@]date := DATE_ADD([@]date, INTERVAL T.id DAY ) AS it_date FROM ( MY SELECT how to use [@]date variable? it is always set to '2014-06-26', but in results I can see it is incremented for example when I write SELECT EV.*, [@]date I have got the same date '2014-06-26' not incremented but 'it_date' field is incremented ) AS T WHERE T.id <= 7 //For 7 days how can use variable in sub select? – elpasso Jul 19 '19 at 12:17

0 Answers0