0 Replies Latest reply on Apr 12, 2012 1:16 AM by d.vanlooij

    problems with SQL macro running in FM pro advanced / FMS advanced

    d.vanlooij

      Title

      problems with SQL macro running in FM pro advanced / FMS advanced

      Post

      I have a problem with an SQL-macro that I want to run within a FM-script.

      some info:

      the script:

      -delete all records in table

      -import SQL data based on SQL-macro:

      SELECT 'Laatste dienst' AS Category, Datum, Dienst,
             Dept, Lijn, Werkplek, SUM(Realisatie), SUM(Target_100procent)
      FROM mse
      WHERE Datum = IF(TIME(CURTIME()) >= TIME('7:00') AND TIME(CURTIME()) <= TIME('14:59'),
                       DATE(CURDATE()) - INTERVAL 1 DAY,
                       DATE(CURDATE()))
        AND Dienst LIKE IF(TIME(CURTIME()) >= TIME('7:00') AND TIME(CURTIME()) <= TIME('14:59'), 'nacht',
                           IF(TIME(CURTIME()) >= TIME('15:00') AND TIME(CURTIME()) <= TIME('22:59'), 'dag',
                              'avond'))
      GROUP BY Dept, Lijn, Werkplek

      UNION

      SELECT 'Actuele dienst' AS Category, Datum, Dienst,
             Dept, Lijn, Werkplek, SUM(Realisatie), SUM(Target_100procent)
      FROM mse
      WHERE Datum = IF(TIME(CURTIME()) <= TIME('6:59'),
                       DATE(CURDATE()) - INTERVAL 1 DAY,
                       DATE(CURDATE()))
        AND Dienst LIKE IF(TIME(CURTIME()) >= TIME('7:00') AND TIME(CURTIME()) <= TIME('14:59'), 'dag',
                           IF(TIME(CURTIME()) >= TIME('15:00') AND TIME(CURTIME()) <= TIME('22:59'), 'avond',
                              'nacht'))
        AND Tijdstip <= TIME(CURTIME())
      GROUP BY Dept, Lijn, Werkplek

      UNION

      SELECT 'Laatste dag' AS Category, Datum, NULL AS Dienst,
             Dept, Lijn, Werkplek, SUM(Realisatie), SUM(Target_100procent)
      FROM mse
      WHERE Datum = CURDATE() - INTERVAL 1 DAY # last day
      GROUP BY Dept, Lijn, Werkplek

      UNION

      SELECT 'Laatste week' AS Category, Datum, NULL AS Dienst,
             Dept, Lijn, Werkplek, SUM(Realisatie), SUM(Target_100procent)
      FROM mse
      WHERE YEARWEEK(Datum, 3) = YEARWEEK(CURDATE() - INTERVAL 7 DAY, 3) # last week
      GROUP BY Dept, Lijn, Werkplek;

       

      So now the problem:

      If I run this macro in the SQL database I get a table of 260 records.

      If I run the same SQL macro from FM pro I get  a table of 130 records.
      This the first and second part of the SQLmacro ( select 'Laatste Dienst' and select 'Actuele dienst) and the first record of the second part of the SQLmacro (select 'Laatste Dag').

      the rest of the SQL macro isn't imported.
      Even if I delete the first to parts of the SQL-macro I get only 1 record, instead of the 60 or so records.

      I tested all the parts of the SQLmacro in FM pro but only the first two parts are imported.

      It looks like some kind of problem with FM pro and the commands in the SQLmacro it  needs to execute.

      Does anybody have an idea? I'm out of idea's.

      thanks