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     [CLOSED]Pass Variable to SQL in Editor
Go
New
Search
Notify
Tools
Reply
  
[CLOSED]Pass Variable to SQL in Editor
 Login/Join
 
Member
posted
Hi, I'm trying to create a chart that can run base on a couple of parameters. I am having issues creating a variable and sending it via SQL.

My code is below. The message I receive is that no rows are returned, yet, if I run this same code in SQL Management Studio, I receive records.

 

SQL SQLMSS PREPARE SQLOUT FOR

SELECT
	 SummaryScorecard.AsOfDate
	,Sites.SiteName
	,Metrics.MetricID
	,Metrics.MetricName
	,Benchmarks.BenchmarkValueNumber
	,MetricTarget = CONVERT(DECIMAL(18,4),Benchmarks.BenchmarkValueNumber) * 100
	,SummaryScorecard.Numerator
	,SummaryScorecard.Denominator

FROM
	dbo.Scorecard SummaryScorecard
	INNER JOIN dbo.Metrics Metrics
		ON SummaryScorecard.MetricID = Metrics.MetricID
			AND Metrics.StatusCode = 1
	INNER JOIN dbo.ProviderList Provider
		ON SummaryScorecard.NPI = Provider.NPI
	INNER JOIN dbo.Sites Sites
		ON Provider.REV_LOC_ID = Sites.REV_LOC_ID
	INNER JOIN dbo.Markets Markets
		ON Sites.MarketID = Markets.MarketID
	INNER JOIN dbo.Benchmarks Benchmarks
		ON Metrics.MetricID = Benchmarks.MetricID
WHERE
	Metrics.MetricID = &Metric
END

This message has been edited. Last edited by: <Emily McAllister>,


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: April 06, 2016Reply With QuoteReport This Post
Guru
posted Hide Post
Reallly?


Does your SQL Management Studio understand the "WHERE
Metrics.MetricID = &Metric "

When you run it from WebFOCUS, it is supposed to prompt the value for &Metric.

I would suggest use this:


SQL MSS PREPARE SQLOUT FOR

SELECT
...
FROM
...
WHERE
;
END


TABLE FILE SQLOUT
PRINT *
END

  


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
"&Metric" is ALPHA, surround it with single-quotes and add .EVAL to it: WHERE Metrics.MetricID = '&Metric.EVAL'.

Also, 1) set &ECHO to ALL, 2) Add some TYPE statement before the "SQL SQLMSS PREPARE SQLOUT FOR" for each of the variables.


   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Most recent: 8204 Gen 48 in Test and Production.
   Currently Available. Please contact me.
 
Posts: 3012 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Member
posted Hide Post
Try placing -SET &ECHO='ALL'; at the top and then exit out after the sql. You will get the code that is being sent to the database. It might be that the variable isn't being set the way you thought.


WebFOCUS 8.008
 
Posts: 1 | Location: United States | Registered: April 01, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Pass Variable to SQL in Editor

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