Новичок
Джуниор
Регистрация: 08.01.2023
Сообщений: 1
|
Помощь с SQL-запросом
Добрый день! Может кто сможет помочь! Пытаюсь модернизировать в MS Visual basic SQL запрос с выгрузкой данных по времени, время представлено в БД я так понимаю в текстовом виде:
HEURE
38
49
54
54
100
100
129
146
Если брать максимум, то значение будет 235959, запрос изначально был:
Код:
1 select date, heure, Sector, colis, nom, prenom, sub_group_type, sub_group_number, sum(EAN) as EAN, sum(QTE) as quantity from (SELECT H2HBD2 as date, CASE WHEN H2HBD2 = A1QHD2 THEN A1CYHE
2 WHEN H2HBD2 = A1RED2 THEN A1CXHE END as Heure, A4SRNX as Sector, iti2c2 as colis, CASE WHEN p1.PLIBE1<>' ' THEN p1.PLIBE1 ELSE H2JPC2 END as nom, CASE WHEN p1.PLIBE1 <> ' ' THEN p1.PLIBE2
3 ELSE p2.PLIBE2 END as prenom, substring(JSG9CE, 1, 4) as sub_group_type, substring(JSG9CE, 5, 5) as sub_group_number, sum(ITMJNY) as QTE, count(distinct(H2JCC2)) as EAN
4 FROM newges.mth1cpp inner join newges.mth2cpp on h1i2c2=h2i2C2 and h1i3c2=h2i3c2 and H2HBD2 between $param1 and $param2
5 inner join newges.skitrep on H2I2C2=ITI2C2 and H2I8C2=ITI8C2 and H2I3C2 = ITI3C2 inner join newges.mrnprep on ITQ5NI = NPQ5NI
6 left outer join newges.iparam p1 on p1.pcle = concat('OPE', H2JPC2) left outer join newges.iparam p2
7 on p2.pcle like 'OPE%' and p2.PLIBE1 = H2JPC2 inner join newges.mta1rep on NPHUNX=A1HUNX and NPH2NX=A1H2NX and a1ianx = npianx inner join newges.mta4rep on NPHUNX=A4HUNX
8 and A4JFN2 = 4 and NPH2NX = A4H2NX inner join newges.mrhvrep on npr3n2=hvr3n2 and npr1n2=hvr1n2 inner join newges.sqjsrep on jsr3n2=npr3n2 and jsr1n2=npr1n2
9 exception join newges.SPFVCPP on substr(FVEGCE, 1, 15)=H1I2C2 exception join newges.STK1CPP on substr(K1QFUB, 1, 15)=H1i2C2 exception join newges.smatcpp on substr(ATDMTZ, 1, 15)=H1I2C2
10 exception join newges.soajcpp on substr(AJP8TZ, 1, 15)=H1I2C2 exception join newges.SOEHCPP on EHI2C2 = H1I2C2 WHERE h1j6SS > '10' and H1J6SS < '71'
11 group by H2HBD2, A1QHD2, A1RED2, A1CXHE, A1CYHE, A4SRNX, iti2c2, p1.PLIBE1, H2JPC2, p1.PLIBE2, p2.PLIBE2, substring(JSG9CE, 1, 4), substring(JSG9CE, 5, 5) ) as toto
12 group by date, heure, Sector, colis, nom, prenom, sub_group_type, sub_group_number order by date, heure, colis, Sector, nom, prenom, sub_group_type, sub_group_number
Я переделал до:
Код:
1 select date, heure, Sector, colis, nom, prenom, sub_group_type, sub_group_number, sum(EAN) as EAN, sum(QTE) as quantity from (SELECT H2HBD2 as date, CASE WHEN H2HBD2 = A1QHD2 THEN A1CYHE
2 WHEN H2HBD2 = A1RED2 THEN A1CXHE END as Heure, A4SRNX as Sector, iti2c2 as colis, CASE WHEN p1.PLIBE1<>' ' THEN p1.PLIBE1 ELSE H2JPC2 END as nom, CASE WHEN p1.PLIBE1 <> ' ' THEN p1.PLIBE2
3 ELSE p2.PLIBE2 END as prenom, substring(JSG9CE, 1, 4) as sub_group_type, substring(JSG9CE, 5, 5) as sub_group_number, sum(ITMJNY) as QTE, count(distinct(H2JCC2)) as EAN
4 FROM newges.mth1cpp inner join newges.mth2cpp on h1i2c2=h2i2C2 and h1i3c2=h2i3c2
5 inner join newges.skitrep on H2I2C2=ITI2C2 and H2I8C2=ITI8C2 and H2I3C2 = ITI3C2 inner join newges.mrnprep on ITQ5NI = NPQ5NI
6 left outer join newges.iparam p1 on p1.pcle = concat('OPE', H2JPC2) left outer join newges.iparam p2
7 on p2.pcle like 'OPE%' and p2.PLIBE1 = H2JPC2 inner join newges.mta1rep on NPHUNX=A1HUNX and NPH2NX=A1H2NX and a1ianx = npianx and H1A6TM = A1CXHE
8 inner join newges.mta4rep on NPHUNX=A4HUNX
9 and A4JFN2 = 4 and NPH2NX = A4H2NX inner join newges.mrhvrep on npr3n2=hvr3n2 and npr1n2=hvr1n2 inner join newges.sqjsrep on jsr3n2=npr3n2 and jsr1n2=npr1n2
10 exception join newges.SPFVCPP on substr(FVEGCE, 1, 15)=H1I2C2 exception join newges.STK1CPP on substr(K1QFUB, 1, 15)=H1i2C2 exception join newges.smatcpp on substr(ATDMTZ, 1, 15)=H1I2C2
11 exception join newges.soajcpp on substr(AJP8TZ, 1, 15)=H1I2C2 exception join newges.SOEHCPP on EHI2C2 = H1I2C2 WHERE h1j6SS > '10' and H1J6SS < '71'
12 group by H2HBD2, A1QHD2, A1RED2, A1CXHE, A1CYHE, A4SRNX, iti2c2, p1.PLIBE1, H2JPC2, p1.PLIBE2, p2.PLIBE2, substring(JSG9CE, 1, 4), substring(JSG9CE, 5, 5) ) as toto
13 where (concat(date,(substr(digits(heure), 1, 2) concat substr(digits(heure), 3, 2) concat substr(digits(heure), 5, 2))) between concat('$param1','$param2')
14 and concat('$param3','$param4'))
15 group by date, heure, Sector, colis, nom, prenom, sub_group_type, sub_group_number order by date, heure, colis, Sector, nom, prenom, sub_group_type, sub_group_number
16
Но проблема в том, что часть каких-то данных по новому запросу не выгружается, они почему-то не учитываются, вот два примера от изначального запроса и через жёлтый разделитель по новому.
Screenshot_1.jpg
Есть идеи что я сделал не так, уже измучался весь
|