create View showing a conditional field

How can I create in a view, a calculated field (name) that depends from 
other field (value) as follows: if not isnull(value) then (name) ="Discount" 
else (name) = null

thanks for your suggestions

LAM
 

0
LuisM
8/22/2010 12:39:01 AM
sqlserver.server 1327 articles. 0 followers. Follow

4 Replies
1175 Views

Similar Articles

[PageSpeed] 12

LuisM (luisantony&@hotmail.com) writes:
> How can I create in a view, a calculated field (name) that depends from 
> other field (value) as follows: if not isnull(value) then (name)
> ="Discount" else (name) = null 
 
name AS CASE WHEN value IS NOT NULL THEN "Discount" END


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/22/2010 8:21:05 AM
Sorry Erland, I wasn't able to configure this sintax for use with a sql 
view.

LAM

"Erland Sommarskog" <esquel@sommarskog.se> escribi� en el mensaje 
news:Xns9DDC694CCF1E7Yazorman@127.0.0.1...
> LuisM (luisantony&@hotmail.com) writes:
>> How can I create in a view, a calculated field (name) that depends from
>> other field (value) as follows: if not isnull(value) then (name)
>> ="Discount" else (name) = null
>
> name AS CASE WHEN value IS NOT NULL THEN "Discount" END
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
LAM
8/23/2010 7:39:34 PM
LAM (luism0@(arroba)yahoo.es) writes:
> Sorry Erland, I wasn't able to configure this sintax for use with a sql 
> view.
 
What did you try? What error message did you get? (And you did not use the 
View Designer, did you?)


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/23/2010 9:42:44 PM
Yes! finally I got it, with this sintax:

USE MYBD
GO

IF object_id('dbo.VIEW1', 'V') IS NOT NULL
DROP VIEW dbo.VIEW1;
GO
CREATE VIEW dbo.VIEW1 AS
SELECT NUM, DATE, 'NAME' =
  CASE
  when NUM IS NOT NULL then 'Discount'
  END
FROM dbo.TABLE1;

thanks

LAM

"Erland Sommarskog" <esquel@sommarskog.se> escribi� en el mensaje 
news:Xns9DDDF1373C17AYazorman@127.0.0.1...
> LAM (luism0@(arroba)yahoo.es) writes:
>> Sorry Erland, I wasn't able to configure this sintax for use with a sql
>> view.
>
> What did you try? What error message did you get? (And you did not use the
> View Designer, did you?)
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
LAM
8/24/2010 3:19:14 PM
Reply:

Similar Artilces:

Print or Print Preview not showing text
Print or Print Preview not showing text using PP2007 just released SP2 for office Now when we try to use print or print preview only see some of the text headers. None of the text is showing or the background or images, using built in templates. Worked bofore After testing, I found I have to set the color/greyscale - to colour to actually use the print preview. But it previews in grey scale, can't even see the preview in color. Unless I also go into printer settings and change that to color then get colour in preview. If I set it to grey scale and my printer is a ...

