Query to get all IBA names for an object type

[aachanta]

Hi All,

Can anyone tell me the SQL Query to get all the IBA names of an object type ?

Say for WTPart or for its subtypes if we wish to get all the IBA's of that object type how will we get it ? Can anyone tell me that SQL query so that I will run once in SQL Developer and see the same.

Thanks in advance.

Best Regards,

Aditya Achanta

[RandyJones]

Aditya Achanta wrote:

Hi All,

Can anyone tell me the SQL Query to get all the IBA names of an object type ?

Say for WTPart or for its subtypes if we wish to get all the IBA's of that object type how will we get it ? Can anyone tell me that SQL query so that I will run once in SQL Developer and see the same.

Thanks in advance.

Best Regards,

Aditya Achanta

If you are referring to custom attributes I don't have an exact query however I have a link to a previous thread that will get you started:

How can I programatically determine the database column(s) associated with a custom standard attribute?

[MikeLockwood]

Randy, on the referenced link, is this line specific to your system?

"and tdef.ida2a2 = 144505"

[RandyJones]

Mike Lockwood wrote:

Randy, on the referenced link, is this line specific to your system?

"and tdef.ida2a2 = 144505"

No. That is an example (actually from David...). The '144505' would come by looking at the lwctypedefinition table to get the appropriate ida2a2.

[aachanta]

Hi All,

Thanks for the input.

In fact it was useful to me.

But if this is the case like for a WTPart I had created a subtype called as "DemoPart" and for that DemoPart I had created an attribute or an IBA called as "DemoAttribute".

How do i retrieve the details of that IBA using an SQL Query in SQLDeveloper.

Can you please let me know about it once ?

Thanks and Regards,

Aditya Achanta

[RandyJones · Accepted solution]

Aditya Achanta wrote:

Hi All,

Thanks for the input.

In fact it was useful to me.

But if this is the case like for a WTPart I had created a subtype called as "DemoPart" and for that DemoPart I had created an attribute or an IBA called as "DemoAttribute".

How do i retrieve the details of that IBA using an SQL Query in SQLDeveloper.

Can you please let me know about it once ?

Thanks and Regards,

Aditya Achanta

Given the following conditions

• DemoAttribute is a standard attribute (vs a global attribute)
  • standard attributes are stored in the wtpart table in columns created when you run the addColumns utility
• WTPart number = "12345"

Then you would do the following

1. find the column name per the referenced thread ie "ptc_str_1typeinfowtpart"

Query would then be something like this:

select
     wtm.wtpartnumber,
     wtm.name,
     wt.versionida2versioninfo || '.' || wt.iterationida2iterationinfo Version,
     wt.ptc_str_1typeinfowtpart DemoAttribute
from
     wtpartmaster wtm,
     wtpart wt
where
     wtm.wtpartnumber = '12345'
     and wt.ida3masterreference = wtm.ida2a2
     and wt.statecheckoutinfo = 'c/i'
order by
     wt.versionsortida2versioninfo,
     wt.iterationida2iterationinfo

[satre-2]

Try with below query. if your attribute is of string type

select sd.DISPLAYNAME, VALUE from STRINGVALUE sv, STRINGDEFINITION sd                
where sv.IDA3A6=sd.IDA2A2 and ida3a4= WTPartIDA2A2

[AbhishekJaiswal]

SQL is 

select wtmas.WTPartNumber, wt.versionIdA2versionInfo, wt.iterationIdA2iterationInfo, wt.statestate, sd.displayName, sv.value, sv.value2 
from WTPartMaster wtmas
	join WTPart wt on wt.idA3masterReference = wtmas.idA2A2
	join StringValue sv on sv.idA3A4= wt.idA2A2
	join StringDefinition sd on sv.idA3A6 = sd.idA2A2
where sd.displayName = 'AttributeName'
order by wtmas.WTPartNumber, wt.versionIdA2versionInfo, wt.iterationIdA2iterationInfo

select wtmas.WTPartNumber, wt.versionIdA2versionInfo, wt.iterationIdA2iterationInfo, wt.statestate, sd.displayName, sv.value, sv.value2

from WTPartMaster wtmas

join WTPart wt on wt.idA3masterReference = wtmas.idA2A2

join StringValue sv on sv.idA3A4= wt.idA2A2 join StringDefinition sd on sv.idA3A6 = sd.idA2A2

where sd.displayName = 'AttributeName'

order by wtmas.WTPartNumber, wt.versionIdA2versionInfo, wt.iterationIdA2iterationInfo