 |
|||||
|
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
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. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| Join with different format | Login/Join |
| Member |
Here's the thing, I have two table file X and Y. On X I got a date field with the format MMDDYY (16/4/08 If I'm not mistaken) on Y I got a date field format varcahar (160408) and I need to join the two. Can it be done? Or is there any alternative for this? Thanks and it's nice meeting you all, this is my third report in WebFocus now and so far I've already got so much help from this forum, thanks again. 7.1.3 on XP into HTML, Excel, PDF | ||
|
| Virtuoso |
Juuta, The best advice would be to do a define based join. First issue the join and follow this with a define that will create a field with the same format as the date field in the target file and give this field the value of the date field in the host file. That should work. So: join datefield with xdb_field in x to ydb_field in x as j1 define file x datefield/mdy = value of ydb_field end You may have to manipulate the x-dbfield to convert it to the correct format for the y-database. Hope this helps ... GamP
| |||||||||
| ||||||||||
| Master |
Yuuta, GamP has given you the right information, but his/her syntax is wrong. When you do a define based join you change the format of the field on the host to to format of the field on the joined file. Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes | |||
|
| Expert |
If the files are relational, I would TABLE them out separately with associated screening conditions for each file and adjusting the formats as required. Make the target file hold file a FORMAT FOCUS INDEX datefield, then join the host hold file (sorted by its date field) to it. A define-based join against a relational table will turn off optimization and the join will be done by WebFOCUS. It is better to take control of the process and do it yourself. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
| Member |
Well, that's confusing  Sorry, I'm still pretty new to all this.Okay, so I stumble upon another problem. The datefield format for one of the table is actualy HYYMDS (2008/04/18 09:55:23) and the other one is A8 (20080418), any ideas? I'm banging my head trying to convert between the two. So far I've tried DATECVT, EDIT, HDATE, and DA no luck so far. Thanks again. On another note, is there any website that list all of WebFocus's procedure and stuff?This message has been edited. Last edited by: Yuuta, 7.1.3 on XP into HTML, Excel, PDF | |||
|
| Expert |
www.techsupport.ibi.com has a documentation link on the right side of the page. Also at the top right of this page is a search link to documentation. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
| Master |
Well, you still haven't said what your data source is or which, if any of the fields are indexed. The answer to your question depends on this information. In our environment and with our database (Oracle) we have many datetime fields. There are times when I want just the date (YYMD) and there are times when I want the datetime (HYYMDS). Try the following in your master file: FIELD=XDATE , DBDATE, YYMD , DATE ,$ FIELD=XDATETIME, DBDATE, HYYMDS, HYYMDS,$ Now with that said, assuming your data source is relational, I am pretty sure you are going to have to code your own SQL to get the most efficient join. In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux. | |||
|
| Virtuoso |
Yuuta I'm glad you already tried something your self. It would be helpful if you explain a bit more about the two files (is it SQL, flafiles, focusfiles) Are these files on the same platform (important for performance) For these two fields, are you planning to use them many times in the same format, if so I would suggest to change the master description (if possible). Else do what Ginny suggest and create two focus tables based on the X and Y table where you change the format of the two datefields. You can find many formulas to do this. I would suggest to convert both to a smartdate format (YYMMDD). HDATE does this for the HYYMDS field.
| |||||||
|
| Member |
Hi, sorry for the long reply, it's a good weekend for me Anyway, my database is Oracle. I'm not really sure wether it's indexed or not, but I finally got it to work, yay \(^ ^)/ tried using an SQL Report and it return the data I want, thanks for all your help, be seing you. My final SQL query : SELECT MONTHLYPROGRESS.OPERATIONMONTH FROM OPT.MONTHLYPROGRESS MONTHLYPROGRESS, OPT.DATERANGE DATERANGE WHERE (MONTHLYPROGRESS.OPERATIONMONTH=to_char(daterange.prevmonth,'yyyymm')) END 7.1.3 on XP into HTML, Excel, PDF | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |