Jump to content

"If" statement in a SQL query???


hadoob024

Recommended Posts

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
.
.
.

Link to comment
https://forums.phpfreaks.com/topic/204424-if-statement-in-a-sql-query/
Share on other sites

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.

 

 

 

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.