IBM Sterling B2B: Queries for Archive: Difference between revisions

From Wiki
No edit summary
No edit summary
Line 18: Line 18:
|}
|}


== ==


select to_char(mm.CREATED_DATETIME, 'YYYY') as YEAR1, count(1) as CONT
from MBX_MESSAGE mm
group by to_char(mm.CREATED_DATETIME, 'YYYY')
order by to_char(mm.CREATED_DATETIME, 'YYYY')
result:
{| class="wikitable"
|-
! YEAR1 !! CNT
|-
| 2018 || 9943
|-
| 2019 || 14530
|-
| 2020 || 265567
|-
| 2021 || 4198
|}
==Messages on mailboxes by created date==
select mm.MESSAGE_ID,mm.CREATED_DATETIME,mm.MESSAGE_SIZE,mm.MAILBOX_ID,mb.PATH,mm.DOC_ID
from MBX_MESSAGE mm, MBX_MAILBOX mb
where mm.MAILBOX_ID = mb.MAILBOX_ID
order by mm.CREATED_DATETIME
==Messages on mailboxes group by year with count and sum==
select to_char(mm.CREATED_DATETIME, 'YYYY') as ANO, mb.PATH as mbx, count(1) as CNT, SUM(mm.MESSAGE_SIZE) as SUM1
from MBX_MESSAGE mm, MBX_MAILBOX mb
where mm.MAILBOX_ID = mb.MAILBOX_ID
group by to_char(mm.CREATED_DATETIME, 'YYYY'), mb.PATH


= Ver também =
= Ver também =

Revision as of 10:39, 17 April 2021


SELECT ARCHIVE_FLAG, count(1) as CNT FROM ARCHIVE_INFO GROUP BY ARCHIVE_FLAG ORDER BY ARCHIVE_FLAG

result:

ARCHIVE_FLAG CNT
-1 310
0 83910
1 912571
2 4198

select to_char(mm.CREATED_DATETIME, 'YYYY') as YEAR1, count(1) as CONT

from MBX_MESSAGE mm
group by to_char(mm.CREATED_DATETIME, 'YYYY')
order by to_char(mm.CREATED_DATETIME, 'YYYY')

result:

YEAR1 CNT
2018 9943
2019 14530
2020 265567
2021 4198


Messages on mailboxes by created date

select mm.MESSAGE_ID,mm.CREATED_DATETIME,mm.MESSAGE_SIZE,mm.MAILBOX_ID,mb.PATH,mm.DOC_ID
from MBX_MESSAGE mm, MBX_MAILBOX mb
where mm.MAILBOX_ID = mb.MAILBOX_ID
order by mm.CREATED_DATETIME


Messages on mailboxes group by year with count and sum

select to_char(mm.CREATED_DATETIME, 'YYYY') as ANO, mb.PATH as mbx, count(1) as CNT, SUM(mm.MESSAGE_SIZE) as SUM1
from MBX_MESSAGE mm, MBX_MAILBOX mb
where mm.MAILBOX_ID = mb.MAILBOX_ID
group by to_char(mm.CREATED_DATETIME, 'YYYY'), mb.PATH

Ver também