I'm trying to call an SQL Server stored procedure from a fex. No arguments passed. What I'm getting is strange.
My stored procedure:
USE [DataMarts] GO
/****** Object: StoredProcedure [dbo].[TelemedSchedVisitsForClinics] Script Date: 9/20/2019 12:24:38 PM ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
alter PROCEDURE [dbo].[TelemedSchedVisitsForClinics] AS BEGIN SET NOCOUNT ON;
declare @current date = getdate(); declare @currentEnd date = dateadd(week, 1, @current); declare @prior date = dateadd(day, -6, DATEADD(wk, DATEDIFF(wk, 6, cast(getdate() as date)), 6)); declare @priorEnd date = dateadd(week, 1, @prior);
-- MAB 9/19 - for Telemedicine Scheduled and Visits Weekly for Clinics select coalesce(APPT_LOCATION_DISP, NURSE_UNIT) as Location, coalesce(a.C1, 0) as [Scheduled_Appts], coalesce(b.C2, 0) as [VTC_Activity], @prior as C1Date, @current as C2Date from (select APPT_LOCATION_DISP, count(PERSON_ID) as C1 from Scheduling.VTC.ScheduledAppts where -- one thing appstudio does that SQL doesn't - not like in APPT_LOCATION_DISP not like 'APIA%' and APPT_LOCATION_DISP not like 'CHU%' and APPT_LOCATION_DISP not like 'EAT%' and APPT_LOCATION_DISP not like 'ICHC%' and APPT_LOCATION_DISP not like 'KANA%' and APPT_LOCATION_DISP not like 'KIT%' and APPT_LOCATION_DISP not like 'MAN%' and APPT_LOCATION_DISP not like 'MSTC%' and APPT_LOCATION_DISP not like 'NSHC%' and APPT_LOCATION_DISP not like 'SRHC%' and APPT_LOCATION_DISP not like 'Nutaqsiivik' and APPT_LOCATION_DISP not like 'YAK%' and APPT_TYPE_DESCRIPTION not in ('SCF VTC Presenter', 'ANMC VTC Presenter') and -- this selects the coming week starting from today BEG_DT_TM >= @current and BEG_DT_TM <= @currentEnd group by APPT_LOCATION_DISP ) a full outer join (select NURSE_UNIT, count(FIN) as C2 from DataMarts.ENC.VTC_Encounters where ((FACILITY in ('Hospital ANMC', 'Specialty ANMC') and APPT_TYPE not in ('ANMC Telemedicine Consult', 'Telemed Review') ) or (FACILITY = 'PCC ANMC' and NURSE_UNIT in ('CFDS', 'OB\GYN Clinic', 'Pediatrics') and APPT_TYPE not in ('ANMC Telemedicine Consult', 'Telemed Review') )) and -- this selects last week as a range - this would be so much easier in prolog or unix SERVICEDATE >= @prior and SERVICEDATE < @priorEnd group by NURSE_UNIT ) b on a.APPT_LOCATION_DISP = b.NURSE_UNIT END
What I'm getting back, no matter what I change is a file WFServlet.ibfs:
Which is the raw output from the stored procedure. Finally, my fex:
SET SQLENGINE = SQLMSS SQL SQLMSS SET SERVER SQL-CDW-PROD1 SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ; -RUN TABLE FILE SQLOUT SUM SCHEDULED_APPTS AS "Planned VTC Visits,Week of: C1Date" VTC_ACTIVITY AS "VTC Visits,Week of: C2Date" BY LOCATION AS 'ANMC Dept/Location' HEADING "Telemedicine Scheduled and Visits Weekly for Clinics" ; ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLEMBEDIMG ON ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty, $ ENDSTYLE END
I assume the run statement is producing the file and it's not getting piped to SQLOUT, so the report doesn't go. I get a blank Chrome window. I've been reading and reading, nothing works.This message has been edited. Last edited by: mbondr,
WebFOCUS 8.2.04 (and climbing) Windows 10, AppStudio
September 20, 2019, 05:12 PM
mbondr
Moved the -RUN after the END to no effect
WebFOCUS 8.2.04 (and climbing) Windows 10, AppStudio
September 20, 2019, 05:17 PM
BabakNYC
I'm not positive this is the answer but could you try adding an END right above -RUN? so your SP will end with ; next line will say END and the line after that -RUN and the line after that TABLE FILE SQLOUT.
SET SQLENGINE = SQLMSS
SQL SQLMSS SET SERVER SQL-CDW-PROD1
SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ;
END
-RUN
TABLE FILE SQLOUT
SUM
SCHEDULED_APPTS AS "Planned VTC Visits,Week of: C1Date"
VTC_ACTIVITY AS "VTC Visits,Week of: C2Date"
BY LOCATION AS 'ANMC Dept/Location'
HEADING
"Telemedicine Scheduled and Visits Weekly for Clinics" ;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END
WebFOCUS 8206, Unix, Windows
September 20, 2019, 05:36 PM
mbondr
Sigh. It had no effect at all. RUN definitely seems to dump everything to file and ends it there.
WebFOCUS 8.2.04 (and climbing) Windows 10, AppStudio
September 20, 2019, 06:11 PM
mbondr
Tried trimming it down to:
SET SQLENGINE = SQLMSS SQL SQLMSS SET SERVER SQL-CDW-PROD1 SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ; TABLE FILE SQLOUT PRINT SCHEDULED_APPTS VTC_ACTIVITY LOCATION ON TABLE HOLD AS YOURFILE END -RUN
Sigh
WebFOCUS 8.2.04 (and climbing) Windows 10, AppStudio
SET SQLENGINE = SQLMSS SQL SQLMSS SET SERVER SQL-CDW-PROD1 SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ; TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS YOURFILE END -RUN
WebFOCUS 8.2.04 (and climbing) Windows 10, AppStudio
September 20, 2019, 07:03 PM
mbondr
Tried this:
ENGINE SQLMSS SET SQL-CDW-PROD1 DataMarts SQL SQLMSS SET NOCOUNT ON SQL SQLMSS SET ANSI_WARNINGS OFF SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ; -RUN TABLE FILE SQLOUT SUM SCHEDULED_APPTS AS "Planned VTC Visits,Week of: C1Date" VTC_ACTIVITY AS "VTC Visits,Week of: C2Date" BY LOCATION AS 'ANMC Dept/Location' END
WebFOCUS 8.2.04 (and climbing) Windows 10, AppStudio
September 22, 2019, 07:46 PM
StuBouyer
You seem to be missing an END after your call to run the stored procedure
ENGINE SQLMSS SET SQL-CDW-PROD1 DataMarts
SQL SQLMSS SET NOCOUNT ON
SQL SQLMSS SET ANSI_WARNINGS OFF
SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ;
END
TABLE FILE SQLOUT
SUM
SCHEDULED_APPTS AS "Planned VTC Visits,Week of: C1Date"
VTC_ACTIVITY AS "VTC Visits,Week of: C2Date"
BY LOCATION AS 'ANMC Dept/Location'
END
Cheers
Stu
WebFOCUS 8.2.03 (8.2.06 in testing)
September 23, 2019, 10:28 AM
FP Mod Chuck
Mbondr
You can also create a synonym for the stored procedure and create a TABLE request against it.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
September 24, 2019, 05:28 PM
mbondr
FP Mod Chuck - That's what I finally did, but it would be cool to be able to eliminate the master file.
WebFOCUS 8.2.04 (and climbing) Windows 10, AppStudio