sql server programming: Newbie SQL programming question -- sql server programming

Newbie SQL programming question

graham
7/22/2003 11:22:31 PM

sql server programming: Try to write a sql statement that I didn't have any
problem with in Access.

Table: Test
Fields: Rec_Type, Amount

Need to write an "IF" Function that says something like IF
Rec_Type = 1 then Amount else 0

BUT, I can't have the Rec_Type in the Select Statement.

So I tryed,

Select Case when test.rec_type=1 then test.amount else 0
end
From dbo.test

But I get an error stating that I can't use Rec_Type
because it is not contained in the Select Statement.

In access this wasn't a problem

Any help would be greatly appreciated

Re: Newbie SQL programming question

Greg Obleshchuk
7/23/2003 4:33:23 PM

Graham,
Try this
begin transaction
create table Test (Rec_type int, Amount money)
insert into test(Rec_type ,Amount) values(1,34)
insert into test(Rec_type ,Amount) values(1,324)
insert into test(Rec_type ,Amount) values(1,314)
insert into test(Rec_type ,Amount) values(0,34)
insert into test(Rec_type ,Amount) values(0,324)
insert into test(Rec_type ,Amount) values(0,314)

select case rec_type when 1 then amount else 0 end AM from test

rollback tran

--
I hope this helps
regards
Greg O MCSD
SQL Scribe Documentation Builder
Document any SQL server database in minutes
Programmers love it, DBA dream of it
AGS SQL Scribe download a 30 day trial today
http://www.ag-software.com/ags_scribe_index.asp

[quoted text, click to view]