Outboud Receipts; view
Some message came in requesting read reciepts. I accepted the reciept request. Now I have reciepts that will not send and I would like to remove them from the queue. Because they are stuck I cannot send any outgoing mail on this account. Nothing shows up in the outbox but when send/receive(ing) it shows the receipt count and fails. Anybody have an idea how to view/remove these bad outbound receipts? Thanks, Greg get Outlookspy (www.dimastr.com/outspy/) and use it to delete the receipts. They are hidden in the top level of the mailbox or personal folder. -- Diane Poremsky [MVP -...

How do I create a database from an Excel form?
Hello Everybody - I need to see if Excel can be used to create a mailing list database from an Excel form. If so...how do I go about doing this? The list will need to include the name of the company, contact person, and address. Thanks in advance for all of your help! It is greatly appreciated! Here are a couple of links: http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge The first is from David McRitchie and the second is by Beth Melton and Dave Rado. FIRESIGN98 wrote: > > Hello Everybody - > > I need to see if Excel can be used...

Create calendar item/mtg request when people register for webinar?
Hi! I need assistance with figuring out how to generate an Outlook calendar item whenever people register for a webinar or other activity through our website. Is a plug-in available? Ideally, whenever someone signs up for an activity through our website, it would then forward a meeting item to their Outlook calendar. I've seen this done in so many places. "jasovi" <jasovi@discussions.microsoft.com> wrote in message news:EDED7311-4B37-4D4A-A0F5-B77C982D2014@microsoft.com... > Hi! I need assistance with figuring out how to generate an Outlook calendar &...

History/Activity-NTEXT Field
Ok, here's the deal, and lets see if it get broken here when I past it in, haha. 1. Try to view cirtain customer's history and get sql error (soap exception, you know the one that really doesn't tell you anything). 2. Look at customer's "History" by using advanced find to find all the activities. 3. Notice that one of the activities has it's 5000 character NTEXT Field filled with what appears to be a "copy/paste" from an outlook e-mail, to which I can open just fine. 4. out of sheer desperation of trying to figure out what the problem is, I wen...

Showing Outline Symbols
I am using Excel 2007 SP2. After using Data-Subtotals, the outline symbols never appear. I always need to go to Excel Options-Advanced-Show outline symbols if an outline is applied. When I close Excel and use subtotals again, I need to go through this process again. How can I make the outline symbols appear after using Data-Subtotals without going through this process every time? Thank you in advance. Cassie ...

Conditional Formatting for a certain month
Having a bit of an issue at the moment, ive got the Cell for instance A1 Which contains 04-Jan-06 Basically Im after some conditional formatting to say if the month is January, then make the cell blue. Which will then lead to me making the other cells different colours depending on their month, but again, it has to be in this format (DD-MM-YY) Thanks for any help, muchly apreciated Assuming a properly formatted date, formula is =if(month(a1)=1) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Advo" <max_mischief@hotmail.com> wrote in message news:1158151582....

Creating a view with concatenated columns
I have a table with about 20 columns and I am trying to create a view of it that includes about 10 columns. The problem is that in the process, I am trying to concatenate some fields into one field and I am unable to get any thing working. Below is one example of my attempts to tackle this. Any ideas will be highly appreciated. // The following should be considered as pseudo-SQL CREATE function [dbo].[fn_myview] (@CardNo varchar(11)) returns Table as BEGIN declare @FullName as nvarchar(128) (SELECT FirstName, FatherName, GFatherName, FamilyName, Birthdate, Birthplace...

conditional formating #4
I have an annual report spreadsheet comprised of 15 rows (1 - 15) and 5 columns (A - E). In columns "B" and "D" I add numbers from the current year, leaving future year cells blank. I highlight in yellow the blank future cells in columns "B" and "D" by a conditional format "formula is = ISBLANK(B9)". I leave past year cells in "B" and "D" with numbers but no highlight colors. I wish to highlight in green the current year numbers in columns "B" and "D", which will always be a cell above the firs...

empty pivot table fields
How can I force a pivot table to, for example, say that there were no accidents in week 2. At present it says 3 in week 1 then jumps to 2 in week 3. I want week 2 =0 for charting purposes. -thanks Right-click on the Weeks field button. Select Field Settings Add a check mark to 'Show items with no data' Click OK bobf wrote: > How can I force a pivot table to, for example, say that there were no > accidents in week 2. At present it says 3 in week 1 then jumps to 2 in week > 3. I want week 2 =0 for charting purposes. -thanks -- Debra Dalgleish Excel FAQ, Tips & Book...

Display Non Inventory description on Create Purchase Orders screen
Display the item Description for non-inventory items on the Create Purchase Orders screen. Currently, only inventory item descriptions are displayed. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrow...

Delete jpg file based on Access field contents
Hello, What would be the best way to have access delete a bunch of jpg's? I have two tables in mdb format, the main table has 36000 records. When I am done with a record, I move it to a Deleted Record table (using a query and a macro). There are about 1300 of the deleted records that have an associated jpg file. The file size is around 100k, so I have around 130 meg of files cluttering up my laptop. I will never look at the jpg's again. I know it's not a lot of space, but why not have some code get rid of them? So, should I use a button on a regularly accessed Fo...

Viewing 4 different graphs at once
HELP I have a workbook with 4 sheets. Each sheet has a graph on it. I want to show all 4 graphs on one sheet in a window pane environment. But I can't click on the chart and get it to allow me to do a Window - Split Any help would be greatly appreciatied Thank Kell Hi Kelly - Insert a blank worksheet. Right click on each chart in turn, select Location from the pop up menu, select the As Object option, and select the new sheet from the drop down list. When you've gotten all four charts onto the sheet, you can arrange them to your liking. If you hold down the Alt key while ...

ListBox Showing Scroll if 1 item
I have a ListBox that I fill with data from my database. I then set the number of rows attribute to the count so the box is only as large as the number of fields. lbDisplay.Rows = lbDisplay.Items.Count; This works fine. The problem is if the list has 1 item, it shows a dropdown arrow and when you press it, it shows only the one item. If there are 2 or more there is no dropdown arrow. How can I get rid of the dropdown arrow for one entry??? Why does it even show? Thanks, Tom you can't, because it sets the of the number of visible options. 1 = dropdown...

Unable to create mailboxes
Two weeks ago we created and installed a new exchange 2003 server in mixed mode in a 2000 active directory enviroment. We are still using the existing exchange 2000 server as a relay server. Since the upgrade when I create a new account in ADUC I see the exchange email tab populates correctly but nothing shows up under Exchange Advance tab. I also dont see the user name in the GAL. I'm seeing 8270 and 9176 in the event viewier. Does exchange require that I install the system manager on all the DC? Thanks, -Michael ...

Creating MFC child dialogs and windows in DLL.
Hi, I need to write some kind of plug-in, which are implemented as separate dll's. I already wrote a few plugins for this program using MFC and it worked ok, but now I need to create some GUI inside a plugin. I have an interface like that class IModule { public: virtual void ShowConfDialog( HWND hwndParent ) = 0; virtual HWND CreatePanel( HWND hwndParent ) =0; // create child window, position and size will be // controlled by a parent window. } But when creating a child windows in MFC I nee...

Item Allocation Inquiry
Dynamics enables you to allocate inventory on the Parts Information window of Depot Management (part of Field Service). After I have included parts in the Parts Information window, when I then go to the Item Quantities Maintenance screen, I can see that these parts have been added to the Allocated quantities. When I double-click on the Allocated label on the Item Quantities Maintenance window, the Item Allocation Inquiry window opens. However, I do not see the allocations made by Depot Management, I assume, because it is has not been selected in the Module Restrictions box . It has not ...

SQL script to create views
I think I saw a script somewhere on CustomerSource that would crete views with Table Display Names - would anybody know where to find it? Thanks, Mario Do a search for SDK and GenView (or Gen View, and it's part of the SDK). I believe that's what it's called. It's not so much a script as it is a utility. Clint "Mario" <anonymous@discussions.microsoft.com> wrote in message news:2a5ee01c465b4$e345d620$a301280a@phx.gbl... > I think I saw a script somewhere on CustomerSource that > would crete views with Table Display Names - would anybody > know wh...

Why show Enable/Disable macro prompt after I deleted all macros?
Hi, I used to have some macros in my excel which I deleted. Now I still get the Enable/disable macro prompt and the Personal workbook (where the macros were) keep opening- it's irritating, how do i stop both?. I want to keep my security settings as it is. Thanks You probably still have a module or modules in the VB Editor. Go to the VB editor and right click on any modules and select delete, when asked if you want to export data, select no. HTH Regards, Howard "m123" <m123@discussions.microsoft.com> wrote in message news:14116171-55E6-4A25-BF8F-FF...

Create a leaderboard in excel?
I want to create a leaderborad in Excel, wich displays the current position of a contestent. If his score changes (due to data input) his position at the leaderborad changes. How do i do that? I got a list of names and thier value starting at 0 then each round they will get points. I enter the points after each name and the leaderbord auto. shows positions, else i need to sort after each input to have the board up to date. Suppose the players and scores are in columns A:B Player Score Bill 3 Mike 9 Fred 6 John 5 Add an extra column, which will help us deal with dupli...

stacked fields 'can grow' but overlap in report
I have four fields stacked one above the other on a report. They're all marked as 'Can Grow', and sometimes they do. In one case, a multi-line data field pushes the field below out of the way. That's good. But in another case, a multi-line field overlaps with the field below. In every case the fields are standard text boxes. However, I noticed there is a difference in the associated label. The labels that push down with their fields do not have any events available for attaching code. The other labels have events, although I'm not using any events. ...

Views in Outlook
I'm using Outlook 2002 and am having issues with the Views... - I created a custom view while in my "Outlook Today" folder and gave it a unique name. - I apply the view to several folders within the "Outlook Today" folder (is there a way to apply it globally?) - I notice that I need to alter the formatting, so I go to the "Outlook Today" folder and change the formatting to my view. - The folders using that view *DO NOT* change to reflect the current formatting of this view. If I go to define views while in one of these folders I do not see the changes that we...

Create new lookup
How do you create a new lookup field? I want to make a new lookup field on the opportunity page to look up contacts only, so I can selecta primary contact. I assume this has something to do with relationships/mapping. Contacts are already mapped to opportunities, and you can select either an account or a contact for the potential customer. However, I want to create another field to be able to select a contact only. Surely this can be done! You are right in saying that lookups are related to relationships. Set up a relationship, and CRM creates a lookup attribute based upon it. The big pr...

Help in Query with two condition
hello all, I have a table like this: Planner Qty User6 CUT SUB BAM-06 1 CONC Yes No BAM-06 1 CONC No Yes BAM-06 1 TOC Yes No BAM-06 1 TOC No Yes BAM-06 1 TACT Yes No BAM-06 1 TACT No Yes JAXM-06 1 CONC Yes No JAXM-06 1 CONC No Yes JAXM-06 1 TOC Yes No JAXM-06 1 TOC No Yes JAXM-06 1 TACT Yes No JAXM-06 1 TACT No Yes Note: CUT and SUB are check box I want a result as follow: User6 A B C D E F CONC 2 2 1 1 1 1 TACT 2 2 1 1 1 1 TOC 2 2 1 1 1 1 WHERE: A SUM Qty =BAM-06 B SUM Qty =JAXM-06 C SUM Qty =BAM-06 if Cut=YES D SUM Qty =JAXM-06 if Cut=YES E SUM Qty =BAM-06 if SUB=YES F SUM Qty =JAXM-06 i...

if & And condition
Dear friends.. I want to use fromula that if one logical condition is correct want to go another logical condition eg:- If A20=<B20, only I want to check C20=<D20, if A20=<B20 is not true iwant to check D20=<E20, anybody can help me please.. -- shajizz On Sun, 24 Jan 2010 03:44:06 -0500, shajizz <shajizz.5a1d04c@officefrustration.com> wrote: > >Dear friends.. > >I want to use fromula that if one logical condition is correct want to >go another logical condition eg:- If A20=<B20, only I want to check >C20=<D20, if A20=<...