My query below should only do an insert if these 2 evaluate to true:
a) The customer number is not currently in the DR table
b) The customer number is in the list check (where customer in)
a has to be true in order for b to be checked. The insert cannot happen for a customer that's already in the DCR whose number is in the IN clause
So far, it is close however I'm finding a few numbers being inserted that are already in the DR table.
I would also like to insert one record for the customer. It will find multiple entries per customer number since this is really a transactiosn table but I need to insert like the Top 1 o something since these inserts are going to be customers who have no transactions...thus is why you see blanks or zeros for all the values. These inserts are part of a larger picture but are needed...so there is no n eed to explain why I'd want to insert nothing ('' and 0 values) for those customers...just leave it at that
SELECT top 1 m.customer,
c.name,
c.customer,
'',
0,
m.Branch,
0,
'',
'',
'',
0,
'',
'',
0,
0,
0,
0,
'UI' AS Type,
1 AS Active,
m.number,
0,
0,
0,
0,
0,
0,
'',
0,
0,
'',
'',
(SELECT TotalPostingDays from TotalPostingDays),
(SELECT CurrentPostingDAy from CurrentPostingDay)
FROM dbo.Master m (NOLOCK)
INNER JOIN dbo.Customer c ON c.Customer = m.Customer
AND c.customer IN ( '0000093',
'0000066',
'0000050',
'0000114',
'0000112',
'0000124',
'0000113'
'0000094',
'0000104',
'0000122',
'0000123',
'0000127',
'0000057',
'0000132',
'0000138',
'0000128',
'0000142',
'0000149',
'0000147',
'0000144',
'0000148',
'0000145',
'0000103',
'0000105',
'0000109',
'0000135',
'0000155',
'0000156',
'0000157',
'0000159',
'0000160',
'0000161',
'0000118',
'0000143',
'0000146',
'0000153',
'0000152',
'0000108',
'0000158',
'0000133')
AND c.customer NOT IN (select customernumber FROM DR)
I think your problem lies in the fact that you are making your customer not in the DR table comparison as part or the join criteria. Try moving it to a WHERE clause instead or actually join the DR table using left join, see below
1st, instead of "AND c.customer NOT IN (select customernumber FROM DR)" Replace with WHERE c.customer NOT IN (SELECT customernumber FROM DR) Also move your check for customers in the provided list, into a where clause instead of the join criteria.
2nd, use the join as below with a where clause
Code Snippet
SELECT top 1 m.customer,
c.name,
c.customer,
'',
0,
m.Branch,
0,
'',
'',
'',
0,
'',
'',
0,
0,
0,
0,
'UI' AS Type,
1 AS Active,
m.number,
0,
0,
0,
0,
0,
0,
'',
0,
0,
'',
'',
(SELECT TotalPostingDays from TotalPostingDays),
(SELECT CurrentPostingDAy from CurrentPostingDay)
FROM dbo.Master m (NOLOCK)
INNER JOIN dbo.Customer c ON c.Customer = m.Customer
LEFT OUTER JOIN DR d ON c.customer = d.customernumber
WHERE d.customernumber is null
AND c.customer IN ( '0000093',
'0000066',
'0000050',
'0000114',
'0000112',
'0000124',
'0000113'
'0000094',
'0000104',
'0000122',
'0000123',
'0000127',
'0000057',
'0000132',
'0000138',
'0000128',
'0000142',
'0000149',
'0000147',
'0000144',
'0000148',
'0000145',
'0000103',
'0000105',
'0000109',
'0000135',
'0000155',
'0000156',
'0000157',
'0000159',
'0000160',
'0000161',
'0000118',
'0000143',
'0000146',
'0000153',
'0000152',
'0000108',
'0000158',
'0000133')
Either method should provide the results you need.
|||
Try re-writing your statement to use NOT EXISTS instead NOT IN to check for existence. If for any reason, there is a row in [DR] where customernumber is NULL, then you will have some trouble.
Example:
Code Snippet
select
*
from
(select 1 as c1 union all select 2) as a
where
c1 not in (1, NULL)
It should looks like:
...
where
c.customer IN (
'0000093',
'0000066',
'0000050',
'0000114',
'0000112',
'0000124',
'0000113',
'0000094',
'0000104',
'0000122',
'0000123',
'0000127',
'0000057',
'0000132',
'0000138',
'0000128',
'0000142',
'0000149',
'0000147',
'0000144',
'0000148',
'0000145',
'0000103',
'0000105',
'0000109',
'0000135',
'0000155',
'0000156',
'0000157',
'0000159',
'0000160',
'0000161',
'0000118',
'0000143',
'0000146',
'0000153',
'0000152',
'0000108',
'0000158',
'0000133'
)
AND NOT exists (
select *
from DR as d
where d.customernumber = c.customer
)
AMB
No comments:
Post a Comment