DQL to retrieve only single valued custom attributes

Hi all,

I can query all custom attributes of my object type 'test_type' including repeating attributes with following DQL query.

SELECT attr_name FROM dmi_dd_attr_info WHERE type_name = 'test_type' AND attr_name NOT IN (SELECT attr_name FROM dmi_dd_attr_info WHERE type_name = 'dm_document')

However ,I want only retrieve only all single valued custom attributes of "test_type".

As first step i tried the following query,But its returning all attributes.

SELECT attr_name,attr_repeating FROM dm_type where name='test_type' and any attr_repeating='0'

Has any one have idea on this............

Thanks,

karthick s

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

CristianOrellana

Hello,

Try this:

select attr_name, attr_type
from dm_type
where name = 'YOUR_CUSTOM_TYPE' and attr_repeating = 0 and (i_position*-1)-1 >= start_pos
enable (row_based)

Regards.

All comments

Chris_Campbell

This is one of those cases where if you know how the database schema is organized, know what kind of SQL server you're running, this actually becomes pretty simple to do through DQL. As you may or may not know, object types are database views to two tables in the format dm_customtype_s or _r. To just get the single-values from your custom type (or any type for that matter) just add a _s to the table name. If you wanted repeating values, append _r.

The tricky part is knowing what SQL server you have running. I happen to have Oracle. Knowing that, I can use a standard SQL command to query the columns names that is compatible with DQL.

select column_name from sys.user_tab_columns 
where table_name = 'MY_CUSTOMTYPE_S'

Make sure that you use the owner name (sys) in front of user_table_columns. If you use SQL server, the format would look similar to this:

SELECT * FROM INFORMATION_SCHEMA.Columns 
where TABLE_NAME = 'MY_CUSTOMTYPE_S'

The resulting DQL result set will return a list of custom attribute names for that type. (Note: This query will not return any parent or shared attributes.)

karthick

Hi ,

Thanks for quick response.....I am using oracle.

well,when i query the table directly i am getting single valued custom attribute along with r_object_id and i_partition.I need to get only single valued custom attributes.