I am trying to create a new .mnt with a sql stored proc as the master. I used the WebFOCUS server to create the synonym. The stored proc has four parameters. Sample prompts me for the parameters and returns the data as expected. I have written a .fex, which returns the expected output based on the values input for the parameters. I have a very simple .mnt I have created. Without the INFER statement, deploy creates or updates the .fcm for my .mnt. If I include the INFER statement, it does not. With and without the INFER statement, my code appears to deploy. I receive no errors and the deployment details indicate it was compiled. I have searched and not found anything to help me. Any assistance would be appreciated.
This app is using WebFOCUS 7703 and SQLServer 2012 or 2014 running in 2008 compatibility mode.
I have included all code and results.
Stored proc CREATE PROCEDURE [dbo].[usp_OffEdCourseAttendSelect] @FacilityCode char(4), @CourseCode char(4), @SessionNumber int, @AttendanceMonthYear datetime AS BEGIN
SELECT --[OffenderEdCourseAttendanceID], [LOCATION], [COURSE], [SESSION_NO], [CLASS_MMYY] FROM DCSCOURS05 WHERE LOCATION = @FacilityCode AND COURSE = @CourseCode AND SESSION_NO = @SessionNumber AND CLASS_MMYY = @AttendanceMonthYear END GO
-* File StoreProcAsMaster.fex TABLE FILE USP_OFFEDCOURSEATTENDSELECT BY LOCATION BY COURSE BY SESSION_NO BY CLASS_MMYY WHERE ( @FACILITYCODE EQ 'WMC' ) AND ( @COURSECODE EQ 'AGED' ) AND ( @SESSIONNUMBER EQ 11 ) AND ( @ATTENDANCEMONTHYEAR EQ '03012017' ); ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = endeflt, $ ENDSTYLE END
Thank you, DeanaThis message has been edited. Last edited by: Deana,
WebFOCUS 7.6.8 and 7.7.03; Windows Server 2003 R2 and Windows Server 2008 R2, respectively; Development environments - Windows Server 2003 R2 and Windows 7 Professional, respectively; excel, html, pdf
April 17, 2017, 11:50 AM
Maintain Wizard
Deana The reason the Infer statement is yielding an error is that Maintain doesn't recognize the @fieldnames. If you just used Maintain against a Master containing:
You would be fine. In your example you are computing values for FacilityCode, CourseCode, SessionNumber and AttendanceMonthYear. However since you are including formats with the Computes, these are not the same as the database fields and Maintain can't write to them.
Can you just create a Master from the lowest segment and use Maintain to write to that?
If this isn't what you are looking for, please open a case so we can track it. Thanks mark
June 12, 2017, 02:32 PM
Deana
As of this morning, I have a work around to allow me to use a stored procedure as my data source using either TABLE or MAINTAIN. As Mark pointed out MAINTAIN does not recognize the @ generated in the original synonym. However, TABLE requires the use of the @ fields.
I have a stored proc, usp_getdata. I created the synonym for this stored procedure using the webfocus console which generated the following code.
I use usp_getdata whenever I need to use TABLE file and usp_getdatamnt for MAINTAIN.
This does create some additional overhead and maintenance, but appears to be working well now.
Thank you to Mark and Adrian. I appreciate your assistance.
Deana
WebFOCUS 7.6.8 and 7.7.03; Windows Server 2003 R2 and Windows Server 2008 R2, respectively; Development environments - Windows Server 2003 R2 and Windows 7 Professional, respectively; excel, html, pdf