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; } } }

Getting cross multiple records

nikhil.sanghvi

Hi,

I have the below report working and giving the results as required. The data is pulled from the FORM custom table (VW_PRSDM_SDS_PROC) which is used by WF and WFATTRDATA table and KUAF table. Below is the SQL Query,

select
VW_PRSDM_SDS_PROC.SDS_INTERNAL_NUMBER as SDS_INTERNAL_NUMBER, VW_PRSDM_SDS_PROC.SDS_PRODUCT_SUBSTANCE_NAME as SDS_PRODUCT_SUBSTANCE_NAME,
VW_PRSDM_SDS_PROC.VENDOR_SUPPLIER_NAME as VENDOR_SUPPLIER_NAME, VW_PRSDM_SDS_PROC.SDS_VERSION_VENDOR_NUMBER as SDS_VERSION_VENDOR_NUMBER,
VW_PRSDM_SDS_PROC.SDS_OT_ID as SDS_OT_ID,
WFATTRDATA.WF_VALINT as "Compliance_Item_ES",
VW_PRSDM_SDS_PROC.LOCATION_ID as LOCATION_ID,
TO_CHAR(VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE, 'YYYY-MM-DD') as "Site Received Date",
VW_PRSDM_SDS_PROC.QC_STATUS as QC_STATUS, KUAF.NAME as SITE_SDS_CONTACT_SSC,
VW_PRSDM_SDS_PROC.COMPLIANCE_CHECK_REQUIRED as COMPLIANCE_CHECK_REQUIRED
from
VW_PRSDM_SDS_PROC, WFATTRDATA, KUAF
where
VW_PRSDM_SDS_PROC.Region = 'AP'
and
VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE is not null
and (WFATTRDATA.WF_ID=VW_PRSDM_SDS_PROC.VOLUMEID and WFATTRDATA.WF_ATTRID=87)
and KUAF.id=VW_PRSDM_SDS_PROC.SITE_SDS_CONTACT_SSC

Now I need to make a change in the report to add a clause that the archived date is null (WF is not archived) and I have marked the changes in red below,

select
VW_PRSDM_SDS_PROC.SDS_INTERNAL_NUMBER as SDS_INTERNAL_NUMBER, VW_PRSDM_SDS_PROC.SDS_PRODUCT_SUBSTANCE_NAME as SDS_PRODUCT_SUBSTANCE_NAME,
VW_PRSDM_SDS_PROC.VENDOR_SUPPLIER_NAME as VENDOR_SUPPLIER_NAME, VW_PRSDM_SDS_PROC.SDS_VERSION_VENDOR_NUMBER as SDS_VERSION_VENDOR_NUMBER,
VW_PRSDM_SDS_PROC.SDS_OT_ID as SDS_OT_ID,
WFATTRDATA.WF_VALINT as "Compliance_Item_ES",
VW_PRSDM_SDS_PROC.LOCATION_ID as LOCATION_ID,
TO_CHAR(VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE, 'YYYY-MM-DD') as "Site Received Date",
VW_PRSDM_SDS_PROC.QC_STATUS as QC_STATUS, KUAF.NAME as SITE_SDS_CONTACT_SSC,
VW_PRSDM_SDS_PROC.COMPLIANCE_CHECK_REQUIRED as COMPLIANCE_CHECK_REQUIRED
from
VW_PRSDM_SDS_PROC, WFATTRDATA, KUAF, WWORKAUDIT
where
VW_PRSDM_SDS_PROC.Region = 'AP'
and
VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE is not null
and (WFATTRDATA.WF_ID=VW_PRSDM_SDS_PROC.VOLUMEID and WFATTRDATA.WF_ATTRID=87)
and KUAF.id=VW_PRSDM_SDS_PROC.SITE_SDS_CONTACT_SSC
and VW_PRSDM_SDS_PROC.VOLUMEID=WWORKAUDIT.WORKAUDIT_WORKID

But after doing this, I am getting the cross multiple records. What I am missing? I am mapping the FORMS VolumeID with the WWORKAUDIT WORKID column.

Find more posts tagged with

Comments

Colin_Schmidt

You are not using a "join" statement. Yours is written to allow all possible combinations. I used to write queries like yours, but it was too simplistic. I started using "join" or "left outer join", etc. to achieve what I wanted. To include only possibilities that match the criteria, a "join" is necessary. In the below, all criteria must match. While I'm no expert in any degree, I will always defer to those who definitely are. In the separation of the Tables by their individual JOINs, I can also separate the conditions specifically to them and not guess as to which condition down in the WHERE belongs to which table. It makes it cleaner to me to understand, though it won't necessarily make it run more effficient.

