nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Query with ContentInstanceDBQuery...

Sanjeev_Kumar

Hi all,

Is it possible to have a where clause on CI related attribute with ContentInstanceDBQuery?

Thanks,
Sanjeev

Find more posts tagged with

Comments

David_Stiles

Are you asking about using an attribute from a referenced CI or asub-relation? You can directly filter on any attribute of the selected CIs, butnot on attributes of referenced CIs. ContentInstanceWhereClause is what you usefor this. In order to filter on attributes of referenced CIs you first need toselect the referenced CIs and then use their identity information to filter thereferring CIs in a subsequent query.

From: eLink Entry:Discussion Group - Web Experience Management[mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 6:23 AM
To: eLink Recipient
Subject: Query with ContentInstanceDBQuery...

Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 07:18

Hi all,

Is it possible to have a where clause on CI related attribute with ContentInstanceDBQuery?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]
Forum:	Discussion Group - Web Experience Management
Livelink Server:	knowledge-wlweb01

Sanjeev_Kumar

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi David,

Thanks for your reply. As of now, I am getting that attribute as below:-

AttributedObject aos[] = ci.getRelations(TRANSLATED_RELATION);
for (int j = 0; j < aos.length; j++) {
AttributedObject ao = aos[j];
ao.getAttributeValue(TRANSLATED_LANGUAGE);
}

It is not a referenced CI.

Thanks,
Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 05 May 2011 14:20
To: eLink Recipient
Subject: RE Query with ContentInstanceDBQuery...

RE Query with ContentInstanceDBQuery... Posted by dstiles (Stiles, David) On 2011/05/05 09:19

Are you asking about using an attribute from a referenced CI or a sub-relation? You can directly filter on any attribute of the selected CIs, but not on attributes of referenced CIs. ContentInstanceWhereClause is what you use for this. In order to filter on attributes of referenced CIs you first need to select the referenced CIs and then use their identity information to filter the referring CIs in a subsequent query. From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 6:23 AM
To: eLink Recipient
Subject: Query with ContentInstanceDBQuery... Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 07:18

Hi all,

Is it possible to have a where clause on CI related attribute with ContentInstanceDBQuery?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic: Query with ContentInstanceDBQuery...
Forum: Discussion Group - Web Experience Management
Livelink Server: knowledge-wlweb01

David_Stiles

Then you can just use ContentInstanceWhereClause.checkAttribute()for this. Let me know if you need a code example.

