cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Query to get all IBA names for an object type

aachanta
13-Aquamarine

Query to get all IBA names for an object type

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

7 REPLIES 7

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 attribu...

mlockwood
19-Tanzanite
(To:RandyJones)

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

"and tdef.ida2a2 = 144505"

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
13-Aquamarine
(To: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

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

View solution in original post

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

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

Announcements