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)
Please log in to answer
Be the first to answer this question
