"If" statement in a SQL query???

[hadoob024]

I just came across this query and am not sure what's going on in this particular section of the SQL statement.  I've never seen "if" statements in a SQL query.  Tried googling this, but no example that I found matched what I was looking for.  Here's the relevant part of the query.

 

select mfg.name as mfgname,ap.manufacturer_id,ap.po_id,mfg.req_facacct as ReqAcctNum,ifm.mfg_acct_num as FacNum,mfg.acctnumber,
if(fac.dtmcontract = 1 and ap.pay_facility = 0 and ap.facility_orders <> 'FacReorder','M',
if(fac.dtmcontract = 1 and ap.pay_facility = 0 and ap.facility_orders = 'FacReorder' and ap.inventorytype = 'Rep Delivery','M',
if(fac.dtmcontract = 0 and mfg.direct_bill = 1 and (ap.inventorytype = 'Rep Delivery' or ap.inventorytype = 'Drop Ship'),'M',
if(fac.dtmcontract = 0 and mfg.direct_bill = 1 and (ap.inventorytype = 'Purchased' or ap.inventorytype = 'Consigned'),'C',
if(fac.dtmcontract = 1 and ap.pay_facility = 0 and ap.facility_orders = 'FacReorder' and ap.inventorytype <> 'Rep Delivery','Z','C')))))
as potype
from actparts ap
.
.
.

[Mchl]

The IF() function is pretty easy:

IF(condition,returnThisIfConditionTrue,returnThisIfConditionFalse)

 

However, the person who created a query above clearly decided to confuse everyone else by nesting it multiple times.

It works like this

if (ac.dtmcontract = 1 and ap.pay_facility = 0 and ap.facility_orders <> 'FacReorder') {
  return 'M';
} else if(fac.dtmcontract = 1 and ap.pay_facility = 0 and ap.facility_orders = 'FacReorder' and ap.inventorytype = 'Rep Delivery') {
  return 'M';
} else if (fac.dtmcontract = 0 and mfg.direct_bill = 1 and (ap.inventorytype = 'Rep Delivery' or ap.inventorytype = 'Drop Ship') {
  return 'M';
} else if(fac.dtmcontract = 0 and mfg.direct_bill = 1 and (ap.inventorytype = 'Purchased' or ap.inventorytype = 'Consigned') {
  return 'C';
} else if(fac.dtmcontract = 1 and ap.pay_facility = 0 and ap.facility_orders = 'FacReorder' and ap.inventorytype <> 'Rep Delivery') {
  return 'Z';
} else {
  return 'C';
}

 

Clearly, this could be rewritten to something more digestible.

 

 

 

 

[hadoob024]

AAAaaahhhhhh....  <light bulb turning on>  got it.

 

Thanks a ton!  Makes more sense now.

[James25]

yes the answer clarified the situation