From: eLink Entry:Discussion Group - Web Experience Management[mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 8:43 AM
To: eLink Recipient
Subject: RE RE Query with ContentInstanceDBQuery...

RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 09:42

Hi David,

Thanks for your reply. As of now, I am getting that attribute as below:-

AttributedObject aos[] = ci.getRelations(TRANSLATED_RELATION);
for (int j = 0; j < aos.length; j++) {
AttributedObject ao = aos[j];
ao.getAttributeValue(TRANSLATED_LANGUAGE);
}

It is not a referenced CI.

Thanks,
Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 05 May 2011 14:20
To: eLink Recipient
Subject: RE Query with ContentInstanceDBQuery...

RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/05 09:19

Are you asking about using an attribute from a referenced CI or a sub-relation? You can directly filter on any attribute of the selected CIs, but not on attributes of referenced CIs. ContentInstanceWhereClause is what you use for this. In order to filter on attributes of referenced CIs you first need to select the referenced CIs and then use their identity information to filter the referring CIs in a subsequent query.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 6:23 AM
To: eLink Recipient
Subject: Query with ContentInstanceDBQuery...

Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 07:18

Hi all,

Is it possible to have a where clause on CI related attribute with ContentInstanceDBQuery?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic:	Query with ContentInstanceDBQuery...
Forum:	Discussion Group - Web Experience Management
Livelink Server:	knowledge-wlweb01

Sanjeev_Kumar

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

It would be great to have an example.

Many thanks,
Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 05 May 2011 14:54
To: eLink Recipient
Subject: RE RE RE Query with ContentInstanceDBQuery...

RE RE RE Query with ContentInstanceDBQuery... Posted by dstiles (Stiles, David) On 2011/05/05 09:53

Then you can just use ContentInstanceWhereClause.checkAttribute() for this. Let me know if you need a code example. From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 8:43 AM
To: eLink Recipient
Subject: RE RE Query with ContentInstanceDBQuery... RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 09:42

Hi David,

Thanks for your reply. As of now, I am getting that attribute as below:-

AttributedObject aos[] = ci.getRelations(TRANSLATED_RELATION);
for (int j = 0; j < aos.length; j++) {
AttributedObject ao = aos[j];
ao.getAttributeValue(TRANSLATED_LANGUAGE);
}

It is not a referenced CI.

Thanks,
Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 05 May 2011 14:20
To: eLink Recipient
Subject: RE Query with ContentInstanceDBQuery...

RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/05 09:19

Are you asking about using an attribute from a referenced CI or a sub-relation? You can directly filter on any attribute of the selected CIs, but not on attributes of referenced CIs. ContentInstanceWhereClause is what you use for this. In order to filter on attributes of referenced CIs you first need to select the referenced CIs and then use their identity information to filter the referring CIs in a subsequent query.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 6:23 AM
To: eLink Recipient
Subject: Query with ContentInstanceDBQuery...

Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 07:18

Hi all,

Is it possible to have a where clause on CI related attribute with ContentInstanceDBQuery?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic: Query with ContentInstanceDBQuery...
Forum: Discussion Group - Web Experience Management
Livelink Server: knowledge-wlweb01

David_Stiles

Take a look at this. I was just making up something for yourattribute, so change that accordingly, of course. I was also assuming theattribute is a String attribute, but if not then there are other *QueryOpclasses (and conditions) that can be used, including DateQueryOp, NumberQueryOpand ObjectQueryOp.

ContentType ct = <your CT>;

ContentInstanceWhereClause wc = newContentInstanceWhereClause();

wc.checkAttribute(TRANSLATED_LANGUAGE,StringQueryOp.EQUAL, "Spanish");

ContentInstanceDBQuery q = newContentInstanceDBQuery(new ContentTypeRef(ct));

q.setWhereClause(wc);

RequestParameters rp = new RequestParameters();

// the RequestParameters can be configured inwhatever way makes sense for the use case

IPagingList pl = QueryManager.execute(q, rp);

// now call whatever IPagingList methods areneeded to access results of the query

From: eLink Entry:Discussion Group - Web Experience Management[mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 9:06 AM
To: eLink Recipient
Subject: RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 10:05

It would be great to have an example.

Many thanks,
Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 05 May 2011 14:54
To: eLink Recipient
Subject: RE RE RE Query with ContentInstanceDBQuery...

RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/05 09:53

Then you can just use ContentInstanceWhereClause.checkAttribute() for this. Let me know if you need a code example.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 8:43 AM
To: eLink Recipient
Subject: RE RE Query with ContentInstanceDBQuery...

RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 09:42

Hi David,

Thanks for your reply. As of now, I am getting that attribute as below:-

AttributedObject aos[] = ci.getRelations(TRANSLATED_RELATION);
for (int j = 0; j < aos.length; j++) {
AttributedObject ao = aos[j];
ao.getAttributeValue(TRANSLATED_LANGUAGE);
}

It is not a referenced CI.

Thanks,
Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 05 May 2011 14:20
To: eLink Recipient
Subject: RE Query with ContentInstanceDBQuery...

RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/05 09:19

Are you asking about using an attribute from a referenced CI or a sub-relation? You can directly filter on any attribute of the selected CIs, but not on attributes of referenced CIs. ContentInstanceWhereClause is what you use for this. In order to filter on attributes of referenced CIs you first need to select the referenced CIs and then use their identity information to filter the referring CIs in a subsequent query.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 6:23 AM
To: eLink Recipient
Subject: Query with ContentInstanceDBQuery...

Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 07:18

Hi all,

Is it possible to have a where clause on CI related attribute with ContentInstanceDBQuery?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic:	Query with ContentInstanceDBQuery...
Forum:	Discussion Group - Web Experience Management
Livelink Server:	knowledge-wlweb01

Sanjeev_Kumar

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi David,

Many thanks! It is working perfectly

Regards,

Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 05 May 2011 16:03
To: eLink Recipient
Subject: RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE Query with ContentInstanceDBQuery... Posted by dstiles (Stiles, David) On 2011/05/05 11:01

Take a look at this. I was just making up something for your attribute, so change that accordingly, of course. I was also assuming the attribute is a String attribute, but if not then there are other *QueryOp classes (and conditions) that can be used, including DateQueryOp, NumberQueryOp and ObjectQueryOp. ContentType ct = <your CT>; ContentInstanceWhereClause wc = new ContentInstanceWhereClause(); wc.checkAttribute(TRANSLATED_LANGUAGE, StringQueryOp.EQUAL, "Spanish"); ContentInstanceDBQuery q = new ContentInstanceDBQuery(new ContentTypeRef(ct)); q.setWhereClause(wc); RequestParameters rp = new RequestParameters(); // the RequestParameters can be configured in whatever way makes sense for the use case IPagingList pl = QueryManager.execute(q, rp); // now call whatever IPagingList methods are needed to access results of the query From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 9:06 AM
To: eLink Recipient
Subject: RE RE RE RE Query with ContentInstanceDBQuery... RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 10:05

It would be great to have an example.

Many thanks,
Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 05 May 2011 14:54
To: eLink Recipient
Subject: RE RE RE Query with ContentInstanceDBQuery...

RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/05 09:53

Then you can just use ContentInstanceWhereClause.checkAttribute() for this. Let me know if you need a code example.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 8:43 AM
To: eLink Recipient
Subject: RE RE Query with ContentInstanceDBQuery...

RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 09:42

Hi David,

Thanks for your reply. As of now, I am getting that attribute as below:-

AttributedObject aos[] = ci.getRelations(TRANSLATED_RELATION);
for (int j = 0; j < aos.length; j++) {
AttributedObject ao = aos[j];
ao.getAttributeValue(TRANSLATED_LANGUAGE);
}

It is not a referenced CI.

Thanks,
Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 05 May 2011 14:20
To: eLink Recipient
Subject: RE Query with ContentInstanceDBQuery...

RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/05 09:19

Are you asking about using an attribute from a referenced CI or a sub-relation? You can directly filter on any attribute of the selected CIs, but not on attributes of referenced CIs. ContentInstanceWhereClause is what you use for this. In order to filter on attributes of referenced CIs you first need to select the referenced CIs and then use their identity information to filter the referring CIs in a subsequent query.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Thursday, May 05, 2011 6:23 AM
To: eLink Recipient
Subject: Query with ContentInstanceDBQuery...

Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/05 07:18

Hi all,

Is it possible to have a where clause on CI related attribute with ContentInstanceDBQuery?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic: Query with ContentInstanceDBQuery...
Forum: Discussion Group - Web Experience Management
Livelink Server: knowledge-wlweb01

Sanjeev_Kumar

Hi David,

I am seeing an strange issue with 'whereclause'.

ContentInstanceDBQuery query = new ContentInstanceDBQuery(new ContentTypeRef(ct));
//Craeting objects of ContentInstanceWhereClause
ContentInstanceWhereClause main = new ContentInstanceWhereClause();
ContentInstanceWhereClause langWC = new ContentInstanceWhereClause();
ContentInstanceWhereClause transLangWC = new ContentInstanceWhereClause();
//Check for language
langWC.checkAttribute(LANGUAGE, StringQueryOp.EQUAL, "ja");
//Check for translated language
transLangWC.checkAttribute(TRANSLATED_LANGUAGE, StringQueryOp.EQUAL, "ja");
//add to main where clause
main.addWhereClause(langWC);
main.addWhereClause(transLangWC);
main.setMatchAny(true);

Now,
query.setWhereClause(main) is giving 33 results
query.setWhereClause(langWC) is giving 346 results
query.setWhereClause(transLangWC) is giving 30 results

Am I doing something wrong here?

Thanks,
Sanjeev

David_Stiles

First, I’m assuming that “query.setWhereClause(!transLangWC)” is a typo and you really just mean “query.setWhereClause(transLangWC)”.

But I can’t explain the results you’re seeing. One thing you cando though is set your VCM’s log level to debug and check the generated SQL thatgets logged to see if it is as you expect for each of the three cases.

From: eLink Entry:Discussion Group - Web Experience Management[mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Wednesday, May 18, 2011 3:42 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/18 04:36

Hi David,

I am seeing an strange issue with 'whereclause'.

Now,
query.setWhereClause(main) is giving 33 results
query.setWhereClause(langWC) is giving 346 results
query.setWhereClause(! transLangWC) is giving 30 results

Am I doing something wrong here?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]
Topic:	Query with ContentInstanceDBQuery...
Forum:	Discussion Group - Web Experience Management
Livelink Server:	knowledge-wlweb01

Sanjeev_Kumar

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi David,

Yes, it was a typo. Below are the SQL queries being executed respectively:-

select count(*) from ctd_news t, ctd_news_trans t1 where (t.LOCALE='ja' or t1.LOCALE='ja') and (t.id=t1.parent_id)
select count(*) from ctd_news where locale='ja'
select COUNT(*) from ctd_news t1, ctd_news_trans t2 where (t2.locale='ja') and (t1.id= t2.parent_id)

Any input/suggestion to achieve this in other way?

Thanks,

Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 18 May 2011 17:39
To: eLink Recipient
Subject: RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE Query with ContentInstanceDBQuery... Posted by dstiles (Stiles, David) On 2011/05/18 12:38

First, I’m assuming that “query.setWhereClause(! transLangWC)” is a typo and you really just mean “query.setWhereClause(transLangWC)”. But I can’t explain the results you’re seeing. One thing you can do though is set your VCM’s log level to debug and check the generated SQL that gets logged to see if it is as you expect for each of the three cases. From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Wednesday, May 18, 2011 3:42 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE Query with ContentInstanceDBQuery... RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/18 04:36

Hi David,

I am seeing an strange issue with 'whereclause'.

ContentInstanceDBQuery query = new ContentInstanceDBQuery(new ContentTypeRef(ct));
//Craeting objects of ContentInstanceWhereClause
ContentInstanceWhereClause main = new ContentInstanceWhereClause();
ContentInstanceWhereClause langWC = new ContentInstanceWhereClause();
ContentInstanceWhereClause transLangWC = new ContentInstanceWhereClause();
//Check for language
langWC.checkAttribute(LANGUAGE, StringQueryOp.EQUAL, "ja");
//Check for translated language
transLangWC.checkAttribute(TRANSLATED_LANGUAGE, StringQueryOp.EQUAL, "ja");
//add to main where clause
main.addWhereClause(langWC);
main.addWhereClause(transLangWC);
main.setMatchAny(true);

Now,
query.setWhereClause(main) is giving 33 results
query.setWhereClause(langWC) is giving 346 results
query.setWhereClause(! transLangWC) is giving 30 results

Am I doing something wrong here?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic: Query with ContentInstanceDBQuery...
Forum: Discussion Group - Web Experience Management
Livelink Server: knowledge-wlweb01

David_Stiles

Well, it still isn’t entirely clear from this. What version VCMare you running on? Also, can you post the exact SQL taken from the log foreach of these cases?

From: eLink Entry:Discussion Group - Web Experience Management[mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Friday, May 20, 2011 9:17 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/20 10:16

Hi David,

Yes, it was a typo. Below are the SQL queries being executed respectively:-

Any input/suggestion to achieve this in other way?

Thanks,

Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 18 May 2011 17:39
To: eLink Recipient
Subject: RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/18 12:38

First, I’m assuming that “query.setWhereClause(! transLangWC)” is a typo and you really just mean “query.setWhereClause(transLangWC)”.

But I can’t explain the results you’re seeing. One thing you can do though is set your VCM’s log level to debug and check the generated SQL that gets logged to see if it is as you expect for each of the three cases.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Wednesday, May 18, 2011 3:42 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/18 04:36

Hi David,

I am seeing an strange issue with 'whereclause'.

ContentInstanceDBQuery query = new ContentInstanceDBQuery(new ContentTypeRef(ct));
//Craeting objects of ContentInstanceWhereClause
ContentInstanceWhereClause main = new ContentInstanceWhereClause();
ContentInstanceWhereClause langWC = new ContentInstanceWhereClause();
ContentInstanceWhereClause transLangWC = new ContentInstanceWhereClause();
//Check for language
langWC.checkAttribute(LANGUAGE, StringQueryOp.EQUAL, "ja");
//Check for translated language
transLangWC.checkAttribute(TRANSLATED_LANGUAGE, StringQueryOp.EQUAL, "ja");
//add to main where clause
main.addWhereClause(langWC);
main.addWhereClause(transLangWC);
main.setMatchAny(true);

Now,
query.setWhereClause(main) is giving 33 results
query.setWhereClause(langWC) is giving 346 results
query.setWhereClause(! transLangWC) is giving 30 results

Am I doing something wrong here?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic:	Query with ContentInstanceDBQuery...
Forum:	Discussion Group - Web Experience Management
Livelink Server:	knowledge-wlweb01

Sanjeev_Kumar

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi David,

I am on VCM 7.5. Exact SQLs are below:-

select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId = ? and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a1.LOCALE = ?) or (a3.LOCALE = ?)))], PARAM[ 1=69b94bd629642110VgnVCM100000426013ac____ 2=ja 3=ja]

