View source for SQL Queries

[[Category:Results]]
If you are interested in results that you for some reason cannot obtain with the tools available in the applications, you can always run queries in SQL Server Management Studio. The results consists of several tables that must be joined. Below is one example of a query that creates a list of stand-level treatments for a selected optimization. To run this script, open SQL Server Management Studio, connect to the database server, and right-click "My_Heureka_Results" (or corresponding). Choose "New Query", paste the script below and click on "Execute". To see what value to use in the condition for "OPTIMIZATION.OptimizationNo" and "SIMULATION.SimNo", open the tables dbo.Optimization and dbo.Simulation, respectively, and look for the correct result sets.

<pre>

SELECT     SIMULATION.SimNo, TREATMENTUNIT.Description, TREATMENTUNIT.Area AS AvdProdArea, 
                      TREATMENTUNIT.Area * OPTIMIZATIONRESULT.Proportion AS �?tgärdsareal, RESULT.AlternativeNo, RESULT_TREATMENTDATA.RowNo AS �?tgärdsNr, 
                      RESULT.Period, RESULT_TREATMENTDATA.Year AS �?r, TREATMENTTYPE.Description AS �?tgärd, 
                      RESULT_FORESTDATA.MeanAgeTotal_Before AS Medelålder, RESULT_TREATMENTDATA.VolumeCutTotal AS VolAvv, 
                      RESULT_VALUEDATA.Net AS Netto, OPTIMIZATIONRESULT.Proportion AS Andel, OPTIMIZATION.OptimizationNo
FROM         RESULT INNER JOIN
                      TREATMENTUNIT ON RESULT.SimulationGuid = TREATMENTUNIT.SimulationGuid AND 
                      RESULT.TreatmentUnitGUID = TREATMENTUNIT.TreatmentUnitGUID INNER JOIN
                      RESULT_FORESTDATA ON RESULT.ResultGuid = RESULT_FORESTDATA.ResultGuid INNER JOIN
                      SIMULATION ON RESULT.SimulationGuid = SIMULATION.SimulationGuid INNER JOIN
                      OPTIMIZATIONRESULT ON RESULT.TreatmentUnitGUID = OPTIMIZATIONRESULT.TreatmentUnitGuid AND 
                      RESULT.AlternativeNo = OPTIMIZATIONRESULT.AlternativeNo AND RESULT.SimulationGuid = OPTIMIZATIONRESULT.SimulationGuid INNER JOIN
                      OPTIMIZATION ON OPTIMIZATIONRESULT.OptimizationGuid = OPTIMIZATION.OptimizationGuid AND 
                      OPTIMIZATIONRESULT.SimulationGuid = OPTIMIZATION.SimulationGuid FULL OUTER JOIN
                      TREATMENTTYPE RIGHT OUTER JOIN
                      RESULT_VALUEDATA INNER JOIN
                      RESULT_TREATMENTDATA ON RESULT_VALUEDATA.ResultGuid = RESULT_TREATMENTDATA.ResultGuid AND 
                      RESULT_VALUEDATA.RowNo = RESULT_TREATMENTDATA.RowNo ON TREATMENTTYPE.Code = RESULT_TREATMENTDATA.Treatment ON 
                      RESULT.ResultGuid = RESULT_TREATMENTDATA.ResultGuid--
WHERE     (OPTIMIZATIONRESULT.Proportion > 0) and (OPTIMIZATION.OptimizationNo = 0) AND (SIMULATION.SimNo = 1) AND 
                      (RESULT_TREATMENTDATA.Treatment > 0)
ORDER BY TREATMENTUNIT.Description, RESULT.AlternativeNo, RESULT.Period

</pre>