DSXchange

I've used the first method Ken suggests with good results for Left, Right, and Inner joins (don't even think about the Merge stage; it works, but is a real PITA to build and maintain). How do you wish to handle f1 and f2 records with the same keys? Do you want survivorship, or do you want to preserv...

I think the date string format you have is almost suitable for direct selection: [SAMPLE(1900-01-01)] If you simply remove the dashes from the string, and convert the result to a number, then max() works. ie, '1900-01-01' ==> 19000101 You can lexically remove the dashes, or do substrings and rembine...

These date formats are commonly used, for example in Peoplesoft (oops, I mean Oracle), the format is yyyddd, where yyy is the number of years since 1900, and ddd is the day of the year. So today, August 2, 2005 is 105214. For years before 1900, you are probably SOL. Note that years before 2000 will ...

Hehe, Mike and I have conversed at length on this subject. But in general, for records >> 4 bytes in size, the chances of a CRC collision between arbitrary records are greater than 1 in 4,294,967,296. And the larger your record set, the greater the chance of a collision. The math *is* complex, but i...

In the original post, the input date is 31-DEC-2004 (4 digit year), but the Oracle to_date format string is 'DD-MON-YY' (2 digit year). This will certainly yield an error. Meanwhile, how is 20041231 not a valid date? (year=2004, month=12, day=31) Carter Dear Team, While inserting the date from file ...

I assume that you are using CRC for performance reasons. You should always be able to re-generate the set of current CRC values from the data stored in your safe persistent target. In fact, for a production application, I consider a dedicated, tested, and versioned process to do that as an absolute ...

Yah, Anything you can do to add a little more info to the error messages helps. Cover the common stuff in the runbooks, with specific advice on how to interpret the error messages. Anything more complex will require someone to examine the tea leaves with Director. You can fine tune it with experienc...

Here's one useful metric: The level of Autosys granularity should equal the set of operations that you don't need to be paged at 3:00 AM to resolve. In other words, if you can code it so that an operator can handle scheduling, initiation, aborts and restarts through Autosys instead of Datastage, do ...

It would help if you posted some more info, like the hash file structure.
How do you know that the record is missing? What are the keys? Is a key-combo that was there before now gone, or are non-key values associated with an existing key-combo now different?

Carter

If you are dealing with a base year of 1900 (as in some PeopleSoft implementations), then you can do something like this: FUNCTION PsDateToY4MD (Arg1) psdate = Arg1 * In the PS date format, 'YYY' represents years since 1900, 'JJJ' is day of year 001-366 * Add 1900000 to the input number to yield 'YY...

I have used the Merge stage on several projects to perform Left, Right, and Inner joins on sets of data. The stage uses named files as sources, dynamically creates hashes under the covers to do the merge/joins, and then outputs data streams based on your keys and join definitions. In my experience, ...

Or,
since your target is Oracle, let the DB engine do the dirty work.
AFAIK, the date format of your example XML string is already the Oracle default, but you should use this date format in the Sql to explicitly convert the string to a date.

to_date(yourXmlDate, 'MM/DD/YYYY HH:MI:SS AM')

Carter

The Arg1 format has to be the same, unless you want to pass in a format string as another arg, or put some parsing logic inside the function. You can do it as a function, or inline as suggested above by pre-computing the secsindays and iconv('1 jan 1970','D'): So if timestamp = '19-APR-2005 00:00:00...

This works. Remember that UNIX time starts at 1-JAN-1970 00:00:00 Function UnixTime(Arg1) timepiece = Arg1[13,8] datepiece = Arg1[1,11] * '19-APR-2005 00:00:00' to 1113868800 secsinday = 60 * 60 * 24 Ans = (iconv(datepiece,"D-") - iconv("1 jan 1970","D")) * secsinday + ...

You can certainly compute the field lengths, and place the prefix as required. But maybe you shouldn't be using varchar() types. What's the target really look like? If you are loading into a known fixed size target, and you can guarantee that your source data is within that size, then define the fie...