/build/static/layout/Breadcrumb_cap_w.png

Best Practices Question


SQL smart label doesn't apply to any machine

06/06/2017 1124 views
I have a smart label to find machines (DELL) which were bought on 2014. I created a report with the same sql code and it works fine, the problem is on smart labels, it doesn't apply to any machine. What i'm doing wrong?

The sql is:

SELECT M.NAME, D.SHIP_DATE FROM MACHINE M JOIN DELL_ASSET D ON D.SERVICE_TAG = M.BIOS_SERIAL_NUMBER WHERE ((M.NAME like 'LAB%') AND (D.SHIP_DATE like '%2014%'))



Answer Summary:
Two things to do: 1st- Must include "MACHINE.ID as TOPIC_ID" on the select clause. 2nd- Must _NOT_ use table abreviations. So, the final SQL is: SELECT MACHINE.NAME, MACHINE.ID as TOPIC_ID FROM MACHINE JOIN DELL_ASSET ON DELL_ASSET.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER WHERE ((MACHINE.NAME like 'LAB%') AND (DELL_ASSET.SHIP_DATE like '%2014%'))
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
Most of my smart labels include MACHINE.ID as TOPIC_ID in the select clause, that might be a requirement for them to work, but I'm not positive.
Answered 06/07/2017 by: chucksteel
Red Belt

  • Worked! Also chaged the abreviation for the tables for it to work. Thanks!
    • Could you share how the final SQL looks like?

Don't be a Stranger!

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

Sign up! or login

Share