Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] date/time field error

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] date/time field error
 Login/Join
 
Tomsweb
Master
posted
I have coded a routine fex to capture water main breaks/leaks which occurred between midnight and noon today.

I created a date/time field in a define which I want to compare against the RECDATE field (A8YYMD) in the master file. I am having a problem with the statement:

quote:

WHERE RECDTE FROM DATEH1 TO DATEH2 ;

as I get an error message:

quote:

COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED


Can anyone help me?

Thanks.

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


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Prarie
Virtuoso
posted Hide Post
what is the format of DATEH1 and DATEH2?
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Tomsweb
Master
posted Hide Post
Here is my code:
  
-SET &ECHO=ALL;                

DEFINE FILE MMDB1        
TDATE/YYMD WITH WO = &YYMD; 
IDATE/I8YYMD = EDIT(RECDTE); 
DATEA/A40 = 'JANUARY 03 2013 12:00:00AM';  DATEB/A40 = 'JANUARY 03 2013 11:59:59AM'; 
DATEH1/HDMYYS = HINPUT(40,DATEA,8,'HDMYYS');  
DATEH2/HDMYYS = HINPUT(40,DATEB,8,'HDMYYS');
WORKTYPE/A12 = DECODE WDC ('211' 'BREAKS' '212' 'BREAKS' '213' 'BREAKS'
 '325' 'LEAKS' '328' 'LEAKS' ELSE 'NONE'); 
END 

TABLE FILE MMDB1
COUNT WO 
BY WORKTYPE  
WHERE STATUS NE 'X'; 
WHERE WDC EQ '211' OR '212' OR '213' OR '325' OR '328' ; 
WHERE RECDTE FROM DATEH1 TO DATEH2; 
IF RECORDLIMIT EQ 10 
END 
-RUN

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


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Prarie
Virtuoso
posted Hide Post
quote:
A8YYMD


All the dates for comparison are going to have to be the same format
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Tomsweb
Master
posted Hide Post
Can I maintain A8YYMD w the time stamp?


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
RSquared
Guru
posted Hide Post
We use the DT function.

/*
WHERE RECDTE GE DT(&Today2 00:00:00.000) AND RECDTE LE DT(&Today2 11:59:59.999);
\*


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
jgelona
Master
posted Hide Post
First you are getting the error because RECDTE is not a datetime field.

Second, why would you want to compare a plain date field with a specific time range? You need a matching time for RECDTE to make a valid comparison. I say that because if you convert RECDTE to a datetime field, the time component will be midnight (00:00:00.000) and every RECDTE for a given day will pass.

If you have a time component (i.e. RECTIME) there are various ways to take RECDTE and RECTIME and make one field you can use in your WHERE statement.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Tomsweb
Master
posted Hide Post
I need to ask if there is a time component field in the databasse.


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Tomsweb
Master
posted Hide Post
I have learned that there are two fields in the master file which
track the time when orders are entered into the system. These fields
are RECTIME/A4 (ie., 1200 for noon), and RECAP (A for AM; P for PM).

Since you must concatenate these fields to distinguish 4am from 4pm, I thought
that I would convert all times greater than 12 to military time. I have done so in
this code, but the WHERE statement is just ignored when I run this fex. 

 
DEFINE FILE MMDB1                                             
TDATE/YYMD = &YYMD;                                           
IDATE/I8YYMD = EDIT(RECDTE);                                  
DYMD/YMD = IDATE;                                             
DYYMD/YYMD = DYMD;                                            
AGEDIF/YYMD = DATEADD(TDATE, 'D', -30);                       
RCTM/A5 = RECTIME | RECAP;                                    
HRI/I4 = EDIT(RECTIME);                                       
MRECTM/I4 = IF RECAP EQ 'P' THEN (HRI + 1200) ELSE HRI;       
ARECTM/A4 = EDIT(MRECTM);                                     
NDTTM/A12 = RECDTE | ARECTM;                                  
NHDATE/HYYMDS = HINPUT(12,NDTTM,8,'HYYMDS');                  
END                                                           
                                                              
TABLE FILE MMDB1                                              
PRINT                                                         
RECDTE                                                        
RECTIME                                                       
NHDATE   
BY ASSIGNEE                                     
WHERE DYYMD GE AGEDIF;
WHERE NHDATE LE DT(2013/01/03 11:59:59) ; 
-*WHERE NHDATE GE DT(2013-01-01 00:00:00)       
-*  AND NHDATE LE DT(2013-01-01 11:59:59) ;     
IF RECORDLIMIT EQ 15                            
-* TABLE SAVE                                   
END                                             
-RUN

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


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
jgelona
Master
posted Hide Post
The first thing I see wrong is the computation for MRECTM. It should be:
MRECTM/I4=IF RECAP EQ 'P' AND HRI LT 1200 THEN HRI+1200 ELSE HRI;


The reason is you do not want to add 12 hours to 12:00 pm (noon) through 12:59 pm. You will get an invalid time.

Second I suggest you remove the where clauses and just print 10-20 rows to make sure your conversions are working correctly.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Tomsweb
Master
posted Hide Post
quote:
I have made the changes you suggested and tested it. The only problem I see here
is I will have 12:12 at 12 minutes after midnight and 12:12 at 12 minutes after noon.

However, I do get 13:12 in the afternoon and a 1:12 in the early morning.



quote:
The first thing I see wrong is the computation for MRECTM. It should be:

MRECTM/I4=IF RECAP EQ 'P' AND HRI LT 1200 THEN HRI+1200 ELSE HRI;

The reason is you do not want to add 12 hours to 12:00 pm (noon) through 12:59 pm. You will get an invalid time.


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
jgelona
Master
posted Hide Post
Well if I understand you, you are saying that you could have RECTIME with a value of 1212 and RECAP could have a value of 'A' which you would want to become 00:12 am. Then your code would look like this:
MRECTM/I4=IF RECAP EQ 'P' AND HRI LT 1200 THEN HRI+1200 ELSE
          IF RECAP EQ 'A' AND HRI GE 1200 THEN HRI-1200 ELSE HRI;


RECTIME with values of 0100 through 1159 and RECAP with a value of 'P' need 12 hours added.
RECTIME with values of 1200 through 1259 and RECAP with a value of 'A' need to have 12 hours substracted;
All of values of RECTIME and RECAP need no adjustment. These are:
RECTIME with values of 0100 through 1159 with RECAP being 'A' and RECTIME with values of 1200 through 1259 with RECAP being 'P'


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Tomsweb
Master
posted Hide Post
Thank you jgelona for helping me get "over the hump".


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] date/time field error

Copyright © 1996-2020 Information Builders