Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Calling Stored Proc From Fex
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]Calling Stored Proc From Fex
 Login/Join
 
Gold member
posted
Yes, you can roll your eyes. Another nube.

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:

000011Pain Center 1 00000102019-09-090000102019-09-20
000016Gastroenterology 2 00000102019-09-090000102019-09-20
000004CFDS 2 00000102019-09-090000102019-09-20
000011Pulmonology 11 00000102019-09-090000102019-09-20
000010Pediatrics 2 80000102019-09-090000102019-09-20
000015Primary Care 1E 1 00000102019-09-090000102019-09-20
000007Int Med 7 20000102019-09-090000102019-09-20
000011Dermatology 1 10000102019-09-090000102019-09-20
000008Diabetes 2 00000102019-09-090000102019-09-20
000012Rheumatology 1 10000102019-09-090000102019-09-20
000010Nephrology 3 20000102019-09-090000102019-09-20
000008Oncology 8 20000102019-09-090000102019-09-20
000010Cardiology 21 180000102019-09-090000102019-09-20
000002OT 2 10000102019-09-090000102019-09-20

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, All Outputs
 
Posts: 81 | Registered: February 08, 2018Reply With QuoteReport This Post
Gold member
posted Hide Post
Moved the -RUN after the END to no effect


WebFOCUS 8.2.04 (and climbing)
Windows, All Outputs
 
Posts: 81 | Registered: February 08, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1672 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Gold member
posted Hide Post
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, All Outputs
 
Posts: 81 | Registered: February 08, 2018Reply With QuoteReport This Post
Gold member
posted Hide Post
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, All Outputs
 
Posts: 81 | Registered: February 08, 2018Reply With QuoteReport This Post
Gold member
posted Hide Post
This is the output of the stored procedure:

Location Scheduled_Appts VTC_Activity C1Date C2Date
Pain Center 1 0 2019-09-09 2019-09-20
Gastroenterology 2 0 2019-09-09 2019-09-20
Int Med 7 2 2019-09-09 2019-09-20
CFDS 2 0 2019-09-09 2019-09-20
Pulmonology 11 0 2019-09-09 2019-09-20
Pediatrics 2 8 2019-09-09 2019-09-20
Primary Care 1E 1 0 2019-09-09 2019-09-20
Dermatology 1 1 2019-09-09 2019-09-20
Diabetes 2 0 2019-09-09 2019-09-20
Rheumatology 1 1 2019-09-09 2019-09-20
Nephrology 3 2 2019-09-09 2019-09-20
Oncology 8 2 2019-09-09 2019-09-20
Cardiology 21 18 2019-09-09 2019-09-20
OT 2 1 2019-09-09 2019-09-20

This produces a blank page labeled "No Output"

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, All Outputs
 
Posts: 81 | Registered: February 08, 2018Reply With QuoteReport This Post
Gold member
posted Hide Post
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, All Outputs
 
Posts: 81 | Registered: February 08, 2018Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.0.09
 
Posts: 237 | Location: Melbourne, Australia | Registered: February 07, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1585 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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, All Outputs
 
Posts: 81 | Registered: February 08, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Calling Stored Proc From Fex

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.