select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1,VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a3.LOCALE ='ja')))

select count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a0.recordId = a2.contentMgmtId and ((a1.LOCALE ='ja'))) Thanks,

Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 20 May 2011 18:11
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery... Posted by dstiles (Stiles, David) On 2011/05/20 13:10

Well, it still isn’t entirely clear from this. What version VCM are you running on? Also, can you post the exact SQL taken from the log for each of these cases? From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Friday, May 20, 2011 9:17 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery... RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/20 10:16

Hi David,

Yes, it was a typo. Below are the SQL queries being executed respectively:-

select count(*) from ctd_news t, ctd_news_trans t1 where (t.LOCALE='ja' or t1.LOCALE='ja') and (t.id=t1.parent_id)
select count(*) from ctd_news where locale='ja'
select COUNT(*) from ctd_news t1, ctd_news_trans t2 where (t2.locale='ja') and (t1.id= t2.parent_id)

Any input/suggestion to achieve this in other way?

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 18 May 2011 17:39
To: eLink Recipient
Subject: RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/18 12:38

First, I’m assuming that “query.setWhereClause(! transLangWC)” is a typo and you really just mean “query.setWhereClause(transLangWC)”.

But I can’t explain the results you’re seeing. One thing you can do though is set your VCM’s log level to debug and check the generated SQL that gets logged to see if it is as you expect for each of the three cases.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Wednesday, May 18, 2011 3:42 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/18 04:36

