SELECT distinct (b.attr1) as origAttr1, max(CASE WHEN b.attr1_alias IS NULL OR RTRIM(LTRIM(b.attr1_alias)) = '' THEN b.attr1 ELSE b.attr1_alias END) as attr1, max(a.sku_id) as sku_id, max(b.attr1_entry_id) as attr1_entry_id, sum(e.qoh+e.qc) as qoh, max(b.attr1_order) as sort_order, max(c.attr1_hex) as hex,max(d.attr1_fam) as fam,max(z.non_invt) as non_invt
FROM tb_skus a
LEFT JOIN tb_attr1_entries b WITH (NOLOCK) ON (b.attr1_entry_id = a.attr1_entry_id)
LEFT JOIN tb_sku_buckets e WITH (NOLOCK) ON (e.sku_id = a.sku_id)
LEFT JOIN tb_attr1_hexes c WITH (NOLOCK) ON (b.attr1=c.attr1)
LEFT JOIN tb_attr1_families d WITH (NOLOCK) ON (c.attr1_fam_id = d.attr1_fam_id)
LEFT JOIN tb_styles z on (z.style_id = a.style_id)
WHERE (a.sell_web not in ('N','NO') or (a.sell_web is null)) and a.style_id=?
AND e.store_id in(1,2,3)
GROUP BY b.attr1
ORDER BY max(b.attr1_order), max(b.attr1_entry_id)
Nike Phantom Scorpion Elite Dynamic Fit FG
Firm-Ground Soccer Cleat ATTACK WITH PRECISION AND AGILITY.
Unlock your skills in the Nike Phantom Scorpion Elite Dynamic Fit FG. The data-informed design is engineered for precise touch and traction on short-grass fields. Scorpion details are inspired by Nike’s “Secret Tournament” advertising campaign that featured 24 global soccer stars.
Innovative texture is strategically placed based on athlete testing of player movements. It's thicker and denser at the instep and toe for precise touch when shooting, passing and dribbling. Lightweight Agility
Hyperquick system combines a tuned chassis with an open-arch plate for lightweight stability. The updated stud configuration creates dynamic traction for quick changes of direction. Weather-Ready Design
All Conditions Control (ACC) technology is designed for reliable touch in wet and dry conditions. It allows the surface to maintain the same level of friction for better control of the ball. Seamless Support
Flyknit construction and a Dynamic Fit collar wrap your foot in stretchy yarns for a secure, sock-like fit.
Off-center lacing creates a clean strike zone.
For use on slightly wet, short-grass fields
Not intended for use as Personal Protective Equipment (PPE)