If Not Exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

If Not Exists

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-20 : 10:07:00

I'm trying to insert a record into table oeordlin_sql where an ord_no does not have line_no 9999.

Dealing with two tables. oeordlin_sql and orderrebate. I need to pull a few values from the orderrebate when I'm inserting into the oeordlin.

Do I need to link my oeordlin to the orderrebate at the bottom of my script????


BEGIN
IF NOT EXISTS(SELECT 1 FROM oeordlin_sql WHERE line_no=9999)
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,
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 10:12:03

yup you need to depends on what all values you need to insert and how you want to insert. do you want to add as rows or add as columns along with current values?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-20 : 10:19:34

I'm adding a new row to the oeordlin. I'm populating the default values and need to pull the ord_type, ord_no and cus_no from the orderrebate table.

I've tried the following. The script runs fine but it's not doing the insert. I've commented out the group by because I'm not longer calculating sum(ext_rebate) from the order rebate table.
BEGIN
IF NOT EXISTS(SELECT 1 FROM oeordlin_sql WHERE line_no=9999)
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,
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 join oeordlin_sql on o.ord_type = oeordlin_sql.ord_type and
o.ord_no = oeordlin_sql.ord_no
where o.ext_rebate <> 0 AND o.ORD_TYPE <> 'C'
--GROUP BY o.ord_type,
--o.ord_no, o.cus_no
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 10:23:51

i cant see any fields from ORDERREBATE being used in select list. what all fields are you trying to insert?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-20 : 10:26:46

First two in the list
o.ord_type,
o.ord_no,
and
o.cus_no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 10:34:00

quote:
Originally posted by Vack

First two in the list
o.ord_type,
o.ord_no,
and
o.cus_no

then whats the purpose of joining to oeordlin_sql ?isnt this enough

BEGIN
IF NOT EXISTS(SELECT 1 FROM oeordlin_sql WHERE line_no=9999)
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,
0,
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'
END

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-20 : 10:37:37

Nothing gets inserted. The only way I can get a record to insert with the code above is if I comment out the IF NOT EXISTS Statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 10:50:06

quote:
Originally posted by Vack

Nothing gets inserted. The only way I can get a record to insert with the code above is if I comment out the IF NOT EXISTS Statement.

simple. that means you already have a record in table with line_no=9999. then how can you insert another one as IF NOT EXISTS fails?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-20 : 10:57:26

right. There may be orders in the oeordlin_sql that already have a record with line_no=9999. Those are the orders I do not want another line inserted. Maybe this will explain what I'm trying to do better.

oeordlin: orderrebate:
ord_type ord_no line_no item_no ord_type ord_no line_no item_no
O 123 1 ABC O 123 1 ABC
O 123 9999 RBATE O 765 1 ABC
O 765 1 ABC

In the records above I want to insert a record for order 765 since it does not have an item_no RBATE. Ord_no 123 already has a rebate record so I do not want to try and insert another one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 11:03:39

then i think what you want is this

BEGIN
IF NOT EXISTS(SELECT 1 FROM oeordlin_sql o
              INNER JOIN ORDERREBATE or
              on o.ord_type = or.ord_type 
              and o.ord_no = or.ord_no
              WHERE o.line_no=9999)
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,
0,
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'
END

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-20 : 11:13:57

Same result. Statement runs fine, but it is not inserting a record.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-20 : 11:37:13

Visakh, Not sure why you are checking if the any orders are common right on top. That wouldn't insert any records if there is 1 or more than 1 common orders between the 2 tables.
I think Vack needs to insert all those orders which are already not present in oeordlin_sql and leave the rest.

Guess, this is what he wants. Let me know if I am understanding this wrong.

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,
0,
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'
and not exists
(select 1 from oeordlin_sql os where
o.ord_type = os.ord_type
and o.ord_no = os.ord_no
and os.line_no=9999)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 11:38:44

quote:
Originally posted by Vack

Same result. Statement runs fine, but it is not inserting a record.

and you're sure you dont have the records existing in destination table already with same ord_type and ord_no values as in orderrebate?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-20 : 11:46:47

I'm positive I do not have an existing record in the destination table. Like I said before, there are existing records in the destination table that have a line_no = 9999. Those are the ones I do not want the insert to be done on.

Below are the records in the destination table I am working with:
ord_type ord_no line_no item_no
O 616640 1 513188
O 616640 2 PT266

When I run the script from sakets I get an error that it cannot insert a duplicate key.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 11:57:23

quote:
Originally posted by Vack

I'm positive I do not have an existing record in the destination table. Like I said before, there are existing records in the destination table that have a line_no = 9999. Those are the ones I do not want the insert to be done on.

Below are the records in the destination table I am working with:
ord_type ord_no line_no item_no
O 616640 1 513188
O 616640 2 PT266

When I run the script from sakets I get an error that it cannot insert a duplicate key.

which key violation does error message suggests?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-20 : 13:29:43

ord_type, ord_no, line_seq_no

Just so you know each record has a line_seq_no and a line_no field. Normally these to numbers match and correspond to the number they were entered on an order.
line_no never changes.
line_seq_no will change if items are removed from the order or inserted between two existing line items.
Example:
I can have 3 items on an order.

ord_no Line_seq_no item_no Line_no
123 1 ABC 1
123 2 DDD 2
123 3 BBB 3

If I were to delete line two from the order the new records would look like
ord_no Line_seq_no item_no Line_no
123 1 ABC 1
123 2 BBB 3

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-20 : 13:41:59

Instead of a IF NOT EXISTS would a where max(oeordlin_sql.lin_no)<>9999 work???

I suppose then I'd have to join the orderrebate to the oeordlin_sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 13:42:56

quote:
Originally posted by Vack

ord_type, ord_no, line_seq_no

Just so you know each record has a line_seq_no and a line_no field. Normally these to numbers match and correspond to the number they were entered on an order.
line_no never changes.
line_seq_no will change if items are removed from the order or inserted between two existing line items.
Example:
I can have 3 items on an order.

ord_no Line_seq_no item_no Line_no
123 1 ABC 1
123 2 DDD 2
123 3 BBB 3

If I were to delete line two from the order the new records would look like
ord_no Line_seq_no item_no Line_no
123 1 ABC 1
123 2 BBB 3

remove that IF NOT EXISTS() condition and try below

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,
0,
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 
LEFT JOIN oeordlin_sql os 
on o.ord_type = os.ord_type 
and o.ord_no = os.ord_no
and os.line_no=9999
where o.ext_rebate <> 0 AND ORD_TYPE <> 'C'
and os.ord_type  is null

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-20 : 13:46:22

same thing. Cannot insert duplicate Key.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 13:49:27

quote:
Originally posted by Vack

same thing. Cannot insert duplicate Key.

what does this return?

SELECT ord_type, ord_no, line_seq_no
from ORDERREBATE
GROUP BY ord_type, ord_no, line_seq_no
HAVING COUNT(*)>1

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-20 : 13:51:41

nothing

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources