Typical disclaimer: I *really* need to stress that everything below is based on my own research and findings - the information below does not indicate official documentation or support, however some of this may be accurate.

Hopefully these K1000 SQL Reports are self-explanatory (they list the SQL Queries or LDAP Filters for their respective targets), but the intention is two-fold:

1) To provide a backup. Rather than having to save these as individual files (or K1000 Resources), they can all be exported in concise reports.

2) To provide a one-stop reference when looking to scavenge code from existing SQL queries and LDAP filters. This can be particularly useful when looking to build JOIN statements or determining how a Wizard-built filter has been constructed.

Another use for the K1000 Queries (Reports) report is for accessing report queries when the Report Wizard has been used to build a report, and subtopics have been used. In this situation, the Edit SQL button will not be available on the Edit Report screen (which precludes accessing the query using the K1000 UI). Using the K1000 Queries (Reports) report will allow you to access the query (and the corresponding JOIN statements that are created when using subtopics).

As usual, I hope these help! ^_^

John

 

K1000 Queries (Reports)

Lists SQL query code for all Reports in the K1000 database.

SELECT S.TITLE, S.CATEGORY, S.QUERY
FROM SMARTY_REPORT S
ORDER BY S.CATEGORY, S.TITLE

 

K1000 Queries (Smart Labels)

Lists SQL query code for all Smart Labels in the K1000 database.

SELECT L.NAME AS LABEL,
(CASE WHEN C.NAME IS NULL THEN '!No Label Group!'
ELSE C.NAME END) AS LABEL_GROUP,
L.NOTES,
F.QUERY
FROM LABEL L
LEFT JOIN LABEL_LABEL_JT LJT ON LJT.CHILD_LABEL_ID = L.ID
LEFT JOIN LABEL C ON C.ID = LJT.LABEL_ID
JOIN FILTER F ON F.LABEL_ID = L.ID
ORDER BY LABEL_GROUP, L.NAME

 

K1000 Queries (LDAP Labels)

Lists LDAP filters for all LDAP labels in the K1000 database.

SELECT L.NAME AS LABEL,
(CASE WHEN C.NAME IS NULL THEN '!No Label Group!'
ELSE C.NAME END) AS LABEL_GROUP,
L.NOTES,
LD.SEARCH_FILTER
FROM LABEL L
LEFT JOIN LABEL_LABEL_JT LJT ON LJT.CHILD_LABEL_ID = L.ID
LEFT JOIN LABEL C ON C.ID = LJT.LABEL_ID
JOIN LDAP_FILTER LD ON LD.LABEL_ID = L.ID
ORDER BY LABEL_GROUP, L.NAME

 

K1000 Queries (Email Alerts)

Lists SQL query code for all Email Alerts in the K1000 database.

SELECT N.TITLE, N.QUERY
FROM NOTIFICATION N
ORDER BY N.TITLE