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

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

  

"&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.

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.