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     [CLOSED] Deinfe Base Join for DATE A10V to DATETIME

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Deinfe Base Join for DATE A10V to DATETIME
 Login/Join
 
Arif
Guru
posted
I am trying to create a report and report is using data from multiple sources. I am not able to create a date to date joing for two different date types. I realize that data needs to be in the same formate to create a join. 
  What am I doing wrong here?
 Field in Join file 
SAMPLE:
 '01/01/2010'  AND '12/24/2010'
 
Field in host file 
 Sample:
 '01/01/2010 02:18:11'

JOIN SD2AD WITH SD_MDY1 IN HOSTTABLE TO AD_YMD1 IN JOINTABLE AS J1
SD_YYMD/YYMD = SD_MDY1;
AD2SD/A10VYYMD = SD_YYMD;

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
N.Selph
Guru
posted Hide Post
I wouldn't do that at all. Even if you got it to work, the performance would probably be terrible (define based join, 2 different data sources).
WebFocus optimizer is going to break that up into 2 queries anyway, so its best to do that yourself, hold the results and join those together.

Also are you sure your Join file has data as A10VYYMD? That looks strange to me. What is the master file's datatype?


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
Arif
Guru
posted Hide Post
quote:
Also are you sure your Join file has data as A10VYYMD? That looks strange to me. What is the master file's datatype

YEa thanks for helping. The join doesnt work. Data type in DB for join file is DATE (SQL2008)
When I create metadata type comes out as A10V

Arif


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Waz
Expert
posted Hide Post
The two date formats need to be the same.

Convert the A10V to a YYMD format, it should then work.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Arif
Guru
posted Hide Post
quote:
e two date formats need to be the same.

I am using Defined based join and its extreemly slow. when I conver A10V to YYMD it still dont work because host table has HYYMDs data type.

JOIN ADATE WITH ID IN LEADS_VIEW TO ALL DATE IN DIM_TIME AS JI

DEFINE FILE LEADS_VIEW
NDATE/A20=HCNVRT(DATE_ENTERED, '(HYYMDS)', 20, 'A20');
ADATE/A10V=EDIT(NDATE, '9999999999$$$$$$$$$$');


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
The main problem is that you're using the Microsoft SQL Server 2005 adapter for a Microsoft SQL Server 2008 database. We have seen from other posts that the metadata for the new date-time formats in Microsoft SQL Server 2008 don't generate as WebFOCUS Date-Time formats. I would open a case with Information Builders Tech Support.

Is the database column a DATETIME2 format? If so, read this: [CLOSED] SQL 2008 DATETIME2

This message has been edited. Last edited by: Francis Mariani,


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
FrankDutch
Virtuoso
posted Hide Post
Do you expect a lot of data from both databases?

What I would do is first select the data from the 2 databases, convert the date field as described and put both data in temporary focus tables.
After that you can join on thoses 2 new tables.




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Waz
Expert
posted Hide Post
I think you should do as Francis and Frank suggests.

Open the case, that is important.

This will probably get some responses, but sometimes dumping the tables and joining after is quicker that trying to join back to the DB.

As Frank Asks how many rows on each side of the join ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 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     [CLOSED] Deinfe Base Join for DATE A10V to DATETIME

Copyright © 1996-2020 Information Builders