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] A10V SQL 2000 DATE data Type

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] A10V SQL 2000 DATE data Type
 Login/Join
 
Arif
Guru
posted
I have posted this question before that my SQL 2008 date data type fields come up as A10V. I received an answer telling me that date is officially supported in WF 7.7. Is there a work around or anything that I can do in metadate to be able to manipulate dates in my reports? for example if I want to add Where condition for date column cal_date:
WHERE CAL_DATE GE '01/01/2010'


When I use the above statement in my procedure I get error message field formate problem.
I changed metadata usage to YYMD, but I am still getting error message. Any suggestions?

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


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
If you changed the USAGE attribute to YYMD and if this change works, then try:

WHERE CAL_DATE GE '20100328'


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
Arif
Guru
posted Hide Post
It still fails:
Date Type:
FIELDNAME=CAL_DATE, ALIAS=CAL_DATE, USAGE=YYMD, ACTUAL=A10V,
      MISSING=ON, $


QUEREY:

WHERE CAL_DATE GE '20100328'

ERROR MESSAGE:
 (FOC1400) SQLCODE IS 206 (HEX: 000000CE) XOPEN: 22018
 : Microsoft OLE DB Provider for SQL Server: [22018] Operand type clash: da
 : te is incompatible with int [42000] Statement(s) could not be prepared.
 : [] Deferred prepare could not be completed.
 L    (FOC1406) SQL OPEN CURSOR ERROR.  : RO_AGG_ENROLLMENT


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
I don't have SQL Server 2008 so I can't fool around with this, but are you sure that the modified USAGE actually works? Try a simple request with no WHERE on the date filed - what do the values look like in the output?


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
Arif
Guru
posted Hide Post
quote:
don't have SQL Server 2008 so I can't fool around with this, but are you sure that the modified USAGE actually works? Try a simple request with no WHERE on the date filed - what do the values look like in the output?

If I just Print where my DIVISIONID EQ 8101 i get the correct date formate 2009/01/02
CAL_DATE     DIRECTOR      DISTRICTNAME    DIVISIONID      ENROLLEDACTIVE     ENROLLEDUNSCHEDULED       NEWENROLL 
2009/01/02   Missy Brown   New York/PA       8101              1                0                          0


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
Try
WHERE CAL_DATE GE '2010-03-28'


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
Arif
Guru
posted Hide Post
quote:
WHERE CAL_DATE GE '2010-03-28'

It doesnt work either. I am getting error message when I use WHERE CAL_DATE GE '2010-03-28'


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Arif
Guru
posted Hide Post
IT is interesing that when I changed it back to A10V as actual and A10V as usage I am able to run
this code:
WHERE CAL_DATE GE '2009-01-01' AND CAL_DATE LE '2009-02-02' ;
However, some of my developers are not able to use other date functions and manipulations. Any suggesions?

Arif


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
FrankDutch
Virtuoso
posted Hide Post
Arif

did you buy the book....

many questions here about day manipulation and I always advice to spend 25 U$ and buy it....




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
Francis Mariani
Expert
posted Hide Post
Frank, actually this time the problem is not really with dates but with using the MS SQL Server 2005 data adapter with MS SQL Server 2008.


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
Arif
Guru
posted Hide Post
Yes i did buy two books; quick reference guide and 1001 ways to work with dates.

This problem is data translation problem. DATE data type from SQL is showing up as A10V in meta data. I wish there was some hotfix for this.


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
Arif, you need to find out when a data adapter will be available for MS SQL Server 2008, or open a case regarding this problem.


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
Arif
Guru
posted Hide Post
Francis thank you for all your great help. Here is an interesting thing I discovered.
When I change usage in my meta data to A10V I can run code like this
[CODE]
WHERE CAL_DATE GE '2010/01/01'
Above code works great.

When I change data usage to YYMD
I get following Error

(FOC1400) SQLCODE IS 206 (HEX: 000000CE) XOPEN: 22018
: Microsoft OLE DB Provider for SQL Server: [22018] Operand type clash: da
: te is incompatible with int [42000] Statement(s) could not be prepared.
: [] Deferred prepare could not be completed.
L (FOC1406) SQL OPEN CURSOR ERROR. : RO_AGG_ENROLLMENT

Interestingly Enough when Data usage is A10V I cant use following code:
WHERE CAL_DATE GE DATEADD('&YYMD', 'Y', -1);
I understand i am using computational field with A10V. But I think GE operator is also computational. So, I think I really need to use defined field driven from the CAL_DATE field to make it YYMD and drive my report from there.

I am going to open a case with IBI;However, I am afraid they will recommend to upgrade to 7.7.


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
Arif, yes, your best solution for the moment is to create a defined field.


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
Dan Satchell
Virtuoso
posted Hide Post
Have you tried defining the field as a date in the master?
FIELDNAME=CAL_DATE, ALIAS=CAL_DATE, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $

If in the end you must use a DEFINE to convert the A10V format to a date, I would put the DEFINE in the master description so it is always active. I would name the DEFINEd field as CAL_DATE and the A10V field as CAL_DATEX (or something other) so when you do upgrade to 7.7 and/or install the 2008 data adapter, you can delete the DEFINE in the master and name the correctly translated date field as CAL_DATE. That way you won't need to make any changes to your program code.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Not sure about the naming suggestion - the ALIAS in the master must match the table, so I don't know what happens if the ALIAS name of a column matches a DEFINE column...


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
Dan Satchell
Virtuoso
posted Hide Post
Francis, good point. I'm not sure myself, but it's worth a try.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Arif
Guru
posted Hide Post
quote:
. 

 Its a good suggestion, I will give a shot and post the results tomorrow. Thank you for all your great help!


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
FrankDutch
Virtuoso
posted Hide Post
Did you try to set the actual format in the master to A10 instead of A10V?




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
Arif
Guru
posted Hide Post
Yes sir A10 and A10V have the same results.


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
EricH
Platinum Member
posted Hide Post
USAGE=YYMD, ACTUAL=DATE

EricH
 
Posts: 164 | Registered: March 26, 2003Report This Post
CoolGuy
Virtuoso
posted Hide Post
So, it is now 2016 and we are still dealing with similar issues. I brought over a group of tables joined together on the SQL side (MS SQL Server 2012), and all fields of DATE datatype were then described as an A10V (actual and usage) in the synonym generated for this stored procedure. Why does it do this? Why does it not bring them over as YYMD? You think after 6 years IBI would have fixed this already... I had to manually change all actual datatypes from A10V to DATE (YYMD) to fix things.

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


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report 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] A10V SQL 2000 DATE data Type

Copyright © 1996-2020 Information Builders