Good Morning -

I'm experiencing an odd occurance and wonder if anyone here can help shed light on it. (I'm fairly new to FOCUS so go easy on me!)

I have an source Alpha field [SRC_ALPHA_DATE/A11] which contains a natural date (format of 01-JAN-2006 to be exact).

The target Oracle field is mapped as RDATE/HYYMDS

I have tried transforming this directly using HINPUT:
RDATE/HYYMDS=HINPUT(11, SRC_ALPHA_DATE , 8, RDATE)
I can see the sample data correctly but when I run, I receive an error. (It appears that 7.1.1 may have database error translation issues as it's coming back as an ORA-08207)

If I do a test and use current date/time:
RDATE/HYYMDS=HGETC(17, RDATE)
the data loads fine.

So I've tried another method - and assigned the HINPUT to a virtual column first and then attempted to reassign this column to the database RDATE.
VIRTCOL/HYYMDS=HINPUT(11, SRC_ALPHA_DATE , 8, RDATE)
RDATE/HYYMDS=VIRTCOL
While I can see that the data is visually in the exact same format as the HGETC (except the time fields are zeros in the HINPUT), this results in the same error as the direct assignment.

Any ideas?

Thanks! - Justin

WebFocus/DataMigrator 7.1.1

Have you tried putting a time in the define/compute?

Something like this:

SRC_ALPHA_DATE1/A20 = SRC_ALPHA_DATE | ' 01:01:01';
RDATE/HYYMDS=HINPUT(20, SRC_ALPHA_DATE1 , 17, RDATE);

e.g.:

DEFINE FILE CAR
SRC_ALPHA_DATE/A11 = '23-JAN-2006';
SRC_ALPHA_DATE1/A20 = SRC_ALPHA_DATE | ' 01:01:01';
RDATE/HYYMDS=HINPUT(20, SRC_ALPHA_DATE1 , 17, RDATE);
RDATE1/HYYMDS=HGETC(17, RDATE)
END
TABLE FILE CAR
PRINT
SRC_ALPHA_DATE
SRC_ALPHA_DATE1
RDATE
RDATE1
BY COUNTRY
END

Hi Francis - thanks for your response. I just realized I may have posted in the wrong forum as I'm trying to write my output to Oracle instead of a report.

The challenge is that the test transform works fine (your suggestion and the way I was trying differ by just the 01010100 vs 000000000 at the end of the date)

When I go to insert the data to the database I get
(FOC1400) SQLCODE IS 8207 (HEX: 0000200F)
: ORA-08207: ora_addr: cannot open address file
(FOC1416) EXECUTE ERROR : STAGE_RECEIPT
-- stats for source file

And I don't really believe that it is a database issue (other than somehow a formatting of the string to be written).

Thanks again - Justin

Another question - although this format is set to HYYMDS - I see that it allocates space for the milliseconds so my date of 23-FEB-2005 used in
RDATE/HYYMDS=HINPUT(11, SRC_ALPHA_DATE , 8, RDATE)

RDATE shows as 20050223000000000 - shouldn't that be the HYYMDs format? Why doesn't it use only 6 characters to store the time? (20050223000000)

Is this a bug with the Data Management Console I'm using or is this typical?

Thanks!

Local/Test WebFocus/DataMigrator 7.1.1

I don't think I can answer the question about the milliseconds. Perhaps an alpha field should be used to insert into the Oracle table.

RDATE2 is the date in an alpha format.
RDATE3 is the date with the milliseconds appended.
RDATE4 has the / converted to -.

One of these should work!


DEFINE FILE CAR
SRC_ALPHA_DATE/A11 = '23-JAN-2006';
RDATE1/HYYMDS=HINPUT(11, SRC_ALPHA_DATE , 17, RDATE1);
RDATE2/A20 = HCNVRT(RDATE1, '(HYYMDS)', 20, 'A20');
RDATE3/A24 = RDATE2 | ' 000';
RDATE4/A24 = EDIT(RDATE2,'9999$-99$-99') | ' 000';
END
TABLE FILE CAR
PRINT
SRC_ALPHA_DATE
RDATE1
RDATE2
RDATE3
RDATE4
BY COUNTRY
END

I wanted to thank you for your assistance and update you on the resolution.

Apparently I was running up against a bug in the 7.1.1 version. Any "natural date" formatted date using OCT was resulting in all zeros at insertion time (which isn't a vaild date). Interestingly enough Oct and oct didn't cause the same problem.

I worked with support and found that this was resolved in 7.1.3, which I have upgraded to, and tested successfully.

Thanks again - Justin