Try this.

select VW.SDS_INTERNAL_NUMBER as SDS_INTERNAL_NUMBER, VW.SDS_PRODUCT_SUBSTANCE_NAME as SDS_PRODUCT_SUBSTANCE_NAME,

VW.VENDOR_SUPPLIER_NAME as VENDOR_SUPPLIER_NAME, VW.SDS_VERSION_VENDOR_NUMBER as SDS_VERSION_VENDOR_NUMBER,

VW.SDS_OT_ID as SDS_OT_ID, WFA.WF_VALINT as "Compliance_Item_ES",

VW.LOCATION_ID as LOCATION_ID,

TO_CHAR(VW.SITE_RECEIVED_DATE, 'YYYY-MM-DD') as "Site Received Date",

VW.QC_STATUS as QC_STATUS, K.NAME as SITE_SDS_CONTACT_SSC,

VW.COMPLIANCE_CHECK_REQUIRED as COMPLIANCE_CHECK_REQUIRED

from VW_PRSDM_SDS_PROC VW

join WFATTRDATA wfa on (WFA.WF_ID=VW_PRSDM_SDS_PROC.VOLUMEID and WFA.WF_ATTRID=87)

join KUAF K on K.id=VW.SITE_SDS_CONTACT_SSC

join WWORKAUDIT WWA on VW.VOLUMEID=WWA.WORKAUDIT_WORKID

where VW.Region = 'AP'

and VW.SITE_RECEIVED_DATE is not null

Colin J.

On Tue, May 26, 2015 at 8:10 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Getting cross multiple records Posted bynikhil.d.sanghvi@accenture.com (Sanghvi, Nikhil D)On 05/26/2015 08:05 AM

Hi,I have the below report working and giving the results as required. The data is pulled from the FORM custom table (VW_PRSDM_SDS_PROC) which is used by WF and WFATTRDATA table and KUAF table. Below is the SQL Query,select
VW_PRSDM_SDS_PROC.SDS_INTERNAL_NUMBER as SDS_INTERNAL_NUMBER, VW_PRSDM_SDS_PROC.SDS_PRODUCT_SUBSTANCE_NAME as SDS_PRODUCT_SUBSTANCE_NAME,
VW_PRSDM_SDS_PROC.VENDOR_SUPPLIER_NAME as VENDOR_SUPPLIER_NAME, VW_PRSDM_SDS_PROC.SDS_VERSION_VENDOR_NUMBER as SDS_VERSION_VENDOR_NUMBER,
VW_PRSDM_SDS_PROC.SDS_OT_ID as SDS_OT_ID,
WFATTRDATA.WF_VALINT as "Compliance_Item_ES",
VW_PRSDM_SDS_PROC.LOCATION_ID as LOCATION_ID,
TO_CHAR(VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE, 'YYYY-MM-DD') as "Site Received Date",
VW_PRSDM_SDS_PROC.QC_STATUS as QC_STATUS, KUAF.NAME as SITE_SDS_CONTACT_SSC,
VW_PRSDM_SDS_PROC.COMPLIANCE_CHECK_REQUIRED as COMPLIANCE_CHECK_REQUIRED
from
VW_PRSDM_SDS_PROC, WFATTRDATA, KUAF
where
VW_PRSDM_SDS_PROC.Region = 'AP'
and
VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE is not null
and (WFATTRDATA.WF_ID=VW_PRSDM_SDS_PROC.VOLUMEID and WFATTRDATA.WF_ATTRID=87)
and KUAF.id=VW_PRSDM_SDS_PROC.SITE_SDS_CONTACT_SSC Now I need to make a change in the report to add a clause that the archived date is null (WF is not archived) and I have marked the changes in red below,select
VW_PRSDM_SDS_PROC.SDS_INTERNAL_NUMBER as SDS_INTERNAL_NUMBER, VW_PRSDM_SDS_PROC.SDS_PRODUCT_SUBSTANCE_NAME as SDS_PRODUCT_SUBSTANCE_NAME,
VW_PRSDM_SDS_PROC.VENDOR_SUPPLIER_NAME as VENDOR_SUPPLIER_NAME, VW_PRSDM_SDS_PROC.SDS_VERSION_VENDOR_NUMBER as SDS_VERSION_VENDOR_NUMBER,
VW_PRSDM_SDS_PROC.SDS_OT_ID as SDS_OT_ID,
WFATTRDATA.WF_VALINT as "Compliance_Item_ES",
VW_PRSDM_SDS_PROC.LOCATION_ID as LOCATION_ID,
TO_CHAR(VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE, 'YYYY-MM-DD') as "Site Received Date",
VW_PRSDM_SDS_PROC.QC_STATUS as QC_STATUS, KUAF.NAME as SITE_SDS_CONTACT_SSC,
VW_PRSDM_SDS_PROC.COMPLIANCE_CHECK_REQUIRED as COMPLIANCE_CHECK_REQUIRED
from
VW_PRSDM_SDS_PROC, WFATTRDATA, KUAF, WWORKAUDIT
where
VW_PRSDM_SDS_PROC.Region = 'AP'
and
VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE is not null
and (WFATTRDATA.WF_ID=VW_PRSDM_SDS_PROC.VOLUMEID and WFATTRDATA.WF_ATTRID=87)
and KUAF.id=VW_PRSDM_SDS_PROC.SITE_SDS_CONTACT_SSC
and VW_PRSDM_SDS_PROC.VOLUMEID=WWORKAUDIT.WORKAUDIT_WORKIDBut after doing this, I am getting the cross multiple records. What I am missing? I am mapping the FORMS VolumeID with the WWORKAUDIT WORKID column.
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation (mwv.com) or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Colin_Schmidt

