/build/static/layout/Breadcrumb_cap_w.png
08/01/2017 751 views
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

  • This content is currently hidden from public view.
    Reason: Removed by member request For more information, visit our FAQ's.

There are no answers at this time