how to create a view without using CASE * updated*

Question

question

pete 1 asked Jun 1, '10

how to create a view without using CASE * updated*

I can receive the correct results when used as a querey, but I would like to turn it into a view. Enterprise Manager will not accept CASE when attempting to create the view.

Here is my querey:

SELECT dbo.INCOME.ID, SUM(CASE WHEN dbo.INCOME.RENT_UNIT_TYPE = 'sf' THEN '1' ELSE dbo.INCOME.RENT_UNITS END) AS 'Rental_Units'

FROM dbo.INCOME INNER JOIN dbo.YEAR ON dbo.INCOME.YEAR_ID = dbo.YEAR.CURRENT_FY

GROUP BY dbo.INCOME.ID

ORDER BY dbo.INCOME.ID

sample data

ID / RENT_Unit_Type / Rent_Units

01 / sf / 1212

01 / sf / 2100

02 / u / 1

03 / u / 5

04 / sf / 567

04 / sf / 1345

04 / u / 1

I am trying to get it to tell me the number of units. every time 'sf' appears, it represents 1 unit. everytime 'u' appears, it is the actual number of units. so to make each time 'sf' is found equal 1, then add it to the number of 'u' for each unique ID.

this is what I would like to recieve:

ID / Rental_Units
01 / 2
02 / 1
03 / 5
04 / 3

sql-server-2000 select aggregates case-statement

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder commented · Jun 01, 2010 at 07:44 PM

You may want to add a GROUP BY ID at the end

0 ·

Answer 1 · 2010-06-01T11:57:42Z

RickD answered Jun 1, '10

CREATE VIEW testView            
AS            
SELECT ID, SUM(CASE WHEN UNIT_TYPE = 'sf' THEN '1' ELSE UNITS END) AS 'rental_units'            
FROM <yourTable>

Run this in a query window on your database and it will work (obviously changing the placemark to your Table).

It is just the View designer that can not handle CASE statements.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-06-01T22:47:35Z

Joe Celko answered Jun 1, '10

SELECT has to have a FROM clause; we don't even know the base table(s). You left off the GROUP BY for the SUM(). The SAUM() is doing math on a string insteae of a numeric.

You confuse fields and columns, which are totally different. You have a some vague, generic "id" that is not really a key. Here is a wild guess:

SELECT property_id, SUM(CASE WHEN unit_type = 'sf' THEN 1 ELSE unit_cnt END) AS rental_units FROM Rentals GROUP BY property_id;

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question