?
Solved

more quick points sql server -- iif

Posted on 2005-03-04
12
Medium Priority
?
385 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

777 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