Problems with COALESCE - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Problems with COALESCE

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-03-30 : 08:15:39

I am having trouble using coalesce and dont understand why?

SELECT
o.order_no,
ol.line_no,
ol.part_no,
o.cust_code,
ol.ordered,
ol.shipped,
o.ship_to,
o.sch_ship_date,
o.req_ship_date,
FROM orders o INNER JOIN ord_list ol ON o.order_no = ol.order_no AND o.ext = ol.order_ext INNER JOIN armaster ar ON o.cust_code = ar.customer_code
WHERE o.order_no = COALESCE(@OrderNo, o.order_no) AND ol.part_no = COALESCE(@PartNo, ol.part_no) AND o.ship_to = COALESCE(@ShipTo, o.ship_to) AND ar.address_name = COALESCE(@CustomerName, ar.customer_code)

If I run this with no parameters I get nothing? If I do enter a value for @CustomerName I get the results expected for just supplying that one value.

I thought that using coalesce in this way removed the need for a load of if/else and repeated sql? Have I gone wrong somewhere?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-30 : 08:23:58

quote:
ar.address_name = COALESCE(@CustomerName, ar.customer_code)

or should it be

ar.address_name = COALESCE(@CustomerName, ar.address_name)

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-03-30 : 08:39:56

Oh crap!

The phrase "wood....trees" comes to mind!

Thnx :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources