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  iWay Software Product Forum on Focal Point    DateTime Filter not working
Go
New
Search
Notify
Tools
Reply
  
DateTime Filter not working
 Login/Join
 
Platinum Member
posted
Hello,

I am trying to filter the data using date.
First I get current date and then get 35 days back date. Then on SQL statement use filter where use the source date = defined date and it pulls up no data and source has data for that date.
Here is thing I am doing on source transformation.
DEFINE FILTERDT/I8YYMD=AYMD ( &YYMD , -36, 'I8YYMD'); $
DEFINE CUTOFFDT/I8YYMD=CUTOFFDATE; $

SQL PREPARE SQLIN FROM
SELECT
T1.CUTOFFDATE ,
T1.LINECODE ,
T1.LOTNUMBER ,
T1.CURDT ,
T1.FINISHDATECALENDAR ,
T1.FINISHDATEFACTORY ,
T1.PRECUTOFFDT ,
T1.FILTERDT ,
T1.CUTOFFDT
FROM
TARGET01 T1
WHERE
T1.CUTOFFDT = T1.FILTERDT
END

When I see the test transformation I can see the CUTOFFDT and FILTERDT works fine.
My source is oracle table and CUTOFFDATE is in YYMD format. Any idea why is not working will be great.. I am just new to his product.

Here is the summary what planning to do. I need to check the data if it 35 days old then delete data from target and then gets current data ( which will be one day worth of data with the cutoffdate will be today and inserts into same target where we deleted. So I will be doing in two data flow, one delete and other with insert. Call the insert dataflow from the delete one. Any suggestion also will be help.

Thanks in advance for your inputs.

Target is Oracle DB. and Source is also Oracle DB. DMC 7.1.3


WF 7.6.2/ OS WIN2003.
DM 7.6.2
 
Posts: 103 | Registered: September 08, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Viral, I see where you say you tested the source transforms. Did you also test the SQL itself? If you don't know how to do this, right click on the SQL box in the middle of the screen, click on SQL Statement, then near the upper right hand side of the screen is a button that says 'SQL'. Click on that. If that also returns no rows, then you'll need to check the table directly. And by that I mean using TOAD or something equivalent to see if there truly is data out there for the date specified. And for that, hardcode in the date. Like CUTOFFDATE = 20070726 (That's 36 days before today). If you get no rows returned, then there isn't data. If you do get rows returned, then I agree. Something is amiss and I'd recommend opening a case with IBI on this one.

As far as what you're ultimately trying to do, I believe your plan is a good one.

Good Luck.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Hi Viral,

Has this issue been resolved? Many thanks to Jessica's input. Smiler

I asked our internal consultants and here is the suggestion: your define sets the fields to I8YYMD then does a comparison. Your database field is a YYMD so we are not sure setting to I8YYMD is appropriate. You may want to try YYMD in your define.


Hope this helps. Big Grin

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1950 | Location: New York | Registered: November 16, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hello All,

Thanks for your reply,

I tested SQL statement and it returned no data. Also tried using hardcode value like cutoffdate = '20070725' and it returned data. I have already open the case with IBI.
I tried to set the YYMD in defined but the defined field shows zero no date. Hence I convert the DB field to I8YYMD and checked data it shows the correct date then compare with my defined field and it returned no data.
How can I do the get date (YYMD) format from current date - 35 days? any idea I can try..

Thanks for your suggestions..and will let you know any outcome from IBI case.


WF 7.6.2/ OS WIN2003.
DM 7.6.2
 
Posts: 103 | Registered: September 08, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Viral

you should use this function

NEW_HIRE_DATE/YYMD = DATECVT(HIRE_DATE, 'I8YYMD', 'YYMD');

to change the I8YYMD to an YYMD format.




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, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    DateTime Filter not working

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