Hello,
i have problems to add some paramters to a simple MySQL Statement.
The Statement looks like this:
SELECT
obj_main.isys_obj__title AS 'Service',
IFNULL(REPLACE(j5.isys_purpose__title,'LC__CMDB__CATG__PURPOSE_PRODUCTION','Produktion'),' ') AS 'Einsatzzweck',
IFNULL(j1.isys_sla_service_level__title,' ') AS 'Servicelevel',
concat(IFNULL(convert(j2.isys_catg_sla_list__reaction_time,char),' '),' ',(IFNULL(REPLACE(REPLACE(convert(j2.isys_catg_sla_list__reaction_time_unit,char),'3','Stunden'),'2','Minuten'),' '))) AS 'Reaktionszeit',
concat(IFNULL(convert(j2.isys_catg_sla_list__recovery_time,char),' '),' ',(IFNULL(REPLACE(REPLACE(convert(j2.isys_catg_sla_list__recovery_time_unit,char),'3','Stunden'),'2','Minuten'),' '))) AS 'Wiederherstellungszeit',
IFNULL(concat(TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__monday_time,'{"from":',''),'"to":',''),'}',''),',',1)),'%H:%i'),' - ',TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__monday_time,'{"from":',''),'"to":',''),'}',''),',',-1)),'%H:%i')),' ') AS 'Montag',
IFNULL(concat(TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__tuesday_time,'{"from":',''),'"to":',''),'}',''),',',1)),'%H:%i'),' - ',TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__tuesday_time,'{"from":',''),'"to":',''),'}',''),',',-1)),'%H:%i')),' ') AS 'Dienstag',
IFNULL(concat(TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__wednesday_time,'{"from":',''),'"to":',''),'}',''),',',1)),'%H:%i'),' - ',TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__wednesday_time,'{"from":',''),'"to":',''),'}',''),',',-1)),'%H:%i')),' ') AS 'Mittwoch',
IFNULL(concat(TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__thursday_time,'{"from":',''),'"to":',''),'}',''),',',1)),'%H:%i'),' - ',TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__thursday_time,'{"from":',''),'"to":',''),'}',''),',',-1)),'%H:%i')),' ') AS 'Donnerstag',
IFNULL(concat(TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__friday_time,'{"from":',''),'"to":',''),'}',''),',',1)),'%H:%i'),' - ',TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__friday_time,'{"from":',''),'"to":',''),'}',''),',',-1)),'%H:%i')),' ') AS 'Freitag',
IFNULL(concat(TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__saturday_time,'{"from":',''),'"to":',''),'}',''),',',1)),'%H:%i'),' - ',TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__saturday_time,'{"from":',''),'"to":',''),'}',''),',',-1)),'%H:%i')),' ') AS 'Samstag',
IFNULL(concat(TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__sunday_time,'{"from":',''),'"to":',''),'}',''),',',1)),'%H:%i'),' - ',TIME_format(SEC_TO_TIME(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(j2.isys_catg_sla_list__sunday_time,'{"from":',''),'"to":',''),'}',''),',',-1)),'%H:%i')),' ') AS 'Sonntag',
IFNULL(j2.isys_catg_sla_list__description,' ') AS 'Beschreibung'
FROM isys_obj AS obj_main
INNER JOIN isys_cmdb_status AS obj_main_status ON obj_main_status.isys_cmdb_status__id = obj_main.isys_obj__isys_cmdb_status__id
LEFT JOIN isys_catg_sla_list AS j2 ON j2.isys_catg_sla_list__isys_obj__id = obj_main.isys_obj__id
LEFT JOIN isys_sla_service_level AS j1 ON j1.isys_sla_service_level__id = j2.isys_catg_sla_list__isys_sla_service_level__id
LEFT JOIN isys_catg_global_list AS j6 ON j6.isys_catg_global_list__isys_obj__id = obj_main.isys_obj__id
LEFT JOIN isys_purpose AS j5 ON j5.isys_purpose__id = j6.isys_catg_global_list__isys_purpose__id
LEFT JOIN isys_obj_type AS j9 ON j9.isys_obj_type__id = obj_main.isys_obj__isys_obj_type__id
AND ( (j9.isys_obj_type__id = '45' )) AND ( (obj_main.isys_obj__title = :Service ));
As you can see, the statement reture a list of services. I need on my confluence page a textbox to edit the parameter "Service" and when it's blank it must return all services.
Please can you help me.
Thank you very much,
Stephan
