Solved

IIF/CASE statement in a SQL Server VIEW

Posted on 2006-10-27
3
1,496 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
Comment Utility
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
Comment Utility
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
Comment Utility
Also, as said by "TommyTupa", try creating views using the Query Analyzer.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now