Livereport Syntax For Record Date == Creation Date

Running V9.5.0I'm trying to find all objects that have the same record date as the creation date. Many objects we added to the system without setting the correct record date. I think the search I want is something like[QLRegion rmDocDate]=[QLRegion rmStatusDate]But I don't know the proper SQL to do that search.

Find more posts tagged with

Comments

Nicole_Bride

Message from Bride, Nicole <nbride@cvps.com> via eLink

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

Well you could run something like:

select dtree.createdate,rimsnodeClassification.rimsdocdate
from dtree, rimsnodeClassification
where dtree.dataid=rimsnodeClassification.nodeid
and dtree.createdate=rimsnodeClassification.rimsdocdate

..but the problem is that the types are different, so that won't actually work. The rimsDocDate is a int, and the CreateDate is a datetime.

I will continue to work on it, but maybe someone more proficient in SQL can help out

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, March 06, 2009 10:03 AM
To: eLink Recipient
Subject: Livereport Syntax For Record Date == Creation Date

Livereport Syntax For Record Date == Creation Date

Posted by blakecarver (Carver, Blake) on 2009/03/06 10:01

Running V9.5.0

I'm trying to find all objects that have the same record date as the creation date. Many objects we added to the system without setting the correct record date. I think the search I want is something like
[QLRegion rmDocDate]=[QLRegion rmStatusDate]

But I don't know the proper SQL to do that search.

Appu_Nair

This is not a full report but just the relation(or what I think is the relation).Dtree.dataid is rimsnodeclassification.nodeiddtree.createdate gives you object creation datedtree.modifydate gives you mod date also updates statusdatein rimsnodeclassification.rimsnodeclassification.rimsdocdate gives you the "Record Date" in Records Detail page.So one can write something like this for a known objectid.select d.createdate,r.rimsdocdate from qall.livelink.rimsnodeclassification R,qall.livelink.dtree Dwhere r.nodeid=d.dataid and r.nodeid=186748it output will look likecreatedate rimsdocdate2008-04-28 14:20:16.000 20080428As you can see the column is dtree is a date/time object and in rimsnode it is an integer.So to run your relation check depending on oracle /sqlserver you , IMHO has to come up with a function that takes care of that.I hope somebody reading this will be kind enough to either tell if the relation is correct or may have done something similar that they could share.IMHO you should post a link in the RM place as well

Nicole_Bride

Message from Bride, Nicole <nbride@cvps.com> via eLink

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

This seems to work. I'm not sure how efficient it is though. I'm running it through SQL Mgmt Studio, not as a LiveReport.

select DateAdd(day, datediff(day,0, dtree.createdate), 0) as "Create Date", convert(datetime, (cast(cast(rimsnodeClassification.rimsdocdate AS char(8)) AS datetime)), 112) as "Record Date"
from dtree, rimsnodeClassification
where dtree.dataid=rimsnodeClassification.nodeid
and DateAdd(day, datediff(day,0, dtree.createdate), 0) = convert(datetime, (cast(cast(rimsnodeClassification.rimsdocdate AS char(8)) AS datetime)), 112)

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, March 06, 2009 10:51 AM
To: eLink Recipient
Subject: RE Livereport Syntax For Record Date == Creation Date

RE Livereport Syntax For Record Date == Creation Date		Posted by nbride (Bride, Nicole) on 2009/03/06 10:50
In reply to: Livereport Syntax For Record Date == Creation Date		Posted by blakecarver (Carver, Blake) on 2009/03/06 10:01

Message from Bride, Nicole <nbride@cvps.com> via eLink

Well you could run something like: select dtree.createdate,rimsnodeClassification.rimsdocdate
from dtree, rimsnodeClassification
where dtree.dataid=rimsnodeClassification.nodeid
and dtree.createdate=rimsnodeClassification.rimsdocdate ..but the problem is that the types are different, so that won't actually work. The rimsDocDate is a int, and the CreateDate is a datetime. I will continue to work on it, but maybe someone more proficient in SQL can help out

Livereport Syntax For Record Date == Creation Date

Posted by blakecarver (Carver, Blake) on 2009/03/06 10:01

Running V9.5.0

I'm trying to find all objects that have the same record date as the creation date. Many objects we added to the system without setting the correct record date. I think the search I want is something like
[QLRegion rmDocDate]=[QLRegion rmStatusDate]

But I don't know the proper SQL to do that search.