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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Query date field for non-existant date values

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Query date field for non-existant date values
 Login/Join
 
Member
posted
I have a client with a report that is suppose to indicate which training rooms are not booked on specific dates. How would you query for a date value that does not exist in a field?

Thank you,
Morgan
 
Posts: 21 | Location: NC, USA | Registered: June 12, 2006Report This Post
Virtuoso
posted Hide Post
WHERE DATEFIELD EQ ' ';


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Member
posted Hide Post
I don't think that will work because I want to know what dates the room is not booked. These values don't exist in the field.
 
Posts: 21 | Location: NC, USA | Registered: June 12, 2006Report This Post
Virtuoso
posted Hide Post
What is your data base? DB2 sets a default on date fields. Our Student system has a default date of 01/01/1001, fills the DB2 dates with that, I believe DB2 has a default date of 01/01/0001. So your data base might also, then you look for rooms with the 'default date in them'. Of course that only finds not booked at all.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Platinum Member
posted Hide Post
One approach to this problem is to create a file of the universe of the dates possible (business days maybe?). Join the date in this file to all the dates that currently have meetings and then test for your missing dates on the cross referenced (child) file.

Good luck.

et


FOCUS 7.6 MVS PDF,HTML,EXCEL
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report This Post
Member
posted Hide Post
Leah, it is an Oracle database.

Thanks,
Morgan
 
Posts: 21 | Location: NC, USA | Registered: June 12, 2006Report This Post
Virtuoso
posted Hide Post
Hard to give a specific answer without some sort of data layout, but here's something that may work. If you have a file with all rooms and join it to the file with room schedules using SET ALL=ON and then do a where test on the schedule file like WHERE SCHEDULE.ROOMNBR EQ MISSING and WHERE SCHEDULE.DATE EQ mm/dd/yyyy you should get a list of rooms that are NOT scheduled for the given data. I believe I recall seeing an example of this in the manual where it was showing all employees that had not attended a training class. It was talking about MISSING field values/segments.


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, 2007Report This Post
Virtuoso
posted Hide Post
For your oracle data base, does the software that sits on top of it have some default date used?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Member
posted Hide Post
Leah, I don't think there is a default date, but there has got to be a way to do this within WebFocus. I say that because you can do lots of date math without field values.

Thanks,
Morgan
 
Posts: 21 | Location: NC, USA | Registered: June 12, 2006Report This Post
Virtuoso
posted Hide Post
Would this work.

WHERE DATEFIELD IS-MISSING


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Virtuoso
posted Hide Post
Without knowing how the data is being stored, missing may or may not be what you need, however have you considered doing a quick check to get the minimum value in the date field in question? Might give you the default. What other parameters if any are you using?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Master
posted Hide Post
I agree with ET's approach.


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
Member
posted Hide Post
ET - I'm new at WebFocus - how would you create the file in WebFocus. Or, would I need to create the file on the server to read it??
 
Posts: 1 | Registered: July 02, 2007Report This Post
Virtuoso
posted Hide Post
At the end of your Fex with the data you want you put:

ON TABLE HOLD AS WHATEVER FORMAT FOCUS

TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE HOLD AS WHATEVER FORMAT FOCUS
END


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Virtuoso
posted Hide Post
create a small text file with let's say the numbers 1 till 90 and save that file in your baseapp directory

now create a master to read that file (PERIOD.MAS)
The number field is "NUMBER"

DEFINE FILE PERIOD
DATEFIELD/YYMD=TODAY+NUMBER;
END

Now you can table that file and hold the data in a focus file and it has the sequence of the coming 3 months.
With some smart extra defines you can list only the working days.
Join the focus file to your table and do what Darin suggested.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
Here is an example to create a file of dates using the car file. You can use any file as long as it has more than the number of days you want to check and use a recordlimit to get the number of dates you need and hold this file.

If I understand your problem correctly you would then need to build a file that has each meeting room and all the possible dates.

room1 date1
room1 date2
...
room9 date1
etc.

This can be done by adding a dummy blank field to the hold file generated with all the possible dates. Then create a file containing each distinct meeting room and another dummy blank field. Join the blank in the meeting room file to all blank in the date file. Now create the above described hold file sorted in meeting room and date order.

Extract a file from your data source that has meeting room and dates used. Join the first file which has every meeting room and date combination possible to the second file that only has the rooms with meeting dates. Print the room/date from the first file where the room/date from the second file is missing should give you what you need.

Good luck

et



DEFINE FILE CAR
CNTR/I9 WITH CAR= LAST CNTR + 1;
STARTDT/MDY WITH CAR='070307';
ALLDATE/MDY=IF CNTR EQ 1 THEN STARTDT ELSE LAST ALLDATE + 1;
END
TABLEF FILE CAR
PRINT CAR ALLDATE
END
CAR ALLDATE
--- -------
JAGUAR 07/03/07
JENSEN 07/04/07
TRIUMPH 07/05/07
PEUGEOT 07/06/07
ALFA ROMEO 07/07/07
MASERATI 07/08/07
DATSUN 07/09/07
TOYOTA 07/10/07
AUDI 07/11/07
BMW 07/12/07


FOCUS 7.6 MVS PDF,HTML,EXCEL
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report 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     Query date field for non-existant date values

Copyright © 1996-2020 Information Builders