Promar 32" Deluxe Hoop Net
|SQL||SELECT DISTINCT a.style_id, max(CASE WHEN m.description IS NULL OR RTRIM(LTRIM(m.description)) = '' THEN a.web_desc ELSE m.description END) as realname, sum(c.qoh+c.qc) as overallqoh, max(a.picture_id) as picture_id, max(a.of8) as of8, max(b.sku_id) as sku_id, max(b.sku_of7) as sku_of7, max(b.scale_entry_id) as scaleid, max(b.attr2_entry_id) as attr2id, max(b.attr1_entry_id) as attr1id, max(a.style) as style, max(r.package_id) as usepackage, max(a.web_style) as webStyle, max(m.capacity) as capacity, max(m.webstyle_sort) as webstyle_sort, max(a.NON_INVT) as NON_INVT FROM tb_styles a LEFT JOIN tb_skus b WITH (NOLOCK) ON (a.style_id = b.style_id) LEFT JOIN tb_sku_buckets c WITH (NOLOCK) ON (b.sku_id = c.sku_id AND NOT (c.qoh < 1 AND a.of8 in (?,?,?,?,?,?,?,?)) AND NOT (c.qoh < 1 AND a.of8='INACTIVE') AND c.store_id in(49,90,4) ) LEFT JOIN web_product_information m WITH (NOLOCK) ON (a.style_id = m.style_id) LEFT JOIN dbo.tb_inet_names f WITH (NOLOCK) on (f.orig_text = a.brand and f.field_name='BRAND') LEFT JOIN tb_package r WITH (NOLOCK) on (r.sku_id=b.sku_id) INNER JOIN web_all_products wb WITH (NOLOCK) ON (wb.style_id = a.style_id) WHERE a.web_style = 'NE-102' AND (upper(f.web_text) = 'PROMAR' or upper(f.orig_text) = 'PROMAR' or upper(a.brand) = 'PROMAR') GROUP BY a.style_id,a.of8 HAVING (sum(c.qoh+c.qc) > 0 OR a.of8='STK') ORDER BY max(m.webstyle_sort), a.style_id|
Unsurpassed in quality and proven to catch more, these are the nets that anglers have asked for by name. These patented exclusive nets are ideal for catching crabs, lobsters, or landing fish from a boat, pier, bridge, or other overhanging structure. The extra deep design and spook-proof polyethylene netting is extremely efffective in preventing the escape of your catch. Zinc galvanized steel construction prevents rust on upper and lower rings.
Cancer and Reproductive Harm
No reviews have been written for this product.
Be the first one! – Write a Review
$19.99 to $29.99
$119.99 to $159.99
$39.99 to $46.99
$7.99 to $9.99
$19.99 to $32.49