Report instances for last 1 week

[Former Member]

Hi,

I am trying to get the failed report instance using jsp. Is there a way to limit the output only to last 2 days some thing like sysdate()-2. Currently it displays all the failed instances.

I tried the below and it says Not a valid query. (FWB 00025)

SELECT * from CI_INFOOBJECTS WHERE si_instance>=1 and si_schedule_status=3 and

si_kind in ('CrystalReport','Webi','FullClient','ObjectPackage') order by si_creation_time > sysdate()-2

Any suggestions appreciated.

Thanks,
Arun

[DellSC · Accepted solution]

SI_CREATION_TIME may be a field in the database, but the SDK won't read it.  Instead, you need to use SI_CREATION_TIMESTAMP which is a "field" that is parsed from a blob that contains all of the InfoObject properties.  So, you'll need to calculate the date from two days ago in your .jsp code, format it so the SDK will read it correctly (yyyy.MM.dd.hh.mm.ss), and use it in the query.  It will look something like this:

SELECT * from CI_INFOOBJECTS WHERE si_instance>=1 and si_schedule_status=3 and

si_kind in ('CrystalReport','Webi','FullClient','ObjectPackage') and SI_CREATION_TIMESTAMP >= '2016.01.13.00.00.00'

Also, you have it in an "order by" clause instead of in the "where" clause, which is what's giving you the "invalid query" message.

-Dell

[Former Member · Accepted solution]

Thank you Dell. I tried both SI_CREATION_TIME and SI_CREATION_TIMESTAMP in the query. Both gives the same output. If i run the query like below, it will throw a blank page.

SELECT * from CI_INFOOBJECTS WHERE si_instance>=1 and si_schedule_status=3 and

si_kind in ('CrystalReport','Webi','FullClient','ObjectPackage') and SI_CREATION_TIMESTAMP >= '2016.01.13.00.00.00'

I tried using the below code but it also throws a blank page and no error -

Calendar cal = Calendar.getInstance();

   cal.add(Calendar.DATE, -10);

   Date date1 = cal.getTime();

   String formattedDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date1);

IInfoObjects infoobjects = iStore.query("SELECT * from CI_INFOOBJECTS WHERE si_instance>=1 and si_schedule_status=3 and si_kind in ('CrystalReport','Webi','FullClient','ObjectPackage') and SI_CREATION_TIMESTAMP >= 'formattedDate'");

Anything i am doing wrong here?

[DellSC · Accepted solution]

Unfortunately, I don't have access to a QueryBuilder instance right now because I'm on-site with a client.  I'll try to look at this over the weekend and see what I can come up with for you.

-Dell

[DellSC · Accepted solution]

This query works for me in QueryBuilder:

Select * from CI_INFOOBJECTS where SI_INSTANCE = 1 and SI_SCHEDULE_STATUS = 3 and SI_CREATION_TIME >= '2016.01.16.00.00.00'

I was wrong in my previous post - it is SI_CREATION_TIME, not SI_CREATION_TIMESTAMP.

The trick is that you have to format the date correctly - change your date format to "yyyy.MM.dd.HH.mm.ss"

-Dell

[Former Member · Accepted solution]

Thanks Dell. It worked.