Simple MySQL Statement with Parameter

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

2 answers

1 accepted

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.

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

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published 5 hours ago in Confluence

Three common content challenges + how to manage them

An efficient enterprise content management system, or ECM, is a must-have for companies that create work online (cough   cough, all companies). If content calendars, marketing plans, and bu...

18 views 0 3
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you