ATG BCC queries

The following section specifies the different queries to fetch information.

1)Query return asset info(like category ) using input the asset id (like category ) and project id

select * from dcs_category where category_id='cat20070' and  WORKSPACE_ID =(select id from avm_devline where name =(select workspace from epub_project where project_id = 'prj350096'));

2)Query return project details using the input workspace id 

select * from epub_project where WORKSPACE = (select name from avm_devline where id ='64352');

3)fetching the asstets using projectname from particular table or custom table

select * from xx_product_dyn_attribute where WORKSPACE_ID in ((select id from avm_devline where name in (select WORKSPACE from epub_project where DISPLAY_NAME ='projectName')));



4) TO verify the bcc dployment log using project id

select * from epub_dep_log where dep_id in (select deployment_id from epub_deploy_proj where project_id ='2345')



5) fetching workspace id using projectId

select workspace_id from epub_prj_targt_ws where project_id ='12345';


6) fetching targetId using projectId

select target_id from epub_prj_targt_ws where project_id ='12345';

7)fetching status of project at specific target using projectId
select  status_code from epub_pr_tg_status where project_id='12345'


8) fetching  the creation time for project's snapshot using project id
select snapsht_creat_tm from epub_pr_tg_st_ts  where project_id='proj1234' and target_id='targ1234';

9)fetching the workspace status using projectName

select * from avm_workspace where ws_id in (select id from avm_devline where name in (select WORKSPACE from epub_project where DISPLAY_NAME ='projectName'));

10)fetching assets repositoryId and repositoryNames which are locked or not using projectName

select * from avm_asset_lock where workspace_id in (select id from avm_devline where name in (select WORKSPACE from epub_project where DISPLAY_NAME ='projectName'));

11)To identify the duplicate record in multi / intermediate table

select category_id, asset_version, sequence_num, count (*), max(child_prd_id), min(child_prd_id)
from pub.dcs_cat_chldprd
group by category_id,asset_version, SEQUENCE_NUM
having count(*) > 1 and (max(child_prd_id) != min(child_prd_id));