AnsweredAssumed Answered

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

Question asked by d.vanlooij on Apr 12, 2012

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

Outcomes