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,

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.

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!

Sorry, I gave you an 8.x answer. Unfortunately, I can't remember Composer that far back.

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.

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

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!

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.

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.

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?

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

If you're populating a control on a GUI designed page, you need XML not HTML.

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!