custom query on bizdoc

, ,
1

Hi,

I’m trying to pull records from bizdoc table using CUSTOM QUERY using the date range. But I could pull only records with hours >= 1 and <= 12

Example below query its working
select * from bizdoc a where a.doctypeid=‘EDIINT------------------’
and a.doctimestamp between ‘24-APR-13 01.00.01’ and ‘25-APR-13 01.00.01’;

But not the below; what needs to be done. Any help on these please.

select * from bizdoc a where a.doctypeid=‘EDIINT------------------’
and a.doctimestamp between “24-APR-13 00.00.01” and “25-APR-13 00.00.01”;

Regards,
Sam

2

I would suggest,instead use the tn:query folder services for any transaction query for custom reports.

use these main services below and review the TN BIS guide for more inputs information to these services:

createDocumentQuery
documentQuery

HTH,
RMG

3

Hi RMG,

Yes we can pull the records using

createDocumentQuery
documentQuery

But we can’t give the exact DayTime duration between which the search needs to be processed. We have only these timeInterval options:

TODAY
YESTERDAY
LAST_7_DAYS
THIS_WEEK
LAST_WEEK
THIS_MONTH
LAST_MONTH
YTD

When I used YTD, it pulled all the records. Any idea how to use the DateTime range?

Regards,
Sam

4

Yes you are right there is not option for the Custom inputs like in TA.

But you might want to take some DBA help if you still want to run the custom db query against TN internal tables as some of the timestamps not works as expected.

HTH,
RMG

5

Hi RMG,

we can use like below; it worked for me.

select * from bizdoc a where a.doctypeid=‘EDIINT------------------’
and to_char(a.doctimestamp,‘dd-MM-yy HH-MI-SS AM’) between ‘23-04-13 04-51-46 AM’ and ‘25-04-13 04-51-46 AM’

Regards,
Sam

6

Other way is using dynamic:

Query: select * from bizdoc ${where}
Input: where doctypeid=‘EDIINT------------------’ and to_char(doctimestamp,‘dd-MM-yy HH-MI-SS AM’) between ‘23-04-13 04-51-46 AM’ and ‘25-04-13 04-51-46 AM’

7

As long as it works for you and thanks for the update: