Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Efficient handling of indexes with date/time fields
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Efficient handling of indexes with date/time fields
 Login/Join
 
Member
posted
Many of our ERP based reports are based on transaction date ranges. We are struggling with the best way to handle these and the advice we have been given doesn't seem to pass the date range to the database engine. All our dates are traditional DB date-time stamps. Our users want to enter dates (in prompts) in MDYY format. I have included code for a sample query.

Report:
TABLE FILE DLYSTKLV
PRINT
CONO
PCLS35
PNUM
DLYSTKL_Year
DLYSTKL_Month
DLYSTKL_Date
WHERE CONO EQ 'MD';
WHERE PCLS35 EQ 'LS' OR 'PA' OR 'MS';
WHERE ( DLYSTKL_Date GE &Start_Date ) AND ( DLYSTKL_Date LE &End_Date );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET WEBVIEWER ON
ON TABLE SET WEBVIEWTARG OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

Generated SQL:
SELECT
T1."DTSL",
T1."CONO",
T1."PNUM",
T2."PCLS35"
FROM
DataMart.dbo.DLYSTKLV T1,
DataMart.dbo.INP35 T2
WHERE
(T2."CONO35" = T1."CONO") AND
(T2."PNUM35" = T1."PNUM") AND
(T1."CONO" = 'MD') AND
(T2."PCLS35" IN('LS', 'PA', 'MS'));
RECORDS IN TABLE= 0 LINES= 0

Daily stock level file:
FILENAME=dlystklv, SUFFIX=SQLMSS , $
SEGMENT=DLYSTKLV, SEGTYPE=S0, $
FIELDNAME=DTSL, ALIAS=DTSL, USAGE=HYYMDs, ACTUAL=HYYMDs, FIELDTYPE=R, $
FIELDNAME=CONO, ALIAS=CONO, USAGE=A2, ACTUAL=A2, FIELDTYPE=R, $
FIELDNAME=PNUM, ALIAS=PNUM, USAGE=A15, ACTUAL=A15, FIELDTYPE=R, $
FIELDNAME=STRC, ALIAS=STRC, USAGE=A2, ACTUAL=A2, FIELDTYPE=R, $
FIELDNAME=LOCN, ALIAS=LOCN, USAGE=A15, ACTUAL=A15, FIELDTYPE=R, $
FIELDNAME=SPHY, ALIAS=SPHY, USAGE=P15.3, ACTUAL=P7, FIELDTYPE=R, $
FIELDNAME=SALC, ALIAS=SALC, USAGE=P15.3, ACTUAL=P7, FIELDTYPE=R, $
FIELDNAME=SAVL, ALIAS=SAVL, USAGE=P15.3, ACTUAL=P7, FIELDTYPE=R, $
FIELDNAME=SFRZ, ALIAS=SFRZ, USAGE=P15.3, ACTUAL=P7, FIELDTYPE=R, $
DEFINE DLYSTKL_Date/MDYY=HDATE(DTSL, 'MDYY'); $
DEFINE DLYSTKL_Year/I4=HPART(DTSL, 'YEAR', 'I4'); $
DEFINE DLYSTKL_Month/I2=HPART(DTSL, 'MONTH', 'I2'); $

Item master:
Partial view:
FILENAME=inp35, SUFFIX=SQLMSS , $
SEGMENT=INP35, SEGTYPE=S0, $
FIELDNAME=CONO35, ALIAS=CONO35, USAGE=A2, ACTUAL=A2, FIELDTYPE=R, $
FIELDNAME=PNUM35, ALIAS=PNUM35, USAGE=A15, ACTUAL=A15, FIELDTYPE=R,
TITLE='Item#', $
FIELDNAME=ACTF35, ALIAS=ACTF35, USAGE=A1, ACTUAL=A1, FIELDTYPE=R, $
FIELDNAME=PDES35, ALIAS=PDES35, USAGE=A36, ACTUAL=A36, FIELDTYPE=R,
TITLE='Description', $

Join:
JOIN
INNER DLYSTKLV.DLYSTKLV.CONO AND DLYSTKLV.DLYSTKLV.PNUM IN DLYSTKLV TO UNIQUE
INP35.INP35.CONO35 AND INP35.INP35.PNUM35 IN INP35 TAG J3 AS J3
END

We are on version 7.7.01 using SQL Server 2005

This message has been edited. Last edited by: Kerry,
 
Posts: 4 | Location: Louisville, KY | Registered: June 23, 2010Reply With QuoteReport This Post
Member
posted Hide Post
Since my post has several views and no response, I'll ask a more specific question:
If you define a field that is derived from an indexed date field and you use the defined date field in your report should the SQL statement then use the actual date field referenced?
 
Posts: 4 | Location: Louisville, KY | Registered: June 23, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Dan,

Be using dialogue manager to create timestamp field from your user input and be testing that value on real columns and not defined. I see in FP that this being not good.

I have be seeing DT(&dateinput) also but not be trying it.

Kofi


Client Server 8.1.05: Apache; Tomcat;Windows Server 2012
Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
 
Posts: 103 | Registered: April 06, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
Why are you DEFINEing the Date fields, and are they DLYSTKL_Date, DLYSTKL_Date, etc?

You can filter on the Date-Time Fields with something like this:

  
WHERE AS_OF_TMS GE DT(&ENDDATE 00:00:00.000) AND AS_OF_TMS LE DT(&ENDDATE 23:59:59.999)


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, 2005Reply With QuoteReport This Post
Master
posted Hide Post
Dan,

Can't speak for SQL Server, but in Oracle, I get around this a couple of ways.

First, instead of using a DEFINE, I put 2 fields in the .mas, something like this.
FIELDNAME=DTSL, ALIAS=DTSL, USAGE=HYYMDs, ACTUAL=HYYMDs, FIELDTYPE=R, $
FIELDNAME=DTSLDT, ALIAS=DTSL, USAGE=MDYY, ACTUAL=DATE, FIELDTYPE=R, $


Then I will add 1 day to the &END_DATE and my where clause will look something like this:
WHERE ( DTSLDT GE '&Start_Date' ) AND ( DTSLDT LT '&End_Date' );


This will generate the proper test for a datetime stamp. Since time is not part of &Start_Date or &End_Date, Oracle will make the time midnight (00:00:00.00000).


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 919 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Efficient handling of indexes with date/time fields

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.