Try this.

select VW.SDS_INTERNAL_NUMBER as SDS_INTERNAL_NUMBER, VW.SDS_PRODUCT_SUBSTANCE_NAME as SDS_PRODUCT_SUBSTANCE_NAME,

VW.VENDOR_SUPPLIER_NAME as VENDOR_SUPPLIER_NAME, VW.SDS_VERSION_VENDOR_NUMBER as SDS_VERSION_VENDOR_NUMBER,

VW.SDS_OT_ID as SDS_OT_ID, WFA.WF_VALINT as "Compliance_Item_ES",

VW.LOCATION_ID as LOCATION_ID,

TO_CHAR(VW.SITE_RECEIVED_DATE, 'YYYY-MM-DD') as "Site Received Date",

VW.QC_STATUS as QC_STATUS, K.NAME as SITE_SDS_CONTACT_SSC,

VW.COMPLIANCE_CHECK_REQUIRED as COMPLIANCE_CHECK_REQUIRED

from VW_PRSDM_SDS_PROC VW

join WFATTRDATA wfa on (WFA.WF_ID=VW_PRSDM_SDS_PROC.VOLUMEID and WFA.WF_ATTRID=87)

join KUAF K on K.id=VW.SITE_SDS_CONTACT_SSC

join WWORKAUDIT WWA on VW.VOLUMEID=WWA.WORKAUDIT_WORKID

where VW.Region = 'AP'

and VW.SITE_RECEIVED_DATE is not null

Colin J.

On Tue, May 26, 2015 at 8:10 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Getting cross multiple records Posted bynikhil.d.sanghvi@accenture.com (Sanghvi, Nikhil D)On 05/26/2015 08:05 AM