Hi David,

I am seeing an strange issue with 'whereclause'.

ContentInstanceDBQuery query = new ContentInstanceDBQuery(new ContentTypeRef(ct));
//Craeting objects of ContentInstanceWhereClause
ContentInstanceWhereClause main = new ContentInstanceWhereClause();
ContentInstanceWhereClause langWC = new ContentInstanceWhereClause();
ContentInstanceWhereClause transLangWC = new ContentInstanceWhereClause();
//Check for language
langWC.checkAttribute(LANGUAGE, StringQueryOp.EQUAL, "ja");
//Check for translated language
transLangWC.checkAttribute(TRANSLATED_LANGUAGE, StringQueryOp.EQUAL, "ja");
//add to main where clause
main.addWhereClause(langWC);
main.addWhereClause(transLangWC);
main.setMatchAny(true);

Now,
query.setWhereClause(main) is giving 33 results
query.setWhereClause(langWC) is giving 346 results
query.setWhereClause(! transLangWC) is giving 30 results

Am I doing something wrong here?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic: Query with ContentInstanceDBQuery...
Forum: Discussion Group - Web Experience Management
Livelink Server: knowledge-wlweb01

Sanjeev_Kumar

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi David,

'select count(*) from ctd_news where id in (select id from ctd_news where locale='ja' Union all select parent_id from ctd_news_trans where locale='ja')' is giving the intended result. Could you please guide us in implementing above with CIDBQuery?

Thanks,

Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 23 May 2011 10:52
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery... Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/23 05:51

Hi David, I am on VCM 7.5. Exact SQLs are below:- select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId = ? and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a1.LOCALE = ?) or (a3.LOCALE = ?)))], PARAM[ 1=69b94bd629642110VgnVCM100000426013ac____ 2=ja 3=ja] select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1,VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a3.LOCALE ='ja')))
select count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a0.recordId = a2.contentMgmtId and ((a1.LOCALE ='ja'))) Thanks, Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 20 May 2011 18:11
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery... Posted by dstiles (Stiles, David) On 2011/05/20 13:10

Well, it still isn’t entirely clear from this. What version VCM are you running on? Also, can you post the exact SQL taken from the log for each of these cases? From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Friday, May 20, 2011 9:17 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery... RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/20 10:16

Hi David,

Yes, it was a typo. Below are the SQL queries being executed respectively:-

select count(*) from ctd_news t, ctd_news_trans t1 where (t.LOCALE='ja' or t1.LOCALE='ja') and (t.id=t1.parent_id)
select count(*) from ctd_news where locale='ja'
select COUNT(*) from ctd_news t1, ctd_news_trans t2 where (t2.locale='ja') and (t1.id= t2.parent_id)

Any input/suggestion to achieve this in other way?

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 18 May 2011 17:39
To: eLink Recipient
Subject: RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/18 12:38

First, I’m assuming that “query.setWhereClause(! transLangWC)” is a typo and you really just mean “query.setWhereClause(transLangWC)”.

But I can’t explain the results you’re seeing. One thing you can do though is set your VCM’s log level to debug and check the generated SQL that gets logged to see if it is as you expect for each of the three cases.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Wednesday, May 18, 2011 3:42 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/18 04:36

Hi David,

I am seeing an strange issue with 'whereclause'.

ContentInstanceDBQuery query = new ContentInstanceDBQuery(new ContentTypeRef(ct));
//Craeting objects of ContentInstanceWhereClause
ContentInstanceWhereClause main = new ContentInstanceWhereClause();
ContentInstanceWhereClause langWC = new ContentInstanceWhereClause();
ContentInstanceWhereClause transLangWC = new ContentInstanceWhereClause();
//Check for language
langWC.checkAttribute(LANGUAGE, StringQueryOp.EQUAL, "ja");
//Check for translated language
transLangWC.checkAttribute(TRANSLATED_LANGUAGE, StringQueryOp.EQUAL, "ja");
//add to main where clause
main.addWhereClause(langWC);
main.addWhereClause(transLangWC);
main.setMatchAny(true);

Now,
query.setWhereClause(main) is giving 33 results
query.setWhereClause(langWC) is giving 346 results
query.setWhereClause(! transLangWC) is giving 30 results

Am I doing something wrong here?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic: Query with ContentInstanceDBQuery...
Forum: Discussion Group - Web Experience Management
Livelink Server: knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic:	Query with ContentInstanceDBQuery...
Forum:	Discussion Group - Web Experience Management
Livelink Server:	knowledge-wlweb01

David_Stiles

There’s no way to do what you’re asking here with the DBQueryAPI. And I think you’re probably encountering a defect that has been correctedsince version 7.5. I’m not sure exactly what version this problem would havebeen corrected in, but the SQL generated by DBQuery in the latest version isdifferent than what you’re seeing. Upgrading would, of course, be one approach,but there may be ways to work around this even with 7.5. But there has been alot of work done on this API since 7.5 to repair a variety of problems, so I’mnot sure what else you might encounter while trying to work around this.

One thing you can try is to obtain the count of result items adifferent way. The ‘select count’ query is issued by callingIPagingList.size(), however, you can instead call IPagingList.asList().size().The asList() method will select all the result items (instead of just thecount) and return a standard java List that you can get the size of. If yourresult set is expected to be very large though, then you might prefer toiterate using IPagingList.subList() to fetch the results in smaller chunks, butthen you’ll have to assume you’ve reached the end when you get back fewer itemsin a chunk than what you requested. You might also still get duplicates in theresult set if you don’t use RequestParameters.setTopRelationOnly(true).

If you continue to get unexpected results with these otherapproaches then another workaround you could try is to issue independent queries,one to filter on the ctd_news table and a separate one to filter on thectd_news_trans table, and then merge the results in memory.

In any case, if you’re going to stay on 7.5 then I stronglysuggest that you verify your approach by examining the generated SQL written tothe log. If you continue to find inappropriate SQL for your use case then you’llneed to continue looking for a different workaround. In the worst case, youcould write/execute your own SQL to filter your custom data by and identify theresults you want, and then use the VCM API to fetch those results by primarykey values.

From: eLink Entry:Discussion Group - Web Experience Management[mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Monday, May 23, 2011 5:34 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE RE Query withContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/23 06:33

Hi David,

Thanks,

Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 23 May 2011 10:52
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/23 05:51

Hi David,

I am on VCM 7.5. Exact SQLs are below:-

select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId = ? and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a1.LOCALE = ?) or (a3.LOCALE = ?)))], PARAM[ 1=69b94bd629642110VgnVCM100000426013ac____ 2=ja 3=ja]

select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1,VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a3.LOCALE ='ja')))

select count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a0.recordId = a2.contentMgmtId and ((a1.LOCALE ='ja')))

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 20 May 2011 18:11
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/20 13:10

Well, it still isn’t entirely clear from this. What version VCM are you running on? Also, can you post the exact SQL taken from the log for each of these cases?

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Friday, May 20, 2011 9:17 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/20 10:16

Hi David,

Yes, it was a typo. Below are the SQL queries being executed respectively:-

select count(*) from ctd_news t, ctd_news_trans t1 where (t.LOCALE='ja' or t1.LOCALE='ja') and (t.id=t1.parent_id)
select count(*) from ctd_news where locale='ja'
select COUNT(*) from ctd_news t1, ctd_news_trans t2 where (t2.locale='ja') and (t1.id= t2.parent_id)

Any input/suggestion to achieve this in other way?

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 18 May 2011 17:39
To: eLink Recipient
Subject: RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/18 12:38

First, I’m assuming that “query.setWhereClause(! transLangWC)” is a typo and you really just mean “query.setWhereClause(transLangWC)”.

But I can’t explain the results you’re seeing. One thing you can do though is set your VCM’s log level to debug and check the generated SQL that gets logged to see if it is as you expect for each of the three cases.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Wednesday, May 18, 2011 3:42 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/18 04:36

Hi David,

I am seeing an strange issue with 'whereclause'.

ContentInstanceDBQuery query = new ContentInstanceDBQuery(new ContentTypeRef(ct));
//Craeting objects of ContentInstanceWhereClause
ContentInstanceWhereClause main = new ContentInstanceWhereClause();
ContentInstanceWhereClause langWC = new ContentInstanceWhereClause();
ContentInstanceWhereClause transLangWC = new ContentInstanceWhereClause();
//Check for language
langWC.checkAttribute(LANGUAGE, StringQueryOp.EQUAL, "ja");
//Check for translated language
transLangWC.checkAttribute(TRANSLATED_LANGUAGE, StringQueryOp.EQUAL, "ja");
//add to main where clause
main.addWhereClause(langWC);
main.addWhereClause(transLangWC);
main.setMatchAny(true);

Now,
query.setWhereClause(main) is giving 33 results
query.setWhereClause(langWC) is giving 346 results
query.setWhereClause(! transLangWC) is giving 30 results

Am I doing something wrong here?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic:	Query with ContentInstanceDBQuery...
Forum:	Discussion Group - Web Experience Management
Livelink Server:	knowledge-wlweb01

Sanjeev_Kumar

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi David,

Thanks for such detailed analysis. I am using below for iterating through the paging list.

IPagingList results = QueryManager.execute(query);
Iterator it = results.pageIterator(ITERATOR_SIZE);

Issue is that, it is a web-application and as we have large number of instances, it is not able to iterate through complete list in some cases within 600 SEC and resulting into STUCK_THREADS.

Thanks,

Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 23 May 2011 17:38
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery... Posted by dstiles (Stiles, David) On 2011/05/23 12:37

