Solved

IIF/CASE statement in a SQL Server VIEW

Posted on 2006-10-27
3
1,499 Views
Last Modified: 2012-06-27
Experts, in my Select statement I want split a transaction column into credits and debits, in SQL Server.  I have opened the SQL Query Analyzer and typed:

CASE WHEN tran > 0 THEN tran ELSE 0 END as credits, CASE WHEN tran < 0 THEN abs(tran) ELSE 0 END as debits FROM transactions

And it works great...

But when I open a new VIEW and enter the same working code, I get an error: "The Query Designer does not support the CASE SQL construct."  And it won't allow me to save the view.  Is there a way I can do an IIF type statement in the Query Designer, that will actually let me save it as a VIEW?
0
Comment
Question by:AptDev
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
TommyTupa earned 450 total points
ID: 17823219
Try using  SQL Query Analyzer rather than the Query designer or enterprise manager which have limitations.

Open query analyzer and create a view with the syntax 'Create vew MyView as select....'

For example:

Use Northwind
Go
Create view my_View
as
select * from customers





0
 
LVL 5

Assisted Solution

by:nakul_vachhrajani
nakul_vachhrajani earned 50 total points
ID: 17833263
The following is working for me in a view in the production environment (I am substituting table names and column names with alphabets to protect privacy of the system).

SELECT a, b,
          (CASE c
            WHEN 1 THEN 'Yes'
            ELSE 'No' END) AS MyCase,
FROM MyTable
0
 
LVL 5

Expert Comment

by:nakul_vachhrajani
ID: 17833267
Also, as said by "TommyTupa", try creating views using the Query Analyzer.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question