MS Visual Studio Query Designer

Is it possible to write an IF statement in Visual Studios Query Designer in the column field?
I've tried a number of iterations that would have worked in MS Access but not luck in Query Designer.
thank you
ShawnGrayAsked:
Who is Participating?
 
ValentinoVBI ConsultantCommented:
Does this have to be written in the SQL pane or can it be written in the Criteria pane?

You can write that whole CASE statement (starting with CASE, ending with END) in the Column field.

iif([DistNet]>10000,"big","small")

CASE WHEN DistNet > 10000 THEN 'big' ELSE 'small' END
{edit: replaced double quotes with single quotes}

Please note: the CASE syntax in this statement is different from the one Jim posted.  Jim's example is known as a "simple case expression" while mine is called a "searched case expression".  More details: CASE (Transact-SQL)

I'd also like to advice you to learn the basics of SELECT statement writing.  Once you've gotten the hang of it you'll never use that Query Designer again.  I always write my queries in the Management Studio and paste it into the Dataset Query window when it's fully functional.  Just my 2c, up to you of course to decide if it's worth the effort :)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes, the T-SQL equivalent of Access Iif is CASE

SELECT CASE Id WHEN 1 THEN 'banana' WHEN 2 THEN 'orange' ELSE 'grape' END
0
 
ShawnGrayAuthor Commented:
Does this have to be written in the SQL pane or can it be written in the Criteria pane?

Table: AdvBase
Field: DistNet

Wanting sql equivalent:
NewField: iif([DistNet]>10000,"big","small")
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ShawnGrayAuthor Commented:
ValentinoV
Thanks; the quotes "" are being replaced by Query Designer with brackets [].
Execution error: "Invalid column name 'big', Invalid column name 'small'
0
 
ShawnGrayAuthor Commented:
ValentinoV
Disregard last question.  found I needed to define the expression with table.field
Really appreciate the help and the resource.
0
 
ValentinoVBI ConsultantCommented:
Thanks; the quotes "" are being replaced by Query Designer with brackets [].

Ooh, just noticed that I mistakingly used double quotes while they should have been single ones.  In T-SQL, double quotes around a string are equivalent to the square brackets but this means SQL Server thinks you're typing an object name, such as a column or a table.  In your case I think you just wanted hard-coded strings, right?

I'll edit my response above to correct this to avoid confusion for any other readers...

Good to hear you got it working though!
0
 
ShawnGrayAuthor Commented:
Very good. Thanks for the education.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.