There’s no way to do what you’re asking here with the DBQuery API. And I think you’re probably encountering a defect that has been corrected since version 7.5. I’m not sure exactly what version this problem would have been corrected in, but the SQL generated by DBQuery in the latest version is different than what you’re seeing. Upgrading would, of course, be one approach, but there may be ways to work around this even with 7.5. But there has been a lot of work done on this API since 7.5 to repair a variety of problems, so I’m not sure what else you might encounter while trying to work around this. One thing you can try is to obtain the count of result items a different way. The ‘select count’ query is issued by calling IPagingList.size(), however, you can instead call IPagingList.asList().size(). The asList() method will select all the result items (instead of just the count) and return a standard java List that you can get the size of. If your result set is expected to be very large though, then you might prefer to iterate using IPagingList.subList() to fetch the results in smaller chunks, but then you’ll have to assume you’ve reached the end when you get back fewer items in a chunk than what you requested. You might also still get duplicates in the result set if you don’t use RequestParameters.setTopRelationOnly(true). If you continue to get unexpected results with these other approaches then another workaround you could try is to issue independent queries, one to filter on the ctd_news table and a separate one to filter on the ctd_news_trans table, and then merge the results in memory. In any case, if you’re going to stay on 7.5 then I strongly suggest that you verify your approach by examining the generated SQL written to the log. If you continue to find inappropriate SQL for your use case then you’ll need to continue looking for a different workaround. In the worst case, you could write/execute your own SQL to filter your custom data by and identify the results you want, and then use the VCM API to fetch those results by primary key values. From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Monday, May 23, 2011 5:34 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery... RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/23 06:33

Hi David,

'select count(*) from ctd_news where id in (select id from ctd_news where locale='ja' Union all select parent_id from ctd_news_trans where locale='ja')' is giving the intended result. Could you please guide us in implementing above with CIDBQuery?

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 23 May 2011 10:52
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/23 05:51

Hi David,

I am on VCM 7.5. Exact SQLs are below:-

select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId = ? and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a1.LOCALE = ?) or (a3.LOCALE = ?)))], PARAM[ 1=69b94bd629642110VgnVCM100000426013ac____ 2=ja 3=ja]

select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1,VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a3.LOCALE ='ja')))

select count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a0.recordId = a2.contentMgmtId and ((a1.LOCALE ='ja')))

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 20 May 2011 18:11
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/20 13:10

Well, it still isn’t entirely clear from this. What version VCM are you running on? Also, can you post the exact SQL taken from the log for each of these cases?

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Friday, May 20, 2011 9:17 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/20 10:16

Hi David,

Yes, it was a typo. Below are the SQL queries being executed respectively:-

select count(*) from ctd_news t, ctd_news_trans t1 where (t.LOCALE='ja' or t1.LOCALE='ja') and (t.id=t1.parent_id)
select count(*) from ctd_news where locale='ja'
select COUNT(*) from ctd_news t1, ctd_news_trans t2 where (t2.locale='ja') and (t1.id= t2.parent_id)

Any input/suggestion to achieve this in other way?

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 18 May 2011 17:39
To: eLink Recipient
Subject: RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/18 12:38

First, I’m assuming that “query.setWhereClause(! transLangWC)” is a typo and you really just mean “query.setWhereClause(transLangWC)”.

But I can’t explain the results you’re seeing. One thing you can do though is set your VCM’s log level to debug and check the generated SQL that gets logged to see if it is as you expect for each of the three cases.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Wednesday, May 18, 2011 3:42 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/18 04:36

Hi David,

I am seeing an strange issue with 'whereclause'.

ContentInstanceDBQuery query = new ContentInstanceDBQuery(new ContentTypeRef(ct));
//Craeting objects of ContentInstanceWhereClause
ContentInstanceWhereClause main = new ContentInstanceWhereClause();
ContentInstanceWhereClause langWC = new ContentInstanceWhereClause();
ContentInstanceWhereClause transLangWC = new ContentInstanceWhereClause();
//Check for language
langWC.checkAttribute(LANGUAGE, StringQueryOp.EQUAL, "ja");
//Check for translated language
transLangWC.checkAttribute(TRANSLATED_LANGUAGE, StringQueryOp.EQUAL, "ja");
//add to main where clause
main.addWhereClause(langWC);
main.addWhereClause(transLangWC);
main.setMatchAny(true);

Now,
query.setWhereClause(main) is giving 33 results
query.setWhereClause(langWC) is giving 346 results
query.setWhereClause(! transLangWC) is giving 30 results

Am I doing something wrong here?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic: Query with ContentInstanceDBQuery...
Forum: Discussion Group - Web Experience Management
Livelink Server: knowledge-wlweb01

eLink User

If you need this data in Content delivery and you do not need to filter by Vignette context (current channel, current site, current content type etc.) then it might be easier to use simple jdbc query to your content table. However, if you need the data in context of a Vignette object, then you do not have that choice.

Sohail

On Tue, May 24, 2011 at 5:16 AM, eLink Entry: Discussion Group - Web Experience Management <v7webcontentmanagement@elinkkc.opentext.com> wrote:

RE RE RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery... ? Posted bysanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev)On 2011/05/24 05:15
?
Hi David,
?
Thanks for such detailed analysis. I am using below for iterating through the paging list.
IPagingList results = QueryManager.execute(query);
Iterator it = results.pageIterator(ITERATOR_SIZE);
?
Issue is that, it is a web-application and as we have?large number of instances, it is not able to iterate through complete list in some cases within 600 SEC and resulting into STUCK_THREADS.
?
Thanks,
Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.
?
-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 23 May 2011 17:38
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery... ? Posted by dstiles (Stiles, David) On 2011/05/23 12:37
?
There?s no way to do what you?re asking here with the DBQuery API. And I think you?re probably encountering a defect that has been corrected since version 7.5. I?m not sure exactly what version this problem would have been corrected in, but the SQL generated by DBQuery in the latest version is different than what you?re seeing. Upgrading would, of course, be one approach, but there may be ways to work around this even with 7.5. But there has been a lot of work done on this API since 7.5 to repair a variety of problems, so I?m not sure what else you might encounter while trying to work around this. One thing you can try is to obtain the count of result items a different way.? The ?select count? query is issued by calling IPagingList.size(), however, you can instead call IPagingList.asList().size(). The asList() method will select all the result items (instead of just the count) and return a standard java List that you can get the size of. If your result set is expected to be very large though, then you might prefer to iterate using IPagingList.subList() to fetch the results in smaller chunks, but then you?ll have to assume you?ve reached the end when you get back fewer items in a chunk than what you requested. You might also still get duplicates in the result set if you don?t use RequestParameters.setTopRelationOnly(true). If you continue to get unexpected results with these other approaches then another workaround you could try is to issue independent queries, one to filter on the ctd_news table and a separate one to filter on the ctd_news_trans table, and then merge the results in memory. In any case, if you?re going to stay on 7.5 then I strongly suggest that you verify your approach by examining the generated SQL written to the log. If you continue to find inappropriate SQL for your use case then you?ll need to continue looking for a different workaround. In the worst case, you could write/execute your own SQL to filter your custom data by and identify the results you want, and then use the VCM API to fetch those results by primary key values. From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Monday, May 23, 2011 5:34 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/23 06:33

