1

I have two tables one ORDERS and the other ORDERITEM

The ORDERS table looks like this:

ORDERNO 
ORDERDATE
ORDERREADYDATE
CUSTOMERNO

The ORDERITEMS table looks like this:

ORDERNO 
ORDERDATE
QTY
ITEMNO

I have an order record:

12335
11/04/2018
11/06/2018
9999999

12336
11/04/2018
11/06/2018
9999998

12337
11/04/2018
11/06/2018
9999997

12345
11/05/2018
11/07/2018
9999999

12346
11/05/2018
11/07/2018
9999998

12347
11/05/2018
11/07/2018
9999997

And orderitems:

12335
11/04/2018
5
678    

12335.1
11/04/2018
11
587

12335.2
11/04/2018
3
554

12336
11/04/2018
5
678    

12336.1
11/04/2018
11
587

12336.2
11/04/2018
3
554

12345
11/05/2018
5
678    

12345.1
11/05/2018
11
587

12345.2
11/05/2018
3
554

12346
11/05/2018
5
678    

12346.1
11/05/2018
11
587

12346.2
11/05/2018
3
554

On the ready date I would like to find all items that do not have a dot in the invoice.

I tried this, but it gives me old orders:

SELECT
  i.orderno,
  i.orderdate,
  d.orderno,
  d.orderdate,
  d.itemno
FROM
  ORDERS i,
  ORDERITEM d
WHERE
      i.ORDERREADYDATE = '2018-11-07'
  AND d.orderdate = i.orderdate
  AND d.orderno NOT LIKE '%.%'

Here is code to reproduce the problem:

TRY DROP TABLE #ORDERS; CATCH ALL END TRY;
TRY DROP TABLE #ORDERITEM; CATCH ALL END TRY;

CREATE TABLE
  #ORDERS
(
  ORDERNO NVARCHAR(10), 
  ORDERDATE DATE,
  ORDERREADYDATE DATE,
  CUSTOMERNO INTEGER
);

CREATE TABLE
  #ORDERITEM
(
  ORDERNO NVARCHAR(10), 
  ORDERDATE DATE,
  QTY INTEGER,
  ITEMNO INTEGER,
);

INSERT INTO
  #ORDERS
SELECT
  '12335',
  '2018-11-04',
  '2018-11-06',
  9999999
FROM system.iota
UNION SELECT
  '12336',
  '2018-11-04',
  '2018-11-06',
  9999998
FROM system.iota
UNION SELECT
  '12337',
  '2018-11-04',
  '2018-11-06',
  9999997
FROM system.iota
UNION SELECT
  '12345',
  '2018-11-05',
  '2018-11-07',
  9999999
FROM system.iota
UNION SELECT
  '12346',
  '2018-11-05',
  '2018-11-07',
  9999998
FROM system.iota
UNION SELECT
  '12347',
  '2018-11-05',
  '2018-11-07',
  9999997
FROM system.iota;

INSERT INTO
  #ORDERITEM
SELECT
  '12335',
  '2018-11-04',
  5,
  678
FROM system.iota
UNION SELECT
  '12335.1',
  '2018-11-04',
  11,
  587
FROM system.iota
UNION SELECT
  '12335.2',
  '2018-11-04',
  3,
  554
FROM system.iota
UNION SELECT
  '12336',
  '2018-11-04',
  5,
  678    
FROM system.iota
UNION SELECT
  '12336.1',
  '2018-11-04',
  11,
  587
FROM system.iota
UNION SELECT
  '12336.2',
  '2018-11-04',
  3,
  554
FROM system.iota
UNION SELECT
  '12345',
  '2018-11-05',
  5,
  678    
FROM system.iota
UNION SELECT
  '12345.1',
  '2018-11-05',
  11,
  587
FROM system.iota
UNION SELECT
  '12345.2',
  '2018-11-05',
  3,
  554
FROM system.iota
UNION SELECT
  '12346',
  '2018-11-05',
  5,
  678    
FROM system.iota
UNION SELECT
  '12346.1',
  '2018-11-05',
  11,
  587
FROM system.iota
UNION SELECT
  '12346.2',
  '2018-11-05',
  3,
  554
FROM system.iota
;

SELECT
  i.orderno,
  i.orderdate,
  d.orderno,
  d.orderdate,
  d.itemno
FROM
  #ORDERS i,
  #ORDERITEM d
WHERE
      i.ORDERREADYDATE = '2018-11-07'
  AND d.orderdate = i.orderdate
  AND d.orderno NOT LIKE '%.%'
;

This SQL runs forever, if i remove the d.orderno NOT LIKE '%.%' it finish, but I get the same 20 repeting i.orderno with diffrent d.orderno The ORDERITEM table have over 7 millon records growing with 10K everyday.

Thanks for any help.

Jens Mühlenhoff
  • 14,565
  • 6
  • 56
  • 113
Kim HJ
  • 1,183
  • 2
  • 11
  • 37

2 Answers2

1

I recommend not using the old style of join syntax, but regardless you seem to be missing the join condition: i.orderno = d.orderno

Select 
    i.orderno, 
    i.orderdate, 
    d.orderno, 
    d.orderdate, 
    d.itemno 
from
    ORDERS i
join
    ORDERITEM d
on 
    i.orderno = d.orderno
    and d.orderdate = i.orderdate
    and d.orderno NOT LIKE '%.%'
where 
    i.ORDERREADYDATE='2018-11-07'

Here is the same query with the old join syntax:

Select 
    i.orderno, 
    i.orderdate, 
    d.orderno, 
    d.orderdate, 
    d.itemno 
from
    #ORDERS i,
    #ORDERITEM d
where 
    i.orderno = d.orderno
    and d.orderdate = i.orderdate
    and d.orderno NOT LIKE '%.%'
    and i.ORDERREADYDATE='2018-11-07'
Jens Mühlenhoff
  • 14,565
  • 6
  • 56
  • 113
Hooman Bahreini
  • 14,480
  • 11
  • 70
  • 137
  • OP is using the "old" style join syntax, but the original query is indeed missing a condition on the orderno fields. – Jens Mühlenhoff Nov 08 '18 at 00:22
  • 1
    This fixes the duplication issue. As for the performance problem: LIKE with placeholder at the beginning and end can not be optimized by the query engine. – Jens Mühlenhoff Nov 09 '18 at 10:34
  • The way I see it is that I have to find all the orders with the OrderReadyDate='2018-11-07' then I have to find all the Orderitems for those orders since I dont want the ones where the orderno have changed I should not need the NOT LIKE just match the orderno. – Kim HJ Nov 10 '18 at 04:02
0

I found the way, this return the right result.

Select d.orderno, d.orderdate from ORDERITEMS d
where
d.orderno NOT LIKE '%.%' and d.orderno in 
(Select i.orderdate ORDERS i where i.orderreadydate='2018-11-07)
Kim HJ
  • 1,183
  • 2
  • 11
  • 37