Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

BODS SQL Query for template table and permanent table

Hello experts,

Any clue, how do we find out permanent table and template tables, for a given repository or for a given project.

Thank and regards,

Abhishek Biwal

replied

Hello Severin,

This helps a lot, but gives zeros records.

We came up with query,             

SELECT SRC.* FROM (   

select
AL_USAGE.PARENT_OBJ,AL_USAGE.PARENT_OBJ_TYPE,AL_SCHEMA.OBJECT_KEY,

AL_SCHEMA.NAME,AL_SCHEMA.OWNER,AL_SCHEMA.DATASTORE
from <DS_DM_USER_NAME>.AL_SCHEMA

  left outer join <DS_DM_USER_NAME>.AL_USAGE

  on AL_SCHEMA.NAME=AL_USAGE.DESCEN_OBJ and
AL_SCHEMA.OWNER=AL_USAGE.DESCEN_OBJ_OWNER and
AL_SCHEMA.DATASTORE=AL_USAGE.DESCEN_OBJ_DS

  where AL_USAGE.PARENT_OBJ_TYPE = 'Job'

  AND AL_USAGE.DESCEN_OBJ_TYPE = 'Table'

  --AND AL_USAGE.DESCEN_OBJ_USAGE in ('Target','Source')

  and AL_USAGE.PARENT_OBJ_KEY in (select distinct JOB_ID from
<DS_DM_USER_NAME>.ALVW_PROJECT_JOB where PROJECT_ID=(select max(OBJECT_KEY) from
<DS_DM_USER_NAME>.AL_PROJECTS where NAME='PR_A_VENDOR_MASTER')))
SRC

  left outer join <DS_DM_USER_NAME>.AL_ATTR

  on SRC.OBJECT_KEY = AL_ATTR.PARENT_OBJID

  WHERE

AL_ATTR.ATTR_NAME = 'Loader_Is_Template_Table' AND
UPPER(AL_ATTR.ATTR_VALUE) = 'NO' AND AL_ATTR.PARENT_OBJ_TYPE = '7'

and not(SRC.NAME like 'A_%') and not(SRC.NAME like
'VMAP_%')
 

order by 1 desc;

You need to replace, DS_DM_USER_NAME with your local repo name, and PR_A_VENDOR_MASTER, with your porject name.

thanks to my teammate

thanks and regards

Abhishek Biwak

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question