Using a case statement in a view - sql 7

[nkiefer]

Is it possible to use a case statement in a view in sql 7? I am using the following in a stored procedure and would like to use same in a view:

SELECT DISTINCT vwAvgStDev_Units.fldTestTypeID,
'FailureStressUnits' =
CASE
When [fldResultsFailureStressUnits] = 1 Then 'psi'
When [fldResultsFailureStressUnits] = 2 Then 'Mpa'
Else ''
END,
FROM vwAvgStDev_Units
WHERE (((vwAvgStDev_Units.fldTestTypeID) Not In
(SELECT [fldTestTypeID] FROM [vwAvgStDev_Units] As Tmp
GROUP BY [fldTestTypeID] HAVING Count(*)>1 )))
ORDER BY vwAvgStDev_Units.fldTestTypeID;

 

[Vancouverite]

Your main problem is that a View cannot contain an Order By clause. You will order the data after the select:

SELECT * From MyView
ORDER BY fldTestTypeID

 

[nkiefer]

Thanks that worked. Now when I try to saved the view I get a message saying "View definition includes no output columns or includes no items in the FROM clause." However the query runs and returns data, I just can't save it. Any ideas?

Here is the actual statement:

SELECT DISTINCT
vwAvgStDev_Units.fldTestTypeID,
'FailureStressUnits' = CASE WHEN [fldResultsFailureStressUnits] = 1 THEN 'psi' WHEN [fldResultsFailureStressUnits] = 2 THEN 'Mpa' ELSE '' END,
'ResultsFailureLoadUnits' = CASE WHEN [fldResultsFailureLoadUnits] = 1 THEN 'pli' WHEN [fldResultsFailureLoadUnits] = 2 THEN 'N/25mm' ELSE '' END,
'ResultsAppliedUnits' = CASE WHEN [fldResultsAppliedUnits] = 1 THEN 'lbs' WHEN [fldResultsAppliedUnits] = 2 THEN 'kg' ELSE '' END,
'ResultsCreepRateUnits' = CASE WHEN [fldResultsCreepRateUnits] = 1 THEN 'mm/hr' WHEN [fldResultsCreepRateUnits] = 2 THEN '' ELSE '' END,
'ResultsPeelLoadUnits' = CASE WHEN [fldResultsPeelLoadUnits] = 1 THEN 'lbs' WHEN [fldResultsPeelLoadUnits] = 2 THEN 'kg' ELSE '' END,
'ResultsShearLoadUnits' = CASE WHEN [fldResultsShearLoadUnits] = 1 THEN 'lbs' WHEN [fldResultsShearLoadUnits] = 2 THEN 'kg' ELSE '' END
FROM vwAvgStDev_Units
WHERE (((vwAvgStDev_Units.fldTestTypeID) NOT IN
(SELECT [fldTestTypeID]
FROM [vwAvgStDev_Units] AS Tmp
GROUP BY [fldTestTypeID]
HAVING COUNT(*) > 1)))