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     [SOLVED] Calendar prompt throws error

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Calendar prompt throws error
 Login/Join
 
Platinum Member
posted
Hello,

I am trying to build a HTML launch page for a SQL pass thru report (external fex file) which has a date prompt. I have to use the calendar control type for my date prompt and I have been able to do that through HTML composer (WF 7.7.03). The datatype for the date field in my database is "date", which appears as "01-FEB-15" when I query the table.

The calendar control in HTML composer does not have this format - I have currently set it to DMY and it throws this error when I run it: (I think it passes the date as 01/02/16 to the fex file)

(FOC1400) SQLCODE IS 1858 (HEX: 00000742)
: ORA-01858: a non-numeric character was found where a numeric was
: expected
L (FOC1406) SQL OPEN CURSOR ERROR. : SQLOUT


What should I do in order for the date from the calendar prompt to match the format from the one in my database? Please help!

Thanks!

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


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Virtuoso
posted Hide Post
In Composer, change the format of the date field to HYYMD.

If you have access to the Reporting Server Console, you could go to Adapters and click Data Types on the Ribbon Bar and get a report that shows you how DBMS formats map out to WebFOCUS formats. When I did that for Oracle, DATE format is mapped to HYYMDS. S is for min/seconds. I assume you don't need that, so you can omit the S from the format.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
There is no choice called 'HYYMD' in HTML composer, so I entered it by going into the text editor and it throws me some other error.

I don't think this works for 7.7.03

Any other thoughts? Thanks!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Virtuoso
posted Hide Post
Sorry, I gave you an 8.x answer. Unfortunately, I can't remember Composer that far back. Frowner


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Expert
posted Hide Post
In your original post above, perhaps you should state that your'e using the Dev Studio GUI tool to build an HTML page and that you do not have a master for the table that you're retrieving the dates from. (which I think was explained in another post you made).

My *guess* is that you're populating the Calendar control with a "Dynamic" "Embedded procedure" that has code something like this:

SQL
SELECT
COUNTRY
FROM CAR
GROUP BY COUNTRY
ORDER BY COUNTRY
TABLE
ON TABLE PCHOLD FORMAT XML
END

Where you have a date column that doesn't properly work with the Calendar control.

If my guess is correct, instead of a "Dynamic" "Embedded procedure", use a "Dynamic" "External Procedure". The fex can now manipulate the date and format it properly.


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
Master
posted Hide Post
This issue may already be resolved, but here's how you can play around with an incoming date string in DMY format (Assuming it is sent "unformatted" -- meaning no slash/dash characters for date component separators):

-* -? &
-* -EXIT

-DEFAULT &MY_DATE=180117;

-SET &MY_DAY=EDIT(&MY_DATE, '99$$$$');
-SET &MY_MONTH=EDIT(&MY_DATE, '$$99$$');
-SET &MY_YEAR=EDIT(&MY_DATE, '$$$$99');
-SET &MONTH_NAME=DECODE &MY_MONTH ( '01' 'JAN' '02' 'FEB' '03' 'MAR' '04' 'APR' '05' 'MAY' '06' 'JUN' '07' 'JUL' '08' 'AUG' '09' 'SEP' '10' 'OCT' '11' 'NOV' '12' 'DEC' ) ;
-SET &MY_MONTH_NAME=&MY_DAY || '-' || &MONTH_NAME || '-' || &MY_YEAR

-TYPE &MY_DAY
-TYPE &MY_MONTH
-TYPE &MY_YEAR
-TYPE &MY_MONTH_NAME

Output:

 SET GRAPHENGINE=GRAPH53
 18
 01
 17
 18-JAN-17


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Platinum Member
posted Hide Post
Your guess is partly correct - I originally wanted to create a drop-down list of dates for a SQL pass thru report (with no Master file as source), but since I could not achieve a drop-down list I decided to try a calendar icon prompt (not with dynamic procedure). That's when I received an error since (I guess) the calendar prompt date format doesn't match the date format in my SQL query. (I am trying to pass the user selected date value to the &variable in my SQL query through a push button on the HTML launch page which runs my report.)

After reading your response, I tried to change my calendar control to drop-down list control and selected "Dynamic External procedure" to populate it with dates so that user can select the date. They can then push the button to run the other report where the (user selected) date will be passed to &variable. But after I tried this the HTML page runs with all the dates displayed as a column instead of populating the drop-down box. I am not sure what went wrong. Now I can't select a date and obviously can't see the "button" to run my report, since the entire date column is displayed on the webpage.

Please help! Thanks!


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Expert
posted Hide Post
Do you have ON TABLE PCHOLD FORMAT XML? in the external fex?

You want to use the HTML GUI tool in a most simple way, but it's easy to create a mess very quickly.

I feel it's hard to explain how to do things in the GUI. The best that some at IBI will tell you is to start over with a fresh blank HTML page.


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
Platinum Member
posted Hide Post
I have ON TABLE PCHOLD FORMAT HTML in my external fex file.

Also, I started on a fresh blank HTML page while building this drop-down prompt and 'Run report' button.


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Platinum Member
posted Hide Post
Thanks Squatch - the incoming date (with calendar control in HTML composer) comes in with slashes (01/02/17 for 01-Feb-17).
Will the same code work with slashes too?


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report This Post
Master
posted Hide Post
quote:
Originally posted by Nova27:
Thanks Squatch - the incoming date (with calendar control in HTML composer) comes in with slashes (01/02/17 for 01-Feb-17).
Will the same code work with slashes too?

The EDIT commands need to be modified, like so:

-DEFAULT &MY_DATE=18/01/17;

-SET &MY_DAY=EDIT(&MY_DATE, '99$$$$$$');
-SET &MY_MONTH=EDIT(&MY_DATE, '$$$99$$$');
-SET &MY_YEAR=EDIT(&MY_DATE, '$$$$$$99');
-SET &MONTH_NAME=DECODE &MY_MONTH ( '01' 'JAN' '02' 'FEB' '03' 'MAR' '04' 'APR' '05' 'MAY' '06' 'JUN' '07' 'JUL' '08' 'AUG' '09' 'SEP' '10' 'OCT' '11' 'NOV' '12' 'DEC' ) ;
-SET &MY_MONTH_NAME=&MY_DAY || '-' || &MONTH_NAME || '-' || &MY_YEAR

-TYPE &MY_DAY
-TYPE &MY_MONTH
-TYPE &MY_YEAR
-TYPE &MY_MONTH_NAME


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Expert
posted Hide Post
If you're populating a control on a GUI designed page, you need XML not HTML.


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
Platinum Member
posted Hide Post
Thank you so much Squatch and Francis -- both of your inputs helped me finally create a calendar/drop-down input parameter and convert my date to the required format.

My report finally works!!! Thanks a bunch!! You guys rock! Smiler Smiler


WF 7.7.03, Win 7
 
Posts: 127 | Registered: January 12, 2017Report 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     [SOLVED] Calendar prompt throws error

Copyright © 1996-2020 Information Builders