Simple MySQL Statement with Parameter

[Stephan Mallmann]

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

[Felix Grund (Scandio) · Accepted solution]

Hi Stephan! Does your query work in general or does it now work at all? So is your requirement only this?

I need on my confluence page a textbox to edit the parameter "Service" and when it's blank it must return all services.

[Stephan Mallmann]

Hello and thank you for the response,

i fix the issue by myself. When i set the "start" value of the parameter to % then it shows all entries.

Thank you very much!

Stephan