OnDemand User Group
Support Forums => iSeries => Topic started by: brianesr 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
-
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.
-
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.
-
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.
-
When I use YYYY-MM-DD it always errors. in my PF it's in as MM/DD/YY
Thanks
Brianesr
-
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.
-
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, :)
-
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.
-
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.