Hi David,

Thanks,

Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 23 May 2011 10:52
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

?

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/23 05:51

Hi David,

I am on VCM 7.5. Exact SQLs are below:-

select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId = ? and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a1.LOCALE = ?) or (a3.LOCALE = ?)))], PARAM[ 1=69b94bd629642110VgnVCM100000426013ac____ 2=ja 3=ja]

select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1,VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a3.LOCALE ='ja')))

select count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a0.recordId = a2.contentMgmtId and ((a1.LOCALE ='ja')))

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 20 May 2011 18:11
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

?

Posted by dstiles (Stiles, David) On 2011/05/20 13:10

Well, it still isn?t entirely clear from this. What version VCM are you running on? Also, can you post the exact SQL taken from the log for each of these cases?

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Friday, May 20, 2011 9:17 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

?

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/20 10:16

Hi David,

Yes, it was a typo. Below are the SQL queries being executed respectively:-

select count(*) from ctd_news t, ctd_news_trans t1 where (t.LOCALE='ja' or t1.LOCALE='ja')? and (t.id=t1.parent_id)
select count(*) from ctd_news where locale='ja'
select? COUNT(*) from ctd_news t1, ctd_news_trans t2 where (t2.locale='ja') and (t1.id= t2.parent_id)

Any input/suggestion to achieve this in other way?

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 18 May 2011 17:39
To: eLink Recipient
Subject: RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

?

Posted by dstiles (Stiles, David) On 2011/05/18 12:38

First, I?m assuming that ?query.setWhereClause(! transLangWC)? is a typo and you really just mean ?query.setWhereClause(transLangWC)?.

But I can?t explain the results you?re seeing. One thing you can do though is set your VCM?s log level to debug and check the generated SQL that gets logged to see if it is as you expect for each of the three cases.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Wednesday, May 18, 2011 3:42 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE Query with ContentInstanceDBQuery...

?

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/18 04:36

Hi David,

I am seeing an strange issue with 'whereclause'.

ContentInstanceDBQuery query = new ContentInstanceDBQuery(new ContentTypeRef(ct));
//Craeting objects of ContentInstanceWhereClause
ContentInstanceWhereClause main = new ContentInstanceWhereClause();
ContentInstanceWhereClause langWC = new ContentInstanceWhereClause();
ContentInstanceWhereClause transLangWC = new ContentInstanceWhereClause();
//Check for language
langWC.checkAttribute(LANGUAGE, StringQueryOp.EQUAL, "ja");
//Check for translated language
transLangWC.checkAttribute(TRANSLATED_LANGUAGE, StringQueryOp.EQUAL, "ja");
//add to main where clause
main.addWhereClause(langWC);
main.addWhereClause(transLangWC);
main.setMatchAny(true);
?

Now,
query.setWhereClause(main) is giving 33 results
query.setWhereClause(langWC) is giving 346 results
query.setWhereClause(! transLangWC) is giving 30 results

Am I doing something wrong here?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic:	Query with ContentInstanceDBQuery...
Forum:	Discussion Group - Web Experience Management
Livelink Server:	knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic:	Query with ContentInstanceDBQuery...
Forum:	Discussion Group - Web Experience Management
Livelink Server:	knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic:	Query with ContentInstanceDBQuery...
Forum:	Discussion Group - Web Experience Management
Livelink Server:	knowledge-wlweb01

--
Regards,
Sohail Aboobaker.
Accumulate Consulting Inc.
www.letsaccumulate.com.
Ph: 416 907 9050

David_Stiles

I agree with Sohail. But if you’re going to continue trying towork with DBQuery then you shouldn’t be using IPagingList.pageIterator(), butinstead should use either asList() or subList() as I suggested before. This implementationof IPagingList.pageIterator() relies on IPagingList.size(), so if you’regetting inaccurate results from IPagingList.size() then IPagingList.pageIterator()isn’t going to work for you either.

