By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `inventori_dt1` (
`_CODE` varchar(10) NOT NULL,
`_ASSET` varchar(50) DEFAULT NULL,
`_SERIAL` varchar(50) DEFAULT NULL,
`_MODEL` varchar(10) DEFAULT NULL,
`_DVCNM` varchar(50) DEFAULT NULL,
`_IPADDR` varchar(50) DEFAULT NULL,
`_IPTYPE` varchar(10) DEFAULT NULL,
`_AD` varchar(10) DEFAULT NULL,
`_WG` int(2) UNSIGNED ZEROFILL DEFAULT NULL,
`_CAT` int(2) UNSIGNED ZEROFILL DEFAULT NULL,
`_PROC` varchar(10) DEFAULT NULL,
`_GRPC` varchar(10) DEFAULT NULL,
`_RAM1_CAP` bigint DEFAULT NULL,
`_RAM2_CAP` bigint DEFAULT NULL,
`_RAM3_CAP` bigint DEFAULT NULL,
`_RAM4_CAP` bigint DEFAULT NULL,
`_HDD1_CAP` bigint DEFAULT NULL,
`_HDD2_CAP` bigint DEFAULT NULL,
`_HDD3_CAP` bigint DEFAULT NULL,
`_HDD4_CAP` bigint DEFAULT NULL,
`_SSD1_CAP` bigint DEFAULT NULL,
`_SSD2_CAP` bigint DEFAULT NULL,
`_SSD3_CAP` bigint DEFAULT NULL,
`_SSD4_CAP` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `inventori_dt1` (`_CODE`, `_ASSET`, `_SERIAL`, `_MODEL`, `_DVCNM`, `_IPADDR`, `_IPTYPE`, `_AD`, `_WG`, `_CAT`, `_PROC`, `_GRPC`, `_RAM1_CAP`, `_RAM2_CAP`, `_RAM3_CAP`, `_RAM4_CAP`, `_HDD1_CAP`, `_HDD2_CAP`, `_HDD3_CAP`, `_HDD4_CAP`, `_SSD1_CAP`, `_SSD2_CAP`, `_SSD3_CAP`, `_SSD4_CAP`) VALUES
('IN2009002', 'FOQBEFUI32BF', 'EJ2FROE4F', '01130012', 'PC_MSHA', NULL, 'DINAMIK', 'ADA', 02, 01, '03150002', '02150001', 8000000000, NULL, NULL, NULL, 2000000000000, NULL, NULL, NULL, 480000000000, NULL, NULL, NULL),
('IN2009003', 'WF3QR12R32', 'SGH129F3', '01130022', 'PC_ALIA2', NULL, 'DINAMIK', 'ADA', 01, 01, '03150002', '02150001', 8000000000, NULL, NULL, NULL, 2000000000000, NULL, NULL, NULL, 480000000000, NULL, NULL, NULL),
('IN2009001', 'DJOBNW9EUFVBO23', 'PR29D33R', '01010006', 'PC-LAILA', NULL, 'DINAMIK', 'TIADA', 03, 01, '03150004', '02150001', 4000000000, NULL, NULL, NULL, 2000000000000, NULL, NULL, NULL, 480000000000, NULL, NULL, NULL);
CREATE TABLE `inventori_dt2` (
`_CODE` varchar(10) NOT NULL,
`_ASSET` varchar(50) DEFAULT NULL,
`_SERIAL` varchar(50) DEFAULT NULL,
Records: 3 Duplicates: 0 Warnings: 0
Records: 5 Duplicates: 0 Warnings: 0
Records: 6 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
Records: 8 Duplicates: 0 Warnings: 0
Records: 2 Duplicates: 0 Warnings: 0
Records: 13 Duplicates: 0 Warnings: 0
Records: 7 Duplicates: 0 Warnings: 0
Records: 6 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
Records: 4 Duplicates: 0 Warnings: 0
Records: 5 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
The table 'inventori_mt' is full
SELECT JSON_PRETTY( JSON_ARRAYAGG(Z) ) AS Z
FROM
(
SELECT
JSON_OBJECT(
'_CODE', a._CODE,
'_USERID', a._USERID,
'_NAME', c._NAME,
'HAHA',
(SELECT
JSON_ARRAYAGG(
JSON_OBJECT(
'_CAT', b._CAT,
'_MODEL', b._MODEL,
'_SERIAL', b._SERIAL,
'_IPADDR', b._IPADDR ,
'_OS', (SELECT _DESC
FROM kod_produk
WHERE _CODE = (SELECT _PROD
FROM inventori_dt3
WHERE _CODE = a._CODE
AND _CAT = '15'
AND _SERIAL = b._SERIAL)),
'_AV', (SELECT CONCAT(_DESC,
' (',
(CASE WHEN(SELECT _CODE
FROM inventori_dt3
WHERE _CODE = a._CODE
AND _CAT = '16'
AND _EXPY_DATE > NOW())
IS NOT NULL
THEN
'AKTIF'
ELSE
'TAMAT'
END), ')')
| Z |
|---|
| [ { "HAHA": [ { "_AV": "ANTIVIRUS (TAMAT)", "_OS": "WINDOWS 10 (64BIT)", "_CAT": 1, "_MODEL": "01010006", "_IPADDR": null, "_SERIAL": "PR29D33R" }, { "_AV": null, "_OS": null, "_CAT": 8, "_MODEL": "08080005", "_IPADDR": "112.12.43.119", "_SERIAL": "CNCJF38364" }, { "_AV": null, "_OS": null, "_CAT": 7, "_MODEL": "07010002", "_IPADDR": null, "_SERIAL": "20TVMG2" } ], "_CODE": "IN2009001", "_NAME": "LAILA", "_USERID": "10200" }, { "HAHA": [ { "_AV": null, "_OS": null, "_CAT": 8, "_MODEL": "08080005", "_IPADDR": "112.12.43.85", "_SERIAL": "NPJA115896" }, { "_AV": null, "_OS": null, "_CAT": 7, "_MODEL": "07130004", "_IPADDR": null, "_SERIAL": "CNC342P000" }, { "_AV": "ANTIVIRUS (TAMAT)", "_OS": "WINDOWS 7 (32BIT)", "_CAT": 1, "_MODEL": "01130012", "_IPADDR": null, "_SERIAL": "EJ2FROE4F" } ], "_CODE": "IN2009002", "_NAME": "MASHA", "_USERID": "10895" }, { "HAHA": [ { "_AV": "ANTIVIRUS (TAMAT)", "_OS": "WINDOWS 7 (64BIT)", "_CAT": 1, "_MODEL": "01130022", "_IPADDR": null, "_SERIAL": "SGH129F3" }, { "_AV": null, "_OS": null, "_CAT": 7, "_MODEL": "07130004", "_IPADDR": null, "_SERIAL": "CNC1270J4H" } ], "_CODE": "IN2009003", "_NAME": "ALIA", "_USERID": "10070" } ] |