Author Topic: Arsdoc Print Date range not working  (Read 5175 times)

brianesr

  • Guest
Arsdoc Print Date range not working
« on: September 11, 2017, 10:28:45 AM »
Hello,
so here is the code I'm using in QSH
Arsdoc print -f AACR04 -h QUSROND -v -i "WHERE grp = '77'" -P E98889  -S 01012013,01312013,%m%d%Y,   I also tried 010113,013113,%m%d%y,
But I always get 8 years of data.   
We are running V7r1 and OnDemand 9.5.0.7
I'm hopping some one else has ran into this

Thanks
Brian Ellison

Darrell Bryant

  • Full Member
  • ***
  • Posts: 104
  • Sed fugit interea fugit inreparabile tempus-Virgil
    • View Profile
Re: Arsdoc Print Date range not working
« Reply #1 on: September 26, 2017, 08:08:58 AM »
Hello Brian,
The -S parameter is used to limit which application group index tables are searched.  The -S means 'segment date'.  The -S will only limit which tables are searched, not which records are returned.  To set a date range for which records are returned, you must use the -i parameter to specify an SQL WHERE clause.

For example:

arsdoc print -v -h ondenu -i "WHERE ODDAT_ENDDATE BETWEEN '2016-01-01' AND '2016-12-31'" -f CHECKSTMTSV -P PRT01

ENDDATE is the AG field name.  ODDAT_ is added at the beginning so that you don't need to be concerned if the AG is using old or new style date.
#IBMi #iSeries #PDF #XML #400 Indexer #ASM

brianesr

  • Guest
Re: Arsdoc Print Date range not working
« Reply #2 on: October 13, 2017, 01:33:24 PM »
Thanks that got me closer   but now I think there is something wrong with the between code

I'm giving it this code  arsdoc print -v -h Qusrond -i "WHERE grp = '77' AND ODDAT_OPNDATE BETWEEN '01/02/2011' AND '01/28/2011'" -f AACR04 -P xxxx
and that is returning is everything for 01/  02 -28   for year 2007 to 2017   it's looks like it ignoring the year.
but when I run this code  arsdoc print -v -h Qusrond -i "WHERE grp = '77' AND ODDAT_OPNDATE= '01/02/13'" -f AACR04  -Pxxxxxx it always give me the correct file I'm looking for no mater what year I give it    so that works.

Any Ideas
I'm thinking of opening a PMR on this one

Thanks for your help.
Brian E. 

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2228
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: Arsdoc Print Date range not working
« Reply #3 on: October 13, 2017, 03:42:27 PM »
Thanks that got me closer   but now I think there is something wrong with the between code

I'm giving it this code  arsdoc print -v -h Qusrond -i "WHERE grp = '77' AND ODDAT_OPNDATE BETWEEN '01/02/2011' AND '01/28/2011'" -f AACR04 -P xxxx

You're entering the date format in MM/DD/YYYY, and Darrell's example used YYYY-MM-DD.  :)

-JD.
IBM CMOD Professional Services: http://TenaciousConsulting.com
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Education & Webinars:  https://CMOD.Training/

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR

brianesr

  • Guest
Re: Arsdoc Print Date range not working
« Reply #4 on: October 16, 2017, 10:26:19 AM »
When I use YYYY-MM-DD    it always errors.   in my PF it's in as MM/DD/YY

Thanks
Brianesr


Darrell Bryant

  • Full Member
  • ***
  • Posts: 104
  • Sed fugit interea fugit inreparabile tempus-Virgil
    • View Profile
Re: Arsdoc Print Date range not working
« Reply #5 on: October 17, 2017, 07:33:05 AM »
What error do you receive then using YYYY-MM-DD?  Per this support item, the YYYY-MM-DD format is required:
http://www.ibm.com/support/docview.wss?uid=swg27036188

Also, are you planning to upgrade to v7.2 or v7.3 soon?  We added the commands PRTDOCOND, QRYDOCOND, and RTVDOCOND to provide an easy to use front end to arsdoc print, arsdoc query, and arsdoc get.  The commands are available at v7.2 and higher at sever version 10.1.
#IBMi #iSeries #PDF #XML #400 Indexer #ASM

brianesr

  • Guest
Re: Arsdoc Print Date range not working
« Reply #6 on: October 17, 2017, 11:02:12 AM »
I'm running arcdoc query instead of print,  I get the same results just don't have to wait for the prints files to move to the out queue.

so here is what I get when I run the command

arsdoc query -v -h Qusrond  -fAACR04 -i "WHERE grp = '77' AND ODDAT_OPNDATE BETWEEN '2011-01-11' AND '2011/01/31'"
2017-10-17 12:54:13.231328: ARS6108I Starting arsdoc.  Version:  9.5.0.7
2017-10-17 12:54:13.234080: ARS6165I QRDARS/ARSDOC query -v -h Qusrond -f AACR04 -i WHERE grp = '77' AND ODDAT_OPNDATE BETWEEN '2
011-01-11' AND '2011/01/31'
2017-10-17 12:54:13.246816: ARS6062I Searching for folder 'AACR04' ...
2017-10-17 12:54:13.268048: ARS6084I Search successful
2017-10-17 12:54:13.268208: ARS6063I Searching for documents in 'AACR04' ...
2017-10-17 12:54:13.268320: ARS6117I Querying database with SQL string 'WHERE grp = '77' AND ODDAT_OPNDATE BETWEEN '2011-01-11' A
ND '2011/01/31''
2017-10-17 12:54:13.286504: ARS6085E Search unsuccessful
2017-10-17 12:54:13.286808: ARS6034W No hits were found that matched search criteria.
2017-10-17 12:54:13.288224: ARS6026I arsdoc completed.

