My requirement is to find the all the layers and their models where a specified component is present. for eg a component 'CustTable' is present in SYS layer Foundation model and also in ISV layer I4C model. Then query should return me the SYS layer and its model Foundation and ISV layer and its model I4C, according to my requirement there must be only 2 records is correct i.e 1st record |SYS | Foundation. 2nd record |ISV| I4C, please help me TIA
*This post is locked for comments
SELECT DISTINCT
ro.Name object,
ro.ElementType type,
l.Name Layer,
mm.Name Model
FROM Sources s
join ModelElement me on
me.ElementHandle = s.SourceHandle
join ElementTypes met
on met.ElementType = me.ElementType
join ModelElement ro
on ro.ElementHandle = me.RootHandle
left join ModelElementData med
on med.ElementHandle = s.SourceHandle
and med.LayerId = s.LayerId
and med.ElementHandle = me.ElementHandle
left join Layer l
on l.Id = s.LayerId
left join ModelManifest mm
on mm.ModelId = med.ModelId
where ro.Name = 'InventDim' order by mm.Name, ro.ElementType is the query giving results according to the requirement.
Sorry for the trouble caused Martin, thanks for ur help i found the query for my requirement Thanks again.
I can help you if you have a question, but I'm not going to do all work for you. I have more than enough of my own work.
Do what you can by yourself and ask a specific question if you run into a problem.
Can you please send the complete query martin i am not able to build right query.
can u please send the query for that or can i achieve the requirement making changes to the above query.
Sure, because your query doesn't do what you've just described. Mine does. Just join SysModel and SysModelLayer and you have everything you've asked for.
It seems that your query tries to deal with some object hierarchies, source code and name patterns, but I don't see anything like that among your requirements. You shouldn't jump to complex stuff unless you can handle simple stuff.
For example a component named 'CustTable' is there in SYS layer in Foundation model and also in ISV layer in I4C model. then the CustTable is present in two locations. i need those two locations table looks like the follows
Object | Layer | Model |
---------------------------------------|
CustTable | SYS | Foundation |
CustTable | ISV | I4C |
But using above query gives me around 150 records which includes InventDim used in all methods etc.. which i dont want.
What exactly do you mean by "where this component is used" and "where component is present"?
Can you confirm that my code returns the models you want? If not, what else do you expect?
Like this?
-- Looking up CustTable
SELECT *
FROM SYSMODELELEMENT me
JOIN SYSMODELELEMENTDATA med
ON me.RECID = med.RECID
JOIN SYSMODELMANIFEST mm
ON mm.RECID = med.LAYER
WHERE me.NAME = 'CustTable'
AND me.ELEMENTTYPE = '44'
I failed in writing the correct query please help me in building the right query for my requirement. Thanks in advance.
#1
André Arnaud de Cal...
293,202
Super User 2025 Season 1
#2
Martin Dráb
231,923
Most Valuable Professional
#3
nmaenpaa
101,156
Moderator
Share
Report
All responses (
Answers (
