Date Parameter producing No HTML Output

quote:
DATEMOV

Are you on MVS or VMS/..

That appears to be where DATEMOV can be used.

Leah

Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004

Ignored post by Leah posted

TXFB_INS

Member

posted

quote:
Originally posted by Leah:
quote:
DATEMOV

Are you on MVS or VMS/..

That appears to be where DATEMOV can be used.

It is on MVS,

That appears to be where DATEMOV can be used I am not sure what this means, would you mind explaining.

Thanks

Posts: 5 | Registered: June 07, 2007

Ignored post by TXFB_INS posted

Francis Mariani

Expert

posted

Have you traced the SQL generated by your request? Have you run SQL independently of WebFOCUS to determine if there are actually rows that satisfy your request?

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Virtuoso

posted

First, you can't use DEFINE to set a parameter (unless you're talking about something other than an & variable) to be used in a WHERE statement. Also, DATEMOV doesn't change any format, just moves the date to a specific date. It must also use a full component DATE field (format MDYY, MDYY, etc.-- formats like YYM or A8 won't work). Last, if you are using a defined value in a WHERE and you have very much data, you're asking for trouble. It can't do the WHERE until after the record is read, so it's going to basically to a table scan. If you can show some code, we can probably give you a more specific answer.

Regards,

Darin

In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat

Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007

Ignored post by Darin Lee posted

TXFB_INS

Member

posted

Here is the prompt for the date parameter

(CLM_ACC_DT) the format is (YYMD)

QRT_RUN/QYY=DATEMOV(CLM_ACC_DT, 'BOQ');

( QRT_RUN GE '&FROM_ENTER_DATE.FROM Q# YYYY.' ) AND ( QRT_RUN LE '&TO_ENTER_DATE.TO Q# YYYY.' );

Thanks for the input it has answered some of the troubles.

What I need is know what to do or where to look to learn to do it;
I need to have a "DATE PARAMETER" where the user enters the desired Quarter (Q1-Q4) and year as a start point and end point. (CLM_ACC_DT) the format is (YYMD) is the date field that will be used.

Thanks

This message has been edited. Last edited by: TXFB_INS,

Posts: 5 | Registered: June 07, 2007

Ignored post by TXFB_INS posted

Virtuoso

posted

Here's an example from EMPDATA:

  
-DEFAULT &BEGQY='Q4 1989'
-DEFAULT &ENDQY='Q4 1990'
DEFINE FILE EMPDATA
QRT_RUN/QYY=DATEMOV(HIREDATE,'BOQ');
END
TABLE FILE EMPDATA
PRINT *
QRT_RUN
WHERE  QRT_RUN GE '&BEGQY'; 
WHERE  QRT_RUN LE '&ENDQY';
END

The user can enter quarter and year (must be in format Qn yyyy) for beginning and end. I would suggest using two separate dropdown boxes and then appending those values in your code:
-SET &BEGQY=&BEGQ|' '|&BEGY ;

As I mentioned in the last post, this is not going to be very efficient - especially if you have a huge data set - because your only selection criteria is on a DEFINEd field. This is NOT passed in the SQL to the DBMS so it's going to do a SELECT *.

Regards,

Darin

Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007

Ignored post by Darin Lee posted

Virtuoso

posted

Come to think of it, you don't even need to do a DATEMOV. You can just do QRT_RUN/QYY=HIREDATE;
or in your case QRT_RUN/QYY=CLM_ACC_DT;

Regards,

Darin

Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007

Ignored post by Darin Lee posted

TXFB_INS

Member

posted

Darin thanks again for the advice.

What would you suggest I do in order to complete the task and do it properly

Thanks

Posts: 5 | Registered: June 07, 2007

Ignored post by TXFB_INS posted

Virtuoso

posted

If you're talking about the selection criteria, here are a couple suggestions.

If you have a Year in the native table (that corresponds to the year in CLM_ACC_DT), create a WHERE for the year as well as the quarter and year. This will get you a little better efficiency. If not, (or for maximum efficiency) take the quarters and years entered by the user. Using dialogue manager -SET commands and DATEMOV and CHGDAT functions, calculate the first day of the beginning quarter and the last day of the ending quarter as & variables. Then use these & variables in your WHERE statement:

WHERE CLM_ACC_DT GE '&BEGDT.EVAL' AND CLM_ACCT_DT LE '&ENDDT.EVAL';

Regards,

Darin

Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007

Ignored post by Darin Lee posted