Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

more quick points sql server -- iif

Posted on 2005-03-04
12
Medium Priority
?
386 Views
Last Modified: 2009-07-29

I want to create a formula field.

if area code is blank then formula should be
[LocalNumber]

if area code is not blank then formula should be
[AreaCode] + '-' + [LocalNumber]

How do I do this in sql server formula field?

0
Comment
Question by:kamleshmistry
12 Comments
 

Author Comment

by:kamleshmistry
ID: 13460772
p.s.,

vb.net supports iif

iif(  condtion,  true part,  false part)

where true part is an expression
and
false part is an expression


0
 
LVL 23

Assisted Solution

by:adathelad
adathelad earned 200 total points
ID: 13460787
use CASE

CASE WHEN ISNULL([AreaCode], '') = '' THEN [LocalNumber] ELSE [AreaCode] + '-' + [LocalNumber] END
0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 200 total points
ID: 13460799
SELECT
     CASE
          WHEN Len([area code]) = 0 THEN [LocalNumber]
          ELSE [AreaCode] + '-' + [LocalNumber]
     END AS PhoneNumber
FROM ...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kamleshmistry
ID: 13460871
Are you all sure?

By the way, what I am doing is in the design table section, I am creating a new column.

I create a new column, and then in the Formula textbox entry, I start typing a formula...  

It is a single line text-box...
0
 

Author Comment

by:kamleshmistry
ID: 13460909
See above comment,

When I enter a simple forumla like this:

CASE WHEN TRUE Then '-' ELSE '' END

I get the error that the formula is not a valid formula

The above is exactly what I typed into the Formula text box in the columns tab for a new column in the design table screen....



0
 

Author Comment

by:kamleshmistry
ID: 13460985
P.S.

I AM NOT DOINT A SQL SELECT


0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13461138
P.S.

THEN WHY ARE YOU POSTING IN THE SQL TA...this sounds like a vb.net question

0
 

Author Comment

by:kamleshmistry
ID: 13461262
no, no...
not a vb.net question.

a sql server question.
create a new database table and create a new field.

there is a text box for "formula" to create a formula field.
I believe the way this works is that no storage is used for formula fields.  It is evaluated and displayed, and can be made available to dataset, but does not use up any hard disk storage...

Question is related to Sql server...
0
 
LVL 4

Accepted Solution

by:
BaldProgrammer earned 600 total points
ID: 13461342
Try this:

Can Area_Code is null or is it always going to be "empty string"?  You can try this:

COALESCE([Area Code] + '-', '') + [LocalNumber]
0
 
LVL 7

Assisted Solution

by:Lori99
Lori99 earned 600 total points
ID: 13462470
Or, if area code contains nulls you can use this:

(isnull(([AreaCode] + '-'),'') + [LocalNumber])
0
 
LVL 13

Assisted Solution

by:KarinLoos
KarinLoos earned 200 total points
ID: 13471566
refer books on line (section create table) , what are you trying to create is called a computed column
and the case statement is valid within a computed column only not like you  used it
however valid examples were given above.
0
 
LVL 8

Assisted Solution

by:Julianva
Julianva earned 200 total points
ID: 13475064
What you should be doing is creating a check constraint on your columns.




0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

564 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