1
0
-1

i have query like this

SELECT
    t1.noreg,
    t1.first_track_date as mak_first_date,
    t2.end_track_date as mak_end_date,
    IFNULL(
        CONCAT(
            LPAD(
                CASE
                    WHEN DATE(STR_TO_DATE(t1.first_track_date, '%m/%d/%Y %H:%i')) = DATE(STR_TO_DATE(t2.end_track_date, '%m/%d/%Y %H:%i'))
                    THEN 0
                    ELSE workdays_between(
                        STR_TO_DATE(t1.first_track_date, '%m/%d/%Y %H:%i'),
                        STR_TO_DATE(t2.end_track_date, '%m/%d/%Y %H:%i')
                    )
                END, 2, '0'
            ), 'D ',
            LPAD(MOD(
                TIMESTAMPDIFF(HOUR, 
                    STR_TO_DATE(t1.first_track_date, '%m/%d/%Y %H:%i'),
                    STR_TO_DATE(t2.end_track_date, '%m/%d/%Y %H:%i')
                ), 24
            ), 2, '0'), 'H ',
            LPAD(MOD(
                TIMESTAMPDIFF(MINUTE, 
                    STR_TO_DATE(t1.first_track_date, '%m/%d/%Y %H:%i'),
                    STR_TO_DATE(t2.end_track_date, '%m/%d/%Y %H:%i')
                ), 60
            ), 2, '0'), 'M'
        ), '00D 00H 00M'
    ) AS duration
FROM
    (SELECT noreg, MIN(track_date) AS first_track_date
     FROM workflow_track_history
     WHERE track_code = '2.0'
     GROUP BY noreg) t1
LEFT JOIN
    (SELECT noreg, MAX(track_date) AS end_track_date
     FROM workflow_track_history
     WHERE track_code = '2.2' OR track_code = '2.3'
     GROUP BY noreg) t2
ON t1.noreg = t2.noreg;

and is working on mysql

but when i try put on datalist 

so is possible to add datalist from subquery on datalist ?


if possible how to do it ?

    CommentAdd your comment...

    1 answer

    1.  
      2
      1
      0

      just did a quick test. seems to work for me. latest Joget version on mysql.

      oh, ensure you don't have a semicolon ( ; ) at the end of the query

      1. galvin

        thanks is working when i remove ';'

      CommentAdd your comment...