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

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.

Hi Viral,

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

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.

Cheers,

Kerry

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.

Viral

you should use this function

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

to change the I8YYMD to an YYMD format.