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

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share