KB Article 16060
2019/03/26

Close
X
Contact Tech Support

 Email Tech Support
 (250) 655-1766
 [7:30 - 5:00 PST]
Your Meeting ID will be Provided by a Rep.
Tech Support
Tech Support Home
Software Updates
Knowledge Base Search
Recent KB Articles
Product FAQs
Code Samples
Documentation
Tech Blog
System Requirements
Installation Instructions
Support Testimonials
Contact us to...
Create a Ticket
Request a Free Demo
Suggest a New Feature
Submit Feedback
Upload a Large File
Other Services
Software Training
Professional Services

How to use SQL to get a list of objects in a repository that can be filtered by application area, library name, object name and/or object type.

Product: X-Analysis Type: How to

You can use the following SQL query to get a filtered list of objects in a repository (replacing MYREPONAME with the name of the repository).

SELECT DISTINCT XOBJECT.L#LIB, XOBJECT.L#OBJ, XOBJECT.L#TYPE,
  XOBJECT.L#ATTR, XOBJECT.L#TEXT, SYSTABLES.TABLE_TYPE
FROM MYREPONAME.XOBJECT
LEFT OUTER JOIN SYSTABLES
  ON  SYSTABLES.SYSTEM_TABLE_NAME = XOBJECT.L#OBJ
  AND SYSTABLES.TABLE_SCHEMA = XOBJECT.L#LIB
INNER JOIN MYREPONAME.XAALIST
  ON  XAALIST.A#OBJ = XOBJECT.L#OBJ
  AND XAALIST.A#TYP = XOBJECT.L#TYPE
  AND XAALIST.A#LIB = XOBJECT.L#LIB
WHERE XAALIST.A#AREA IN ('DEMO')           -- application area(s)
  AND XOBJECT.L#LIB NOT IN ('XAN4C13102')  -- library name(s)
  AND TRIM(XOBJECT.L#OBJ) LIKE 'C%'        -- object name
  AND XOBJECT.L#TYPE = '*FILE'             -- object type
ORDER BY XOBJECT.L#LIB ASC, XOBJECT.L#OBJ ASC, XOBJECT.L#TYPE ASC

This example would return all the objects in the application area "DEMO", not in the library "XAN4C13102", with an object name starting with "C", of type "*FILE".

Rate This Article

Did this example help you to achieve your goal?
 Yes  No  Don't Know

Enter additional comments below.   If you want to hear back from us, include your contact information.

Email Address:
Comments:
Please enable JavaScript in order to rate this page.