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 ENDThis message has been edited. Last edited by: <Emily McAllister>,
Windows, All Outputs
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
"&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.
|Powered by Social Strata|