/build/static/layout/Breadcrumb_cap_w.png

K1000 Smart Label SQL Question

Hi all, 

I'm working on some smart labels with some custom SQL statements and have run into an issue. 

I have one LDAP label that collects all the computers from a single department. I want to take that LDAP label and create two smart labels that would dynamically split the list in half (eg. Group 1 and Group 2). 

I did some testing and research into an SQL statement that would allow me to take the total number of systems in a label and cut it in half for Group 1. I have the SQL statement below that I tested in mySQLWorkbench

set @r:= SELECT ROUND(count(*) * 50/100) FROM MACHINE WHERE ((( exists  (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID  AND LABEL.TYPE <> 'hidden' and LABEL.NAME rlike 'Accounting Computers')) ));
prepare halfQuery from '
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE ((( exists  (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID  AND LABEL.TYPE <> "hidden" and LABEL.NAME rlike "Accounting Computers")) )) ORDER BY MACHINE.NAME LIMIT 0,?' ;
execute halfQuery using @r;


The code works in mySQL Workbench and it displays half of the total number of rows. But when I copy/paste this code into the SQL option for the smart label, it fails after the first statement. 


Any tips on how I could make this work? 


0 Comments   [ + ] Show comments

Answers (0)

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