Hello All,

I am running the below query in order to list the documents created after 23/09/2015, whose the name starts with ‘javalu’ and attached/associated with the ASPECTS aspect_1 or aspect_2:

NOTE : r_aspect_name is repeating attribute.

SELECT doc.r_object_id, doc.r_aspect_name
FROM dm_document doc
LEFT OUTER JOIN dm_acl mAcl ON mAcl.object_name = doc.acl_name
WHERE LOWER(doc.object_name) like 'javalu%'
AND (any doc.r_aspect_name = 'aspect_1' or any doc.r_aspect_name = 'aspect_2')
and doc.r_creation_date > date('2015-09-23 00:00:00','yyyy-mm-dd hh:mi:ss');

The query looked very simple but an ‘unexpected’ error occurrs while executing it.
The error could be:
+ [DM_QUERY_E_REPEAT_REG]error: “You have specified repeating attribute(s) and columns from a registered table in the same select list.”
+ [DM_QUERY2_E_REPEAT_TYPE_JOIN]error: “Your query is selecting repeating attributes and joining types

Is there any alternative ?
How to get repeating attribute result in single row when joining of object type and registered table in spite of the errors DM_QUERY_E_REPEAT_REG or/and DM_QUERY2_E_REPEAT_TYPE_JOIN?


SOLUTION 1 : Use of DQL HINT “ENABLE (ROW_BASED)”
Th adding of this HINT to the end of the query, gets multiple rows depending of repeating column values but it’s possible to limit the damage by adding distinct to the query.

SELECT doc.r_object_id, doc.r_aspect_name
FROM dm_document doc
LEFT OUTER JOIN dm_acl mAcl ON mAcl.object_name = doc.acl_name
WHERE LOWER(doc.object_name) like 'javalu%'
AND (any doc.r_aspect_name = 'aspect_1' or any doc.r_aspect_name = 'aspect_2')
and doc.r_creation_date > date('2015-09-23 00:00:00','yyyy-mm-dd hh:mi:ss')
ENABLE (ROW_BASED);



SOLUTION 2 : Use of UNION

SELECT doc.r_object_id, 'aspect_1' as document_type
FROM dm_document doc
LEFT OUTER JOIN dm_acl mAcl ON mAcl.object_name = doc.acl_name
WHERE LOWER(doc.object_name) like 'javalu%'
AND any doc.r_aspect_name = 'aspect_1'
and doc.r_creation_date > date('2015-09-23 00:00:00','yyyy-mm-dd hh:mi:ss')

UNION

SELECT doc.r_object_id, 'aspect_2' as document_type
FROM dm_document doc
LEFT OUTER JOIN dm_acl mAcl ON mAcl.object_name = doc.acl_name
WHERE LOWER(doc.object_name) like 'javalu%'
AND any doc.r_aspect_name = 'aspect_2'
and doc.r_creation_date > date('2015-09-23 00:00:00','yyyy-mm-dd hh:mi:ss')
;


SOLUTION 3 : Use of multiple LEFT OUTER JOIN with Inner SELECT clauses

SELECT doc.r_object_id, docaspect1.aspectname as docaspect1_aspectname, docaspect2.aspectname as docaspect2_aspectname
FROM dm_document doc
LEFT OUTER JOIN dm_acl mAcl ON mAcl.object_name = doc.acl_name
LEFT OUTER JOIN (select r_object_id, 'aspect_1' as aspectname from dm_document where any r_aspect_name = 'aspect_1') docaspect1 on doc.r_object_id = docaspect1.r_object_id
LEFT OUTER JOIN (select r_object_id, 'aspect_2' as aspectname from dm_document where any r_aspect_name = 'aspect_2') docaspect2 on doc.r_object_id = docaspect2.r_object_id
WHERE LOWER(doc.object_name) like 'javalu%'
AND (any doc.r_aspect_name = 'aspect_1' or any doc.r_aspect_name = 'aspect_2')
and doc.r_creation_date > date('2015-09-23 00:00:00','yyyy-mm-dd hh:mi:ss')
;

Best regards,

Huseyin OZVEREN