and it's all because of the date range    if I do this

arsdoc query -v -h Qusrond  -fAACR04 -i "WHERE grp = '77' AND ODDAT_OPNDATE BETWEEN '01/02/11' AND '01/31/11'"
2017-10-17 12:57:33.549384: ARS6108I Starting arsdoc.  Version:  9.5.0.7
2017-10-17 12:57:33.552120: ARS6165I QRDARS/ARSDOC query -v -h Qusrond -f AACR04 -i WHERE grp = '77' AND ODDAT_OPNDATE BETWEEN '0
1/02/11' AND '01/31/11'
2017-10-17 12:57:33.564344: ARS6062I Searching for folder 'AACR04' ...
2017-10-17 12:57:33.642104: ARS6084I Search successful
2017-10-17 12:57:33.642272: ARS6063I Searching for documents in 'AACR04' ...
2017-10-17 12:57:33.642400: ARS6117I Querying database with SQL string 'WHERE grp = '77' AND ODDAT_OPNDATE BETWEEN '01/02/11' AND
'01/31/11''
2017-10-17 12:57:34.011496: ARS6084I Search successful

but I'm getting every thing for grp 77 for everyday listed but for every year from 07 to 17,   :) 


Darrell Bryant

  • Full Member
  • ***
  • Posts: 104
  • Sed fugit interea fugit inreparabile tempus-Virgil
    • View Profile
Re: Arsdoc Print Date range not working
« Reply #7 on: October 17, 2017, 02:14:49 PM »
I have not been able to recreate your problem on our 9.5.0.7 and 9.5.0.9 test systems.  I tested with AGs using old style dates, new style dates, and a combination of both.

arsdoc query -v -h ondenu2  -f CHECKSP -i "WHERE ACCTNO = '140060161' AND ODDAT_ENDDATE BETWEEN '2014-12-01' AND '2014-12-31'"
2017-10-17 17:00:44.207600: ARS6108I Starting arsdoc.  Version:  9.5.0.7
2017-10-17 17:00:44.211632: ARS6165I QRDARS/ARSDOC query -v -h ondenu2 -f CHECKSP -i WHERE ACCTNO = '140060161' AND ODDAT_ENDDATE
 BETWEEN '2014-12-01' AND '2014-12-31'
2017-10-17 17:00:44.239080: ARS6062I Searching for folder 'CHECKSP' ...
2017-10-17 17:00:44.589280: ARS6084I Search successful
2017-10-17 17:00:44.589576: ARS6063I Searching for documents in 'CHECKSP' ...
2017-10-17 17:00:44.589808: ARS6117I Querying database with SQL string 'WHERE ACCTNO = '140060161' AND ODDAT_ENDDATE BETWEEN '201
4-12-01' AND '2014-12-31''
2017-10-17 17:00:44.970520: ARS6084I Search successful                   
MYRTLE BEACH BLANKET     ,140060161,651-0086375,12/19/14,513324.960000,01
MYRTLE BEACH BLANKET     ,140060161,651-0086375,12/19/14,513324.960000,01
MYRTLE BEACH BLANKET     ,140060161,651-0086375,12/19/14,513324.960000,01
2017-10-17 17:00:44.973776: ARS6026I arsdoc completed.

If I use other date formats or separators I receive the message: ARS6007E The server failed during a search operation.
The system log contains message 13, DB Error: Syntax of date, time, or timestamp value not valid. -- SQLSTATE=22007, SQLCODE=-180, File=arsdoc.c, Line=2997

If you have the AG set to log Query messages, you can check the actual query that is run.  In my case, the after query message 226s (one per AG), contain this"

Application Group Query: Name(CHECKSP005) Agid(9838) Time(0.013) Hits(1) Count() SegStart() SegStop() Tables(1) Sql(WHERE ACCTNO = '140060161' AND ODDAT_ENDDATE BETWEEN '2014-12-01' AND '2014-12-31') SqlR() FullTextSearch() FullTextScore() ServerTextSearch() AnnColor() AnnText() OrderBy()
and
Application Group Query: Name(CHECKSP004) Agid(9835) Time(0.032) Hits(2) Count() SegStart() SegStop() Tables(2) Sql(WHERE ACCTNO = '140060161' AND ODDAT_ENDDATE BETWEEN '2014-12-01' AND '2014-12-31') SqlR() FullTextSearch() FullTextScore() ServerTextSearch() AnnColor() AnnText() OrderBy()

The messages show the query used, the number of tables searched, and the number of hits returned.
#IBMi #iSeries #PDF #XML #400 Indexer #ASM

brianesr

  • Guest
Re: Arsdoc Print Date range not working
« Reply #8 on: October 26, 2017, 12:59:58 PM »
Ok,  so I have the problem fixed.   the feild I was trying to do a between on  the OPNDATE there is no between on the GUI,  the only between is in the Report date and so when I did This
arsdoc query  -f AACR04 -v -h Qusrond -i "WHERE GRP ='77'and ODDAT_RDATE between '2015/01/01' and '2015/01/31'"   I got the results I was looking for on the query.

Thanks for all your help


Brianesr.
« Last Edit: October 26, 2017, 01:09:59 PM by brianesr »