Hi,I have the below report working and giving the results as required. The data is pulled from the FORM custom table (VW_PRSDM_SDS_PROC) which is used by WF and WFATTRDATA table and KUAF table. Below is the SQL Query,select
VW_PRSDM_SDS_PROC.SDS_INTERNAL_NUMBER as SDS_INTERNAL_NUMBER, VW_PRSDM_SDS_PROC.SDS_PRODUCT_SUBSTANCE_NAME as SDS_PRODUCT_SUBSTANCE_NAME,
VW_PRSDM_SDS_PROC.VENDOR_SUPPLIER_NAME as VENDOR_SUPPLIER_NAME, VW_PRSDM_SDS_PROC.SDS_VERSION_VENDOR_NUMBER as SDS_VERSION_VENDOR_NUMBER,
VW_PRSDM_SDS_PROC.SDS_OT_ID as SDS_OT_ID,
WFATTRDATA.WF_VALINT as "Compliance_Item_ES",
VW_PRSDM_SDS_PROC.LOCATION_ID as LOCATION_ID,
TO_CHAR(VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE, 'YYYY-MM-DD') as "Site Received Date",
VW_PRSDM_SDS_PROC.QC_STATUS as QC_STATUS, KUAF.NAME as SITE_SDS_CONTACT_SSC,
VW_PRSDM_SDS_PROC.COMPLIANCE_CHECK_REQUIRED as COMPLIANCE_CHECK_REQUIRED
from
VW_PRSDM_SDS_PROC, WFATTRDATA, KUAF
where
VW_PRSDM_SDS_PROC.Region = 'AP'
and
VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE is not null
and (WFATTRDATA.WF_ID=VW_PRSDM_SDS_PROC.VOLUMEID and WFATTRDATA.WF_ATTRID=87)
and KUAF.id=VW_PRSDM_SDS_PROC.SITE_SDS_CONTACT_SSC Now I need to make a change in the report to add a clause that the archived date is null (WF is not archived) and I have marked the changes in red below,select
VW_PRSDM_SDS_PROC.SDS_INTERNAL_NUMBER as SDS_INTERNAL_NUMBER, VW_PRSDM_SDS_PROC.SDS_PRODUCT_SUBSTANCE_NAME as SDS_PRODUCT_SUBSTANCE_NAME,
VW_PRSDM_SDS_PROC.VENDOR_SUPPLIER_NAME as VENDOR_SUPPLIER_NAME, VW_PRSDM_SDS_PROC.SDS_VERSION_VENDOR_NUMBER as SDS_VERSION_VENDOR_NUMBER,
VW_PRSDM_SDS_PROC.SDS_OT_ID as SDS_OT_ID,
WFATTRDATA.WF_VALINT as "Compliance_Item_ES",
VW_PRSDM_SDS_PROC.LOCATION_ID as LOCATION_ID,
TO_CHAR(VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE, 'YYYY-MM-DD') as "Site Received Date",
VW_PRSDM_SDS_PROC.QC_STATUS as QC_STATUS, KUAF.NAME as SITE_SDS_CONTACT_SSC,
VW_PRSDM_SDS_PROC.COMPLIANCE_CHECK_REQUIRED as COMPLIANCE_CHECK_REQUIRED
from
VW_PRSDM_SDS_PROC, WFATTRDATA, KUAF, WWORKAUDIT
where
VW_PRSDM_SDS_PROC.Region = 'AP'
and
VW_PRSDM_SDS_PROC.SITE_RECEIVED_DATE is not null
and (WFATTRDATA.WF_ID=VW_PRSDM_SDS_PROC.VOLUMEID and WFATTRDATA.WF_ATTRID=87)
and KUAF.id=VW_PRSDM_SDS_PROC.SITE_SDS_CONTACT_SSC
and VW_PRSDM_SDS_PROC.VOLUMEID=WWORKAUDIT.WORKAUDIT_WORKIDBut after doing this, I am getting the cross multiple records. What I am missing? I am mapping the FORMS VolumeID with the WWORKAUDIT WORKID column.
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

nikhil.sanghvi

Hi Colin,

I am still getting the same result. The condition VW.REGION='AP' has only 1 result in the table, so I should get only 1 result. But here I see that I am getting the same result for N number of times.

In this query there are other 3 table, so if I remove the WWORKAUDIT table then everything is working fine. In the query which you have posted, here also if I do remove the WWORKAUDIT table join then it is working as expected.

Thank You.

Nikhil Sanghvi

Colin_Schmidt

Sometimes, you will get multiple entries because of multiples from another table. Part of this may have to be in a sub-query or rearranged.

Colin J. Schmidt

Opentext Content Server Support Admin

colin.schmidt@mwv.com
+1 804.444 5012 (Office)

+1 918.904 9223 (Cell)

MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Tue, May 26, 2015 at 1:51 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Getting cross multiple records 2 Posted bynikhil.d.sanghvi@accenture.com (Sanghvi, Nikhil D)On 05/26/2015 01:48 PM

Hi Colin,I am still getting the same result. The condition VW.REGION='AP' has only 1 result in the table, so I should get only 1 result. But here I see that I am getting the same result for N number of times.In this query there are other 3 table, so if I remove the WWORKAUDIT table then everything is working fine. In the query which you have posted, here also if I do remove the WWORKAUDIT table join then it is working as expected.Thank You.
Nikhil Sanghvi
[To post a comment, use the normal reply function]
Topic: Getting cross multiple records
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Colin_Schmidt

