I would like to use an HTML calendar in fron of this report however when I reference the external procedure I get the following message

(FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : SORLCUR_PIDM
(FOC1400) SQLCODE IS 1858 (HEX: 00000742)
: ORA-01858: a non-numeric character was found where a numeric was
: expected


the code is below

-SET &MYDATE=EDIT(&DATE_YYYYMMDD,'99999999');
ENGINE SQLORA SET DEFAULT_CONNECTION PRODW
SQL SQLORA PREPARE SQLOUT FOR
select *
from(
select count(*) , sorlcur_pidm
from(
select distinct sorlcur_pidm,sorlfos_majr_code from sorlcur,sorlfos
where sorlcur_lmod_code='LEARNER'
and sorlcur_activity_date >= TO_DATE ('&MYDATE','YYYYMMDD')
and sorlcur_cact_code='ACTIVE'
and sorlcur_current_cde='Y'
and sorlfos_cact_code='ACTIVE'
and sorlfos_current_cde='Y'
and sorlcur_pidm=sorlfos_pidm
and sorlfos_lfst_code='MAJOR'
and sorlfos_priority_no='1'
and sorlcur_seqno=sorlfos_lcur_seqno)
group by sorlcur_pidm
having count(*) >1)
END
TABLE FILE SQLOUT
PRINT
SORLCUR_PIDM
COUNT___
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_PIDM
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END



SET ASNAMES = ON
JOIN
LEFT_OUTER HOLD_PIDM.HOLD_PID.SORLCUR_PIDM IN HOLD_PIDM TO MULTIPLE
SORLFOS.SORLFOS.SORLFOS_PIDM IN SORLFOS TAG J1 AS J1
END

TABLE FILE HOLD_PIDM
PRINT
HOLD_PIDM.HOLD_PID.COUNT___/I5 NOPRINT
COMPUTE MAJORCHANGES/I5 = HOLD_PIDM.HOLD_PID.COUNT___ - 1;
J1.SORLFOS.SORLFOS_MAJR_CODE
BY LOWEST HOLD_PIDM.HOLD_PID.SORLCUR_PIDM
BY LOWEST 1 J1.SORLFOS.SORLFOS_LCUR_SEQNO
BY LOWEST 1 J1.SORLFOS.SORLFOS_SEQNO
WHERE J1.SORLFOS.SORLFOS_ACTIVITY_DATE GE DT(&DATE_YYYYMMDD.QUOTEDSTRING);
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_LWST_MJR
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END



JOIN
LEFT_OUTER HOLD_PIDM.HOLD_PID.SORLCUR_PIDM IN HOLD_PIDM TO MULTIPLE
SORLFOS.SORLFOS.SORLFOS_PIDM IN SORLFOS TAG J2 AS J2
END

TABLE FILE HOLD_PIDM
PRINT
HOLD_PIDM.HOLD_PID.COUNT___/I5 NOPRINT
COMPUTE MAJORCHANGES/I5 = HOLD_PIDM.HOLD_PID.COUNT___ - 1;
J2.SORLFOS.SORLFOS_MAJR_CODE
BY LOWEST HOLD_PIDM.HOLD_PID.SORLCUR_PIDM
BY HIGHEST 1 J2.SORLFOS.SORLFOS_LCUR_SEQNO
BY HIGHEST 1 J2.SORLFOS.SORLFOS_SEQNO
WHERE J2.SORLFOS.SORLFOS_ACTIVITY_DATE GE DT(&DATE_YYYYMMDD.QUOTEDSTRING);
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_HIST_MJR
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END



JOIN
LEFT_OUTER HOLD_LWST_MJR.HOLD_LWS.SORLCUR_PIDM IN HOLD_LWST_MJR TO UNIQUE
HOLD_HIST_MJR.HOLD_HIS.SORLCUR_PIDM IN HOLD_HIST_MJR TAG J3 AS J3
END
TABLE FILE HOLD_LWST_MJR
BY LOWEST HOLD_LWST_MJR.HOLD_LWS.SORLCUR_PIDM
BY LOWEST HOLD_LWST_MJR.HOLD_LWS.SORLFOS_MAJR_CODE AS 'LOWEST'
BY J3.HOLD_HIS.SORLFOS_MAJR_CODE AS 'HIGHEST'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_ALL_MJR
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
JOIN
LEFT_OUTER FILE HOLD_ALL_MJR AT HOLD_ALL_MJR.HOLD_ALL.SORLCUR_PIDM TO UNIQUE
FILE SPRIDEN AT SPRIDEN.SPRIDEN.SPRIDEN_PIDM TAG J4 AS J4
WHERE HOLD_ALL_MJR.HOLD_ALL.SORLCUR_PIDM EQ J4.SPRIDEN.SPRIDEN_PIDM;
WHERE J4.SPRIDEN.SPRIDEN_CHANGE_IND EQ MISSING;
END
TABLE FILE HOLD_ALL_MJR
PRINT
J4.SPRIDEN.SPRIDEN_ID AS 'ID'
HOLD_ALL_MJR.HOLD_ALL.LOWEST AS 'Lowest,Sequence,Major'
HOLD_ALL_MJR.HOLD_ALL.HIGHEST AS 'Highest,Sequence,Major'
BY LOWEST J4.SPRIDEN.SPRIDEN_LAST_NAME AS 'Last Name'
BY LOWEST J4.SPRIDEN.SPRIDEN_FIRST_NAME AS 'First Name'
ON TABLE SUBHEAD
"RunDate: "
"RunTime:"
"Report for Majors changed since &DATE_YYYYMMDD.QUOTEDSTRING "
"Where Field of Study Record = Priority 1 Active and Learner "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL07

If you can point out my error that would be great

This message has been edited. Last edited by: Geoff Fish,

What does

-SET &MYDATE=EDIT(&DATE_YYYYMMDD,'99999999');

do?

For Oracle, our SQL uses dates with dashes, that is also how the WF Oracle engine translates WF code.

If you have slashes in &DATE_YYYYMMDD, then do:

-SET &MYDATE=EDIT(&DATE_YYYYMMDD,'9999$-99$-99');

This removes slashes and adds dashes.

If you have no slashes in &DATE_YYYYMMDD, then do:

-SET &MYDATE=EDIT(&DATE_YYYYMMDD,'9999-99-99');

This adds dashes.

In the SQL, I think the code should be:

and sorlcur_activity_date >= TO_DATE ('&MYDATE','YYYY-MM-DD')

It appears that date values without dashes works as well, so, if you have slashes in &DATE_YYYYMMDD, then do:

-SET &MYDATE=EDIT(&DATE_YYYYMMDD,'9999$99$99');

This removes slashes. Then you should be able to leave the SQL as is.

Francis : thanks for your response, my real issue is trying to figure out what the HTML calndar passes to the Fex and then matching that format in other words what does "var rltdyncalendar = "rltdyncalendar";" output


Thanks again in the mean time I will see if I can get your suggestion to play nice with the calendar

Geoff

Francis you were spot on



-SET &MYDATE=EDIT(&DATE_YYYYMMDD,'9999$99$99');
worked great