Rookie question about query/View

[bmeyynospamnospam]

I have much MSACCESS expeience and am new to SQL so here goes...

I am trying to create calculated fields in a view, but can't seem to
more complex calculations to work.

I can create a new field which does simple arithmetic on two other
fields and rename it so it becomes part of the new view to be queried
by other views, but when I use more complicated constructs I can't
seem to get them to work.

For starters:

How do you create a new field that has a number based on an IF
construct based on another field.

Example:
field1 has state abbrevs and field 2 should have a "1" if field1="NY".
In access I do field2:IIF(field1="NY",1,0),
How do I accomplish this in MS SQL.
Can it be done in the view?

Thanks,
Brad

[gchavez]

Brad look up the "Case" comand in the books on line, that should do
what you want...

Greg

[bmeyynospamnospam]

Greg,
Thanks for the answer,
I agree that the case command is the best for my example, but that
example was just a way of giving an idea of what I can't get to work.
Can the case command be entered in the view grid?
Does it have to be entered in the sql window?

I am familiar with the case command and others, but it's not clear to
me where I enter these commands.
The IF construct should work also (even though it's not the easiest or
most efficient for the example I gave), but I could not get it to
work.

In MSACCESS you enter the iif and other functions right in the grid.

I feel like I'm doing something real basic that is wrong, like
entering it in the wrong area. I have tried entering commands other
than simple equations in the grid and in the sql window with various
error messages as my ultimate result.

Any ideas.
THanks,
Brad

"gc*****@aspenres.com" <gc*****@aspenres.com> wrote:

Brad look up the "Case" comand in the books on line, that should do
what you want...

Greg

[Stu]

In MS Access, write a qury that uses an IIF command, and then flip to
the SQL view from the dropdown. This should show you the SQL syntax
for entering a command; CASE is similar.

SELECT field1, field2=CASE WHEN field1='NY' THEN 1 ELSE 0 END
FROM table
Hope that helps,
Stu

[bmeyynospamnospam]

Thanks Stu,
Can I just put the case statement into the grid in MS SQL?
I can't seem to get th egrid to work, or should I not be using the
grid.

Brad

"Stu" <st**************@gmail.com> wrote:

In MS Access, write a qury that uses an IIF command, and then flip to
the SQL view from the dropdown. This should show you the SQL syntax
for entering a command; CASE is similar.

SELECT field1, field2=CASE WHEN field1='NY' THEN 1 ELSE 0 END
FROM table
Hope that helps,
Stu

[Stu]

If you're talking about the grid in Enterprise Manager, try to wean
yourself off of that as quickly as you can. It limits your flexibility
in writing clean SQL statements. I don't use it, and so I'm not sure
how to write a CASE statement in it, but I belive you can write it
directly in it.

You may want to to try writing a simple select statement using the
grid, and then adding the case statement to the SQL statement, and then
flip back to the grid to see how it gets interpreted.

Stu

[bmeyynospamnospam]

Thanks Stu,
My experimentation has found exactly that.
The grid will not support case statements, so you have to write them
directly in the sql window.
I was able to get my statements to work pretty well once I figured out
how the landscape was different than access.

Access does hold your hand a bit more and it's going to take some time
to get used to writing from scratch.

Thanks again.
Brad
"Stu" <st**************@gmail.com> wrote:

If you're talking about the grid in Enterprise Manager, try to wean
yourself off of that as quickly as you can. It limits your flexibility
in writing clean SQL statements. I don't use it, and so I'm not sure
how to write a CASE statement in it, but I belive you can write it
directly in it.

You may want to to try writing a simple select statement using the
grid, and then adding the case statement to the SQL statement, and then
flip back to the grid to see how it gets interpreted.

Stu

[Hugo Kornelis]

On Wed, 15 Jun 2005 02:16:11 GMT, bm***************@bmegroup.com wrote:

Thanks Stu,
My experimentation has found exactly that.
The grid will not support case statements, so you have to write them
directly in the sql window.

Hi Brad,

I'd go one step further, and tell you not to use Enterprise Manager for
writing queries at all. Instead, use Query Analyzer (a tool much better
suited for the job) and use EM for what it is good at (managing backups,
restores, maintenance schedules, replication, etc.)

http://www.aspfaq.com/show.asp?id=2455

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)