Sometimes, you will get multiple entries because of multiples from another table. Part of this may have to be in a sub-query or rearranged.

Colin J. Schmidt

Opentext Content Server Support Admin

colin.schmidt@mwv.com
+1 804.444 5012 (Office)

+1 918.904 9223 (Cell)

MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Tue, May 26, 2015 at 1:51 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Getting cross multiple records 2 Posted bynikhil.d.sanghvi@accenture.com (Sanghvi, Nikhil D)On 05/26/2015 01:48 PM

Hi Colin,I am still getting the same result. The condition VW.REGION='AP' has only 1 result in the table, so I should get only 1 result. But here I see that I am getting the same result for N number of times.In this query there are other 3 table, so if I remove the WWORKAUDIT table then everything is working fine. In the query which you have posted, here also if I do remove the WWORKAUDIT table join then it is working as expected.Thank You.
Nikhil Sanghvi
[To post a comment, use the normal reply function]
Topic: Getting cross multiple records
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Colin_Schmidt

I would peg the problem at the WWORKAUDIT table. You're accessing it on a high level pulling in every entry. I don't see anything being reported out of it. So, try this. It does pull in every possible row because all it's doing is checking for a condition "something exists". If it continues, then maybe a more restrictive select on the WFATTRDATA table..

select VW.SDS_INTERNAL_NUMBER as SDS_INTERNAL_NUMBER,

VW.SDS_PRODUCT_SUBSTANCE_NAME as SDS_PRODUCT_SUBSTANCE_NAME,

VW.VENDOR_SUPPLIER_NAME as VENDOR_SUPPLIER_NAME,

VW.SDS_VERSION_VENDOR_NUMBER as SDS_VERSION_VENDOR_NUMBER,

VW.SDS_OT_ID as SDS_OT_ID,

WFA.WF_VALINT as "Compliance_Item_ES",

VW.LOCATION_ID as LOCATION_ID,

TO_CHAR(VW.SITE_RECEIVED_DATE, 'YYYY-MM-DD') as "Site Received Date",

VW.QC_STATUS as QC_STATUS, K.NAME as SITE_SDS_CONTACT_SSC,

VW.COMPLIANCE_CHECK_REQUIRED as COMPLIANCE_CHECK_REQUIRED

from VW_PRSDM_SDS_PROC VW

join WFATTRDATA wfa on (WFA.WF_ID=VW_PRSDM_SDS_PROC.VOLUMEID and WFA.WF_ATTRID=87)

join KUAF K on K.id=VW.SITE_SDS_CONTACT_SSC

where VW.Region = 'AP' and VW.SITE_RECEIVED_DATE is not null

and EXISTS (select WWA.workaudit_workid from wworkaudit wwa where VW.VOLUMEID=WWA.WORKAUDIT_WORKID)

Colin J.

On Tue, May 26, 2015 at 2:04 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Re Getting cross multiple records 2 Posted bycolin.schmidt@mwv.com (Schmidt, Colin)On 05/26/2015 02:04 PM

Sometimes, you will get multiple entries because of multiples from another table. Part of this may have to be in a sub-query or rearranged.

Colin J.

On Tue, May 26, 2015 at 1:51 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:
Re Getting cross multiple records 2 Posted bynikhil.d.sanghvi@accenture.com (Sanghvi, Nikhil D)On 05/26/2015 01:48 PM

Hi Colin,I am still getting the same result. The condition VW.REGION='AP' has only 1 result in the table, so I should get only 1 result. But here I see that I am getting the same result for N number of times.In this query there are other 3 table, so if I remove the WWORKAUDIT table then everything is working fine. In the query which you have posted, here also if I do remove the WWORKAUDIT table join then it is working as expected.Thank You.
Nikhil Sanghvi
[To post a comment, use the normal reply function]
Topic: Getting cross multiple records
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Getting cross multiple records
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Appu_Nair1

I too am no SQL expert but looking at my system the audittable of workflow is designed so it can handle any number of duplicates.

When I look in the wworkaudit table I see that
295780 28826 295780 7 164429 12/22/2010 3:57:14 PM 41
295780 28827 295780 7 164429 12/22/2010 3:57:10 PM 29
so for the same workid, subworkid ,taskid you can get more than one row.
in your case if you are saying join my workflow like this
VW_PRSDM_SDS_PROC.VOLUMEID=WWORKAUDIT.WORKAUDIT_WORKID
that condition will pull out every row in wworkaudit for thet workid for every step. so you need to handle it correctly.One possibility is to provide a distinct clause to that,so see if you say select distinct(WWORKAUDIT.WORKAUDIT_WORKID) from WWORKAUDIT where work_workid=<value of a workflow instance id that you know>

These I would use to debug your query
select distinct(ww.workaudit_workid)from WWORKAUDIT WW where ww.workaudit_workid=295780;
if you run this
select ww.workaudit_workid from WWORKAUDIT WW where ww.workaudit_workid=295780;

My theory is if you don't restrict the WWORKAUDIT rows that your big query will give you that many records more than what you want.Lindsay may ask what pray is that you are tring to do,you seem to be after the audit table for some things?

nikhil.sanghvi

Hi Colin,

The query which you have posted does not give multiple output but it does not work as expected. What I am saying is, I need to change the SQL in such a way that it should not pull out the WF which are archived. And the archived status is in the WWORKAUDIT table where the WORKAUDIT_STATUS = 6. So status 6 is the WF is archived.

So I have added in the last line as

and (VW_PRSDM_SDS_PROC.VOLUMEID=WWORKAUDIT.WORKAUDIT_WORKID and WWORKAUDIT.WORKAUDIT_STATUS !='6')

But I see here that it is giving me all the status output in the above statement except which i archived. Which is ideally correct, but maybe it is multiplying all those result here. Don't know how to restrict it here.

@Appu: I am trying to add the WORKAUDIT status which is not equal to 6. So this should restrict but will still give multiple output as it is pulling out all the other status. But how to restrict the volumeid to not to take the workID which are archived (status=6)

Thank You.

Nikhil D Sanghvi

Appu_Nair1

If you want to restrict the workflow status you should look at wwork table. Also look at webactivework view actually many views OT uses to encapsulate that info. You have seen this document have you?

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=17344308&objAction=viewincontainer&ShowReplyEntry=17345056#forum_topic_17345056

it is a nice PDF that gives you ways of looking at WF data

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, May 27, 2015 9:40 AM
To: eLink Recipient
Subject: [EXTERNAL]Re Getting cross multiple records 2

Re Getting cross multiple records 2

Posted bynikhil.d.sanghvi@accenture.com (Sanghvi, Nikhil D) On 05/27/2015 10:35 AM

Hi Colin,

So I have added in the last line as

and (VW_PRSDM_SDS_PROC.VOLUMEID=WWORKAUDIT.WORKAUDIT_WORKID and WWORKAUDIT.WORKAUDIT_STATUS !='6')

Thank You.

Nikhil D Sanghvi

[To post a comment, use the normal reply function]
Topic:	Getting cross multiple records
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Colin_Schmidt

In WWORKAUDIT, you'll have all action taken by every step as many times as they may have looped and those not taken ('killed'). You only want, I'm guessing, to know that the WF is not archived. With that, you don't want to list it in with the other tables. As Appu noted, there is "DISTINCT" that may be used.

I sent the query back with "EXISTS (select ...)" ...add this last piece within the parenthesis('and WWA.WORKAUDIT_STATUS != 6) to it. "Status" is numeric, so no quotes.

Colin J.

On Wed, May 27, 2015 at 10:39 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Getting cross multiple records 2 Posted bynikhil.d.sanghvi@accenture.com (Sanghvi, Nikhil D)On 05/27/2015 10:35 AM

Hi Colin,The query which you have posted does not give multiple output but it does not work as expected. What I am saying is, I need to change the SQL in such a way that it should not pull out the WF which are archived. And the archived status is in the WWORKAUDIT table where the WORKAUDIT_STATUS = 6. So status 6 is the WF is archived.So I have added in the last line as
and (VW_PRSDM_SDS_PROC.VOLUMEID=WWORKAUDIT.WORKAUDIT_WORKID and WWORKAUDIT.WORKAUDIT_STATUS !='6')
But I see here that it is giving me all the status output in the above statement except which i archived. Which is ideally correct, but maybe it is multiplying all those result here. Don't know how to restrict it here.

@Appu: I am trying to add the WORKAUDIT status which is not equal to 6. So this should restrict but will still give multiple output as it is pulling out all the other status. But how to restrict the volumeid to not to take the workID which are archived (status=6)

Thank You.
Nikhil D Sanghvi
[To post a comment, use the normal reply function]
Topic: Getting cross multiple records
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center