Good morning,

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


.ACX File
SEGNAME=INPUT, CONNECTION=CORR, STPNAME=dbo.usp_OffEdCourseAttendSelect, $
SEGNAME=OUTPUT, STPRESORDER=0, $
SEGNAME=ANSWERSET1, STPRESORDER=1, $

.MAS File
FILENAME=USP_OFFEDCOURSEATTENDSELECT, SUFFIX=SQLMSS , $
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@FACILITYCODE, ALIAS=P0001, USAGE=A4, ACTUAL=A4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@COURSECODE, ALIAS=P0002, USAGE=A4, ACTUAL=A4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@SESSIONNUMBER, ALIAS=P0003, USAGE=I11, ACTUAL=I4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=@ATTENDANCEMONTHYEAR, ALIAS=P0004, USAGE=MDYY, ACTUAL=DATE,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=@RETURN_VALUE, ALIAS=P0000, USAGE=I11, ACTUAL=I4, $
SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=LOCATION, ALIAS=LOCATION, USAGE=A4, ACTUAL=A4, $
FIELDNAME=COURSE, ALIAS=COURSE, USAGE=A4, ACTUAL=A4, $
FIELDNAME=SESSION_NO, ALIAS=SESSION_NO, USAGE=I11, ACTUAL=I4, $
FIELDNAME=CLASS_MMYY, ALIAS=CLASS_MMYY, USAGE=MDYY, ACTUAL=DATE, $


Sample Data for BASEAPP/USP_OFFEDCOURSEATTENDSELECT


LOCATION COURSE SESSION_NO CLASS_MMYY
WMC AGED 11 03/01/2017
WMC AGED 11 03/01/2017


-* 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

.fex output
LOCATION COURSE SESSION_NO CLASS_MMYY
WMC AGED 11 03/01/2017

.mnt
MAINTAIN FILE USP_OFFEDCOURSEATTENDSELECT

$$Declarations

Case Top
-*INFER usp_offedcourseattendselect.ANSWERSET1.LOCATION INTO StkAttendance;

COMPUTE
FacilityCode/A4 = 'WMC';
CourseCode/A4 = 'AGED';
SessionNumber/I11 = 10;
AttendanceMonthYear/MDYY = 03012017;

Winform Show Form1;
EndCase

END

Deployment Details
---------------Scenario: Local Deploy (Development)---------------
Calling deploy extensions...
Compiling maintain procedures...
IBFS:/localhost/DEV/testcode/TestPopFromSql.mnt

********************************************************************************************
Deployment Status: 0 error(s), 0 warning(s)
********************************************************************************************


Thank you,
Deana

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

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:

SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=LOCATION, ALIAS=LOCATION, USAGE=A4, ACTUAL=A4, $
FIELDNAME=COURSE, ALIAS=COURSE, USAGE=A4, ACTUAL=A4, $
FIELDNAME=SESSION_NO, ALIAS=SESSION_NO, USAGE=I11, ACTUAL=I4, $
FIELDNAME=CLASS_MMYY, ALIAS=CLASS_MMYY, USAGE=MDYY, ACTUAL=DATE, $

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

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.

.acx
SEGNAME=INPUT, CONNECTION=CONN, STPNAME=dbo.usp_getdata, $
SEGNAME=OUTPUT, STPRESORDER=0, $
SEGNAME=ANSWERSET1, STPRESORDER=1, $

.mas
FILENAME=USP_GETDATE, SUFFIX=SQLMSS, $
SEGMENT=INPUT, SEGTYPE=S0, $
FIELDNAME=@InputField1, ALIAS=P0001, USAGE=A4, ACTUAL=A4,
MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=@RETURN_VALUE, ALIAS=P0000, USAGE=I11, ACTUAL=I4, $
SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $
FIELDNAME=Field1Name, ALIAS=Field1Alias, USAGE=I11, ACTUAL=I4, $

I then copied these 2 files to usp_getdatamnt.acx and usp_getdatamnt.mas and made changes so the files look like this.

.acx
SEGNAME= ANSWERSET1, CONNECTION=CONN, STPNAME=dbo.usp_getdata, $

.mas
FILENAME=USP_GETDATE, SUFFIX=SQLMSS , $
SEGMENT=ANSWERSET1, SEGTYPE=S0, $
FIELDNAME=Field1Name, ALIAS=Field1Alias, USAGE=I11, ACTUAL=I4, $

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