Hello all,

I know how to use MS Access's 'IIf' statement to test one field and
get different results in another field. Is there some way of doing
the same thing in SQL Server's Enterprise Manager and store it as a
view? My SQL Server Admin class instructor showed me the UNION query,
but I can't store it as a view:

SELECT CurBal, CurRate, Payment, MinorCode, MPS, MEDate, IntInc,
OrigDate, AcctID, ScheduledPmt, 0 AS SchedPmt
FROM dbo.Loans_Data
WHERE (ScheduledPmt < 0)
UNION
SELECT CurBal, CurRate, Payment, MinorCode, MPS, MEDate, IntInc,
OrigDate, AcctID, ScheduledPmt, ScheduledPmt AS SchedPmt
FROM dbo.Loans_Data
WHERE (ScheduledPmt >= 0)

Is there some way of accomplishing the query above and store it as a
view?

Thanks for any help anyone can provide,

Conan Kelly

Try:

SELECT CurBal, CurRate, Payment, MinorCode, MPS, MEDate, IntInc,
OrigDate, AcctID, ScheduledPmt, case when ScheduledPmt > 0 then ScheduledPmt
else 0 end AS SchedPmt
FROM dbo.Loans_Data

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hello all,

I know how to use MS Access's 'IIf' statement to test one field and
get different results in another field. Is there some way of doing
the same thing in SQL Server's Enterprise Manager and store it as a
view? My SQL Server Admin class instructor showed me the UNION query,
but I can't store it as a view:

SELECT CurBal, CurRate, Payment, MinorCode, MPS, MEDate, IntInc,
OrigDate, AcctID, ScheduledPmt, 0 AS SchedPmt
FROM dbo.Loans_Data
WHERE (ScheduledPmt < 0)
UNION
SELECT CurBal, CurRate, Payment, MinorCode, MPS, MEDate, IntInc,
OrigDate, AcctID, ScheduledPmt, ScheduledPmt AS SchedPmt
FROM dbo.Loans_Data
WHERE (ScheduledPmt >= 0)

Is there some way of accomplishing the query above and store it as a
view?

Thanks for any help anyone can provide,

Conan Kelly