hadoob024 Posted June 10, 2010 Share Posted June 10, 2010 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 More sharing options...
Mchl Posted June 10, 2010 Share Posted June 10, 2010 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. Link to comment https://forums.phpfreaks.com/topic/204424-if-statement-in-a-sql-query/#findComment-1070521 Share on other sites More sharing options...
hadoob024 Posted June 10, 2010 Author Share Posted June 10, 2010 AAAaaahhhhhh.... <light bulb turning on> got it. Thanks a ton! Makes more sense now. Link to comment https://forums.phpfreaks.com/topic/204424-if-statement-in-a-sql-query/#findComment-1070524 Share on other sites More sharing options...
James25 Posted June 14, 2010 Share Posted June 14, 2010 yes the answer clarified the situation Link to comment https://forums.phpfreaks.com/topic/204424-if-statement-in-a-sql-query/#findComment-1071737 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.