Lucky Craft LV Max500 Lucky Vibration 3/4oz
Out Of Stock
|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) ) 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 = 'LV500-052' AND (upper(f.web_text) = 'LUCKY CRAFT' or upper(f.orig_text) = 'LUCKY CRAFT' or upper(a.brand) = 'LUCKY CRAFT') 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|
For professional fishermen and weekenders alike, the LV series will allow fast coverage of water especially when fish are difficult to locate. The lure can be cast long distance and retrieved at depths of 12-15 feet. The LV series are equipped with brass and glass rattles that expose bass to a unique sound unlike other rattling lures. The natural shape coupled with the tight vibrating wobble action makes this 3-inch, 3/4oz. bait a must for your tackle box.
Cancer and Reproductive Harm
No reviews have been written for this product.
Be the first one! – Write a Review
$4.79 to $4.99
$4.97 to $10.69
$4.79 to $4.89
$10.97 to $16.69
$9.29 to $10.99