BEGIN
INSERT INTO oeordlin_sql (ord_type,
ord_no,
line_seq_no,
item_no,
loc,
pick_seq,
item_desc_1, 
qty_ordered,
qty_to_ship, 
unit_price,
discount_pct,
request_dt,
qty_bkord,
qty_return_to_stk, 
bkord_fg,
uom,
uom_ratio,
unit_cost,
unit_weight, 
comm_calc_type,
comm_pct_or_amt,
promise_dt, 
tax_fg,
stocked_fg, 
controlled_fg,
select_cd,
tot_qty_ordered, 
tot_qty_shipped, 
orig_price,
post_to_inv_qty,
posted_to_inv,
tot_qty_posted,
qty_allocated,
components_alloc,
bin_fg,
ser_lot_cd,
line_type,
prod_cat,
end_item_cd,
req_ship_dt, 
qty_from_stk,
shipped_dt,
billed_dt, 
prc_cd_orig_price,
cus_no,
tax_amt,
qty_bkord_fg,
line_no, 
mfg_method,
conf_pick_dt,
item_release_no,
total_cost,
rma_seq, 
posted_unit_cost,
recalc_sw)
SELECT o.ord_type,
o.ord_no,
9999,
'RBATE',
'BRY', 
'99',
'OE REBATE', 
-1, 
-1,
SUM(ext_rebate),
0,
getdate(),
0,
0,
'Y',
'EA',
1,
0,
0,
'A',
0,
getdate(),
'N',
'N',
'N',
'S',
-1,
0,
0,
0,
0,
0,
0,
0,
'N',
'N',
'I',
'REB',
'P',
getdate(), 
0,
getdate(),
getdate(), 
0,
o.cus_no,
0,
' ',
9999,
'OE',
getdate(),
'       1',
0,
0,
0,
'Y'
FROM ORDERREBATE o 
where o.ext_rebate <> 0 AND ORD_TYPE <> 'C'
GROUP BY o.ord_type,
o.ord_no, o.cus_no
END

quote:

---

Originally posted by Vack

How do I make the script below only insert if a line_no = 9999 does not exist in the oeordlin_sql?

IF NOT EXISTS(SELECT 1 FROM oeordlin_sql WHERE line_no=9999)
BEGIN
INSERT INTO oeordlin_sql (ord_type,
ord_no,
line_seq_no,
item_no,
loc,
pick_seq,
item_desc_1, 
qty_ordered,
qty_to_ship, 
unit_price,
discount_pct,
request_dt,
qty_bkord,
qty_return_to_stk, 
bkord_fg,
uom,
uom_ratio,
unit_cost,
unit_weight, 
comm_calc_type,
comm_pct_or_amt,
promise_dt, 
tax_fg,
stocked_fg, 
controlled_fg,
select_cd,
tot_qty_ordered, 
tot_qty_shipped, 
orig_price,
post_to_inv_qty,
posted_to_inv,
tot_qty_posted,
qty_allocated,
components_alloc,
bin_fg,
ser_lot_cd,
line_type,
prod_cat,
end_item_cd,
req_ship_dt, 
qty_from_stk,
shipped_dt,
billed_dt, 
prc_cd_orig_price,
cus_no,
tax_amt,
qty_bkord_fg,
line_no, 
mfg_method,
conf_pick_dt,
item_release_no,
total_cost,
rma_seq, 
posted_unit_cost,
recalc_sw)
SELECT o.ord_type,
o.ord_no,
9999,
'RBATE',
'BRY', 
'99',
'OE REBATE', 
-1, 
-1,
SUM(ext_rebate),
0,
getdate(),
0,
0,
'Y',
'EA',
1,
0,
0,
'A',
0,
getdate(),
'N',
'N',
'N',
'S',
-1,
0,
0,
0,
0,
0,
0,
0,
'N',
'N',
'I',
'REB',
'P',
getdate(), 
0,
getdate(),
getdate(), 
0,
o.cus_no,
0,
' ',
9999,
'OE',
getdate(),
'       1',
0,
0,
0,
'Y'
FROM ORDERREBATE o 
where o.ext_rebate <> 0 AND ORD_TYPE <> 'C'
GROUP BY o.ord_type,
o.ord_no, o.cus_no
END

---

quote:

---

Originally posted by Vack

Thanks,

What does the SELECT 1 do?

---