iif question

[scott1742]

My query is returning incorrect column expression.
it is centered around the iif statement area. Is my syntax in error?
I can pull the iif statement and the query runs fine.

SELECT '687547' AS 'Divison ID', '018502' AS 'Customer ID', po_hdr.po_no, po_hdr.date_created AS 'po date', po_line.qty_ordered AS 'Quantity', po_line.unit_of_measure AS 'UOM', po_line.required_date AS 'Requested ship date', inventory_supplier.supplier_part_no, address.mail_address1 AS 'ANSI SCAC',

iif(po_hdr.location_id = 101 and len(po_hdr.ship2_name) = 0, 05,
iif(po_hdr.location_id = 104 and len(po_hdr.ship2_name) = 0, 02,
iif(po_hdr.location_id = 102 and len(po_hdr.ship2_name) = 0, 01,
iif(po_hdr.location_id = 105 and len(po_hdr.ship2_name) = 0, 04,
iif(po_hdr.location_id = 107 and len(po_hdr.ship2_name) = 0, 03,
iif(po_hdr.location_id = 101 and len(po_hdr.ship2_name) = 0, 05,
"DS")))))) as 'ship code',

po_hdr.ship2_name, po_hdr.ship2_add1, po_hdr.ship2_add2, po_hdr.ship2_city, po_hdr.ship2_state, po_hdr.ship2_zip, po_hdr.ship2_country, po_hdr.po_desc AS 'free form text', inv_mast.item_id
FROM Prophet21_Live.dbo.address address, Prophet21_Live.dbo.inv_mast inv_mast, Prophet21_Live.dbo.inventory_supplier inventory_supplier, Prophet21_Live.dbo.po_hdr po_hdr, Prophet21_Live.dbo.po_line po_line
WHERE po_line.po_no = po_hdr.po_no AND po_line.inv_mast_uid = inventory_supplier.inv_mast_uid AND po_hdr.supplier_id = inventory_supplier.supplier_id AND po_line.inv_mast_uid = inv_mast.inv_mast_uid AND po_hdr.carrier_id = address.id AND ((po_hdr.supplier_id=$10987) AND (po_hdr.date_created>getdate()-60) AND (po_hdr.delete_flag='n') AND (po_line.complete='n') OR (po_hdr.supplier_id=$45094) AND (po_hdr.date_created>getdate()-60) AND (po_hdr.delete_flag='n') AND (po_line.complete='n'))

 

[ranman256]

Instead of a lot of IIFs, make a lookup table, return the 1 result.