From: eLink Entry:Discussion Group - Web Experience Management[mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Tuesday, May 24, 2011 5:02 AM
To: eLink Recipient
Subject: Re RE RE RE RE RE RE RE RE RE RE RE RE RE Query withContentInstanceDBQuery...

Re RE RE RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by eLink On 2011/05/24 06:00

On Tue, May 24, 2011 at 5:16 AM, eLink Entry: Discussion Group - Web Experience Management <v7webcontentmanagement@elinkkc.opentext.com> wrote:

RE RE RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/24 05:15

Hi David,

Thanks for such detailed analysis. I am using below for iterating through the paging list.

IPagingList results = QueryManager.execute(query);
Iterator it = results.pageIterator(ITERATOR_SIZE);

Issue is that, it is a web-application and as we havelarge number of instances, it is not able to iterate through complete list in some cases within 600 SEC and resulting into STUCK_THREADS.

Thanks,

Sanjeev

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 23 May 2011 17:38
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/23 12:37

Theres no way to do what youre asking here with the DBQuery API. And I think youre probably encountering a defect that has been corrected since version 7.5. Im not sure exactly what version this problem would have been corrected in, but the SQL generated by DBQuery in the latest version is different than what youre seeing. Upgrading would, of course, be one approach, but there may be ways to work around this even with 7.5. But there has been a lot of work done on this API since 7.5 to repair a variety of problems, so Im not sure what else you might encounter while trying to work around this.

One thing you can try is to obtain the count of result items a different way. The select count query is issued by calling IPagingList.size(), however, you can instead call IPagingList.asList().size(). The asList() method will select all the result items (instead of just the count) and return a standard java List that you can get the size of. If your result set is expected to be very large though, then you might prefer to iterate using IPagingList.subList() to fetch the results in smaller chunks, but then youll have to assume youve reached the end when you get back fewer items in a chunk than what you requested. You might also still get duplicates in the result set if you dont use RequestParameters.setTopRelationOnly(true).

If you continue to get unexpected results with these other approaches then another workaround you could try is to issue independent queries, one to filter on the ctd_news table and a separate one to filter on the ctd_news_trans table, and then merge the results in memory.

In any case, if youre going to stay on 7.5 then I strongly suggest that you verify your approach by examining the generated SQL written to the log. If you continue to find inappropriate SQL for your use case then youll need to continue looking for a different workaround. In the worst case, you could write/execute your own SQL to filter your custom data by and identify the results you want, and then use the VCM API to fetch those results by primary key values.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Monday, May 23, 2011 5:34 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/23 06:33

Hi David,

'select count(*) from ctd_news where id in (select id from ctd_news where locale='ja' Union all select parent_id from ctd_news_trans where locale='ja')' is giving the intended result. Could you please guide us in implementing above with CIDBQuery?

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 23 May 2011 10:52
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/23 05:51

Hi David,

I am on VCM 7.5. Exact SQLs are below:-

select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId = ? and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a1.LOCALE = ?) or (a3.LOCALE = ?)))], PARAM[ 1=69b94bd629642110VgnVCM100000426013ac____ 2=ja 3=ja]

select distinct count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1,VCMSYS.vgnAsMoMetaData a2, CONTENTM.CTD_NEWS_TRANS a3 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a1.ID = a3.PARENT_ID and a0.recordId = a2.contentMgmtId and ((a3.LOCALE ='ja')))

select count(*) from VCMSYS.vgnAsMoMap a0, CONTENTM.CTD_NEWS a1, VCMSYS.vgnAsMoMetaData a2 where (a0.keyString1 = a1.ID and a2.objectTypeId ='69b94bd629642110VgnVCM100000426013ac____' and a0.recordId = a2.contentMgmtId and ((a1.LOCALE ='ja')))

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 20 May 2011 18:11
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/20 13:10

Well, it still isnt entirely clear from this. What version VCM are you running on? Also, can you post the exact SQL taken from the log for each of these cases?

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Friday, May 20, 2011 9:17 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/20 10:16

Hi David,

Yes, it was a typo. Below are the SQL queries being executed respectively:-

select count(*) from ctd_news t, ctd_news_trans t1 where (t.LOCALE='ja' or t1.LOCALE='ja') and (t.id=t1.parent_id)
select count(*) from ctd_news where locale='ja'
select COUNT(*) from ctd_news t1, ctd_news_trans t2 where (t2.locale='ja') and (t1.id= t2.parent_id)

Any input/suggestion to achieve this in other way?

Thanks,

Sanjeev

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

-----Original Message-----

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: 18 May 2011 17:39
To: eLink Recipient
Subject: RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by dstiles (Stiles, David) On 2011/05/18 12:38

First, Im assuming that query.setWhereClause(! transLangWC) is a typo and you really just mean query.setWhereClause(transLangWC).

But I cant explain the results youre seeing. One thing you can do though is set your VCMs log level to debug and check the generated SQL that gets logged to see if it is as you expect for each of the three cases.

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Wednesday, May 18, 2011 3:42 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE Query with ContentInstanceDBQuery...

RE RE RE RE RE RE Query with ContentInstanceDBQuery...

Posted by sanjeev.x.kumar@astrazeneca.com (Kumar, Sanjeev) On 2011/05/18 04:36

Hi David,

I am seeing an strange issue with 'whereclause'.

ContentInstanceDBQuery query = new ContentInstanceDBQuery(new ContentTypeRef(ct));
//Craeting objects of ContentInstanceWhereClause
ContentInstanceWhereClause main = new ContentInstanceWhereClause();
ContentInstanceWhereClause langWC = new ContentInstanceWhereClause();
ContentInstanceWhereClause transLangWC = new ContentInstanceWhereClause();
//Check for language
langWC.checkAttribute(LANGUAGE, StringQueryOp.EQUAL, "ja");
//Check for translated language
transLangWC.checkAttribute(TRANSLATED_LANGUAGE, StringQueryOp.EQUAL, "ja");
//add to main where clause
main.addWhereClause(langWC);
main.addWhereClause(transLangWC);
main.setMatchAny(true);

Now,
query.setWhereClause(main) is giving 33 results
query.setWhereClause(langWC) is giving 346 results
query.setWhereClause(! transLangWC) is giving 30 results

Am I doing something wrong here?

Thanks,
Sanjeev

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]

Topic:

Query with ContentInstanceDBQuery...

Forum:

Discussion Group - Web Experience Management

Livelink Server:

knowledge-wlweb01

[To post a comment, use the normal reply function]
Topic:	Query with ContentInstanceDBQuery...
Forum:	Discussion Group - Web Experience Management
Livelink Server:	knowledge-wlweb01

--
Regards,
Sohail Aboobaker.
Accumulate Consulting Inc.
www.letsaccumulate.com.
Ph: 416 907 9050

<td

[To post a comment, use the normal reply function]
Topic:	Query with ContentInstanceDBQuery...