Highlighted
Honored Contributor.
Honored Contributor.
622 views

SQL Query across projects

I'm trying to build a query that will give me every GR_GROUP_NAME from every project.

I have a query that will give me every Template and project NOT built from a Template as those should be the only ones with possible different groups.

 

SELECT DB_NAME + '.td.GROUPS' as UniqueDatabases FROM qcsiteadmin_db.td.projects p1
where PR_IS_ACTIVE = 'Y'
and (
(IS_TEMPLATE = 'N' and PROJECT_TYPE = 'Standard' and ISNULL((select IS_TEMPLATE from qcsiteadmin_db.td.projects where DOMAIN_NAME = p1.CREATE_FROM_DOMAIN and PROJECT_NAME = p1.CREATE_FROM_PROJECT), 'N') = 'N')
or (IS_TEMPLATE = 'Y')

 

 

That gives me the database names, but I'm trying to figure out how to use it to pull the groups from all of the projects in a single query.  Something like this ??? but of course this doesn't work...  maybe Union, but How will I union when the db names are in the select statement

SELECT GR_GROUP_NAME FROM (SELECT DB_NAME + '.td.GROUPS' as UniqueDatabases FROM qcsiteadmin_db.td.projects p1
where PR_IS_ACTIVE = 'Y'
and (
(IS_TEMPLATE = 'N' and PROJECT_TYPE = 'Standard' and ISNULL((select IS_TEMPLATE from qcsiteadmin_db.td.projects where DOMAIN_NAME = p1.CREATE_FROM_DOMAIN and PROJECT_NAME = p1.CREATE_FROM_PROJECT), 'N') = 'N')
or (IS_TEMPLATE = 'Y')
)
)

 

Thanks for any help you can provide.

Mike

Tags (1)
0 Likes
1 Reply
Highlighted
Honored Contributor.
Honored Contributor.

Hi Mike,

Hi,

Not very sure about the SQL query but we can achieve this in another way .

Extrac the database names without appending the string .td.groups with your below query 

SELECT DB_NAME as UniqueDatabases FROM qcsiteadmin_db.td.projects p1
where PR_IS_ACTIVE = 'Y'
and (
(IS_TEMPLATE = 'N' and PROJECT_TYPE = 'Standard' and ISNULL((select IS_TEMPLATE from qcsiteadmin_db.td.projects where DOMAIN_NAME = p1.CREATE_FROM_DOMAIN and PROJECT_NAME = p1.CREATE_FROM_PROJECT), 'N') = 'N')
or (IS_TEMPLATE = 'Y')

Copy the database names and place in column "A" of an excel workbook starting from Cell A1

In column 'B' at cell B1,  place the below formula as it is

=CONCATENATE("select ","'",A1,"', gr_group_name from ",A1,".groups union all")

Extend the formula in column B to all values in column A .

copy all the values in column B and paste in notepad.

Remove the string "Union all" from the end and now you are ready with the query for extracting groups in all databases.

This output of the query is in two columns, with first column as database name and the second column is group name

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.