Select query one field cconditional

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select query one field cconditional

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-04-17 : 16:05:25

Is it possible to have a select query except the order amount which is conditional based on 3 field in the same table.


select 'Order Details' as Type, Ordid as ID, Ordno as ref_trans, 

(
If FinalCostOrdAmt available then choose Final_OrderAmt

elseif FairCostOrdAmt available then choose FairCostOrdAmt

elseif RoughCostOrdAmt available then choose RoughCostOrdAmt
) as Order_Amount


from tableOrders where OrderID=@OrdID

Thank you very much for the helpful info.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-17 : 16:10:41

Do you mean accept?

Try this

select 'Order Details' as Type, Ordid as ID, Ordno as ref_trans,
coalesce(Final_OrderAmt,FairCostOrdAmt,RoughCostOrdAmt)
as Order_Amount
from tableOrders where OrderID=@OrdID

An infinite universe is the ultimate cartesian product.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-04-17 : 17:22:30

Is it possible to check if 0 and null both.

If it is 0 it should'nt pick that.

the problem is
Final_OrderAmt = 0

FairCostOrdAmt = 0

RoughCostOrdAmt = 120

It should pick RoughCostOrdAmt = 120.

Really appreceate for the help.

quote:
Originally posted by cat_jesus

Do you mean accept?

Try this

select 'Order Details' as Type, Ordid as ID, Ordno as ref_trans,
coalesce(Final_OrderAmt,FairCostOrdAmt,RoughCostOrdAmt)
as Order_Amount
from tableOrders where OrderID=@OrdID

An infinite universe is the ultimate cartesian product.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-17 : 22:44:50

select 'Order Details' as Type, Ordid as ID, Ordno as ref_trans,
case when Final_OrderAmt > 0 then Final_OrderAmt
when FairCostOrdAmt > 0 then FairCostOrdAmt else RoughCostOrdAmt end as Order_Amount
from tableOrders where OrderID=@OrdID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources