coalesce with a condition?-CodePudding

I think I need help with a query, I'm probably overcomplicating things mentally, but how would I change this date_add

DATE_ADD(ceorder.orderdate_dt,INTERVAL profile.maxexpiration_i DAY) as maxExpirationDate
to use

course.override_expiration_days_i

if not null, instead of

profile.maxexpiration_i

but only if the variable

course.producttype_vc

equals 'Electronic Book' so kind of like a coalesce but with an extra condition?

the entire query for context

 private String SELECT_EXTENSION = "select ceordereditem.expiration_dt as currentExpirationDate, DATE_ADD(ceorder.orderdate_dt,INTERVAL profile.maxexpiration_i DAY) as maxExpirationDate, coursedetail.price_f as itemPrice, ceordereditem.approvalnumber_vc as approvalNumber, extension_percent_b as percentage, extension_price_f as extensionPrice, extension_interval_days_i as extensionDays "  
     "from coursedetail, ceordereditem, ceorder, profile "  
     "where ceordereditem.ceorder_id_i = ceorder.id_i and coursedetail.profile_id_i = profile.id_i and ceordereditem.coursedetail_id_i = coursedetail.id_i and ceordereditem.id_i = ?";

CodePudding user response：

coalesce is just a shortcut for a case|when block. If you want to do something more complex than just returning a fallback value when null, you can use a case expression. Example:

CASE  
WHEN (yourDataField IS NOT NULL) THEN yourDataField
WHEN (yourDataField IS NULL AND course.producttype_vc = "Electronic Book") THEN course.override_expiration_days_i
ELSE somethingElseHere
END

CodePudding user response：

You can use CASE WHEN THEN END in T-sql, like:

select ceordereditem.expiration_dt as currentExpirationDate, CASE WHEN course.override_expiration_days_i IS NOT NULL AND course.producttype_vc = 'Electronic Book' 
THEN DATE_ADD(ceorder.orderdate_dt,INTERVAL course.override_expiration_days_i DAY) 
ELSE DATE_ADD(ceorder.orderdate_dt,INTERVAL profile.maxexpiration_i DAY) END as maxExpirationDate, coursedetail.price_f as itemPrice, ceordereditem.approvalnumber_vc as approvalNumber, extension_percent_b as percentage, extension_price_f as extensionPrice, extension_interval_days_i as extensionDays "  
     "from coursedetail, ceordereditem, ceorder, profile "  
     "where ceordereditem.ceorder_id_i = ceorder.id_i and coursedetail.profile_id_i = profile.id_i and ceordereditem.coursedetail_id_i = coursedetail.id_i and ceordereditem.id_i = ?