Changes between Initial Version and Version 1 of Ticket #1175, comment 7


Ignore:
Timestamp:
Jun 9, 2023, 10:45:06 AM (17 months ago)
Author:
Denis_N

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #1175, comment 7

    initial v1  
    11drop procedure mismatchQueryWithoutOtkRows;
     2
    23DELIMITER }
     4
    35CREATE PROCEDURE mismatchQueryWithoutOtkRows(min int, max int, searchName varchar (100), searchSerial varchar (100), searchComment varchar (10000)) READS SQL DATA
    46BEGIN
    … …  
    79select * from (select *, @mintotal as mintotal, @prevtotal := @mintotal - total as prevtotal from
    810(select products.serial, history.uid, history.date, hist1.total from history inner join
    9 (select uid, count(uid) as total from history where (type_write = "mismatch" or type_write = 'record'
    10 or type_write = "repair" or (type_write = "testing" and status = "fail")) and comment is not null and comment != group by uid) as hist1 on history.uid = hist1.uid
     11(select uid, count(uid) as total from history where (type_write = "mismatch" or type_write = 'record' 
     12or type_write = "repair" or (type_write = "testing" and status = "fail")) and comment is not null and  comment != '' group by uid) as hist1 on history.uid = hist1.uid
    1113inner join products on products.uid = history.uid where (products.name like searchName
    12 and products.serial like searchSerial and history.comment like searchComment) and (history.uid, history.date) in
     14and products.serial like searchSerial and history.comment like searchComment) and (history.uid, history.date) in 
    1315(select uid, max(date) from history where (type_write = "mismatch" or type_write = 'record' or type_write = "repair" or
    14 (type_write = "testing" and status = "fail") or history.type_write = 'record') and history.comment like searchComment and comment is not null and comment != group by uid)
     16(type_write = "testing" and status = "fail") or `history`.type_write = 'record') and history.comment like searchComment and comment is not null and  comment != '' group by uid)
    1517group by products.serial, history.uid, history.date, hist1.total order by history.date desc) as hist2
    1618where (@mintotal := @minTotal + total)) as hust2 where hust2.mintotal > min and hust2.prevtotal < max;
     19
    1720END}
    1821DELIMITER ;