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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Issue writing converted alpha-date to Oracle table

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Issue writing converted alpha-date to Oracle table
 Login/Join
 
Member
posted
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
 
Posts: 5 | Registered: March 31, 2006Report This Post
Expert
posted Hide Post
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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: March 31, 2006Report This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: March 31, 2006Report This Post
Expert
posted Hide Post
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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: March 31, 2006Report 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     Issue writing converted alpha-date to Oracle table

Copyright © 1996-2020 Information Builders