Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS mbc300 (
lotnopre VARCHAR(50),
lotno VARCHAR(100),
lotqty INT,
account15_lot VARCHAR(50),
partno_lot VARCHAR(100),
warehouse_lot VARCHAR(100)
);

CREATE TABLE


CREATE TABLE IF NOT EXISTS mbc020 (
account15_alt VARCHAR(50),
partno_alt VARCHAR(100),
warehouse_alt VARCHAR(100),
lotnopre_alt VARCHAR(50),
lotno_alt VARCHAR(100),
locbin VARCHAR(50),
locstor VARCHAR(50),
locstock INT,
locqrstock INT,
locpicked INT,
locconsign INT,
localloc INT
);

CREATE TABLE


-- Insert sample data into mbc300
INSERT INTO mbc300 (lotnopre, lotno, lotqty, account15_lot, partno_lot, warehouse_lot) VALUES
('PRE1', 'L001', 100, '1 1 5', 'PROD1', 'WARE1'),
('PRE1', 'L001', 50, '1 1 5', 'PROD1', 'WARE2'), -- Duplicate lotno L001
('PRE2', 'L002', 200, '1 1 5', 'PROD2', 'WARE1'),
('PRE2', 'L002', 150, '1 1 5', 'PROD2', 'WARE2'), -- Duplicate lotno L002
('PRE3', 'L003', 300, '1 1 5', 'PROD3', 'WARE1'),
('PRE3', 'L004', 250, '1 1 5', 'PROD3', 'WARE2'), -- Unique lotno L004
('PRE4', 'L001', 75, '1 1 5', 'PROD4', 'WARE3'),
/* test case added */
('PRE5', 'L001', 75, '9 9 9', 'PROD5', 'WARE4'),
('PRE5', 'L002', 75, '9 9 9', 'PROD5', 'WARE4')
  ;

INSERT 0 9


-- Duplicate lotno L001

-- Insert sample data into mbc020
INSERT INTO mbc020 (account15_alt, partno_alt, warehouse_alt, lotnopre_alt, lotno_alt, locbin, locstor, locstock, locqrstock, locpicked, locconsign, localloc) VALUES
('1 1 5', 'PROD1', 'WARE1', 'PRE1', 'L001', 'BIN1', 'STOR1', 50, 20, 30, 0, 10),
('1 1 5', 'PROD1', 'WARE2', 'PRE1', 'L001', 'BIN2', 'STOR2', 25, 15, 20, 0, 5),
('1 1 5', 'PROD2', 'WARE1', 'PRE2', 'L002', 'BIN3', 'STOR3', 100, 50, 40, 0, 20),
('1 1 5', 'PROD2', 'WARE2', 'PRE2', 'L002', 'BIN4', 'STOR4', 75, 30, 35, 0, 15),
('1 1 5', 'PROD3', 'WARE1', 'PRE3', 'L003', 'BIN5', 'STOR5', 150, 60, 50, 0, 25),
('1 1 5', 'PROD3', 'WARE2', 'PRE3', 'L004', 'BIN6', 'STOR6', 200, 80, 60, 0, 30),
('1 1 5', 'PROD4', 'WARE3', 'PRE4', 'L001', 'BIN7', 'STOR7', 50, 25, 20, 0, 10),
/* test case added */
('9 9 9', 'PROD5', 'WARE4', 'PRE5', 'L001', 'BIN7', 'STOR7', 50, 25, 20, 0, 10),
('9 9 9', 'PROD5', 'WARE4', 'PRE5', 'L002', 'BIN7', 'STOR7', 50, 25, 20, 0, 10)
  ;

INSERT 0 9


/* harishgg1 */
SELECT 
    t1.lotnopre,
    t1.lotno,
    t2.locbin,
    t2.locstor,
    ((t2.locstock + t2.locqrstock) + (t2.locpicked + t2.locconsign)) + t2.localloc,
    t1.lotqty,
    t1.account15_lot,
    t2.partno_alt
FROM 
    mbc020 t2,
    mbc300 t1
WHERE 
    t1.account15_lot = '1 1 5'
    AND t2."account15_alt" = t1."account15_lot"
    AND t2."partno_alt" = t1."partno_lot"
    AND t2."warehouse_alt" = t1."warehouse_lot"
    AND t2."lotnopre_alt" = t1."lotnopre"
    AND t2."lotno_alt" = t1."lotno"
    AND t1."lotnopre" <> ' '
GROUP BY 
    t1.lotno
HAVING 
    COUNT(t1.lotno) > 1
ORDER BY 
    t1.lotno;

ERROR:  column "t1.lotnopre" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:     t1.lotnopre,
            ^


/* 
This solution makes many assumpions about the data.
One such assumption is that account15_lot is a parameter rather than a static value.

account15_lot '1 1 5' should produce 5 resulting rows:
3 rows with lotno L001
2 rows with lotno L002
0 rows with lotno L003
0 rows with lotno L004
*/
WITH
  mbc AS 
  (
    SELECT 
        t1.lotnopre,
        t1.lotno,
        t2.locbin,
        t2.locstor,
        ((t2.locstock + t2.locqrstock) + (t2.locpicked + t2.locconsign)) + t2.localloc AS loc_something,
        t1.lotqty,
        t1.account15_lot,
        t2.partno_alt
    FROM 
        mbc020 t2,
        mbc300 t1
    WHERE 
            t2."account15_alt" = t1."account15_lot"
        AND t2."partno_alt" = t1."partno_lot"
        AND t2."warehouse_alt" = t1."warehouse_lot"
        AND t2."lotnopre_alt" = t1."lotnopre"
        AND t2."lotno_alt" = t1."lotno"
        AND t1."lotnopre" <> ' '
  )
SELECT *
FROM mbc
WHERE mbc.account15_lot = '1 1 5'
    AND mbc.lotno IN ( SELECT t1.lotno 
                       FROM mbc t1 
                       WHERE t1.account15_lot = mbc.account15_lot
                       GROUP BY t1.lotno 
                       HAVING COUNT(t1.lotno) > 1 
                     )
ORDER BY 
    mbc.lotno;


lotnopre
lotno
locbin
locstor
loc_something
lotqty
account15_lot
partno_alt


PRE1
L001
BIN1
STOR1
110
100
1 1 5
PROD1

PRE1
L001
BIN2
STOR2
65
50
1 1 5
PROD1

PRE4
L001
BIN7
STOR7
105
75
1 1 5
PROD4

PRE2
L002
BIN3
STOR3
210
200
1 1 5
PROD2

PRE2
L002
BIN4
STOR4
155
150
1 1 5
PROD2



SELECT 5


/* 
account15_lot '9 9 9' should produce no resulting rows.
*/
WITH
  mbc AS 
  (
    SELECT 
        t1.lotnopre,
        t1.lotno,
        t2.locbin,
        t2.locstor,
        ((t2.locstock + t2.locqrstock) + (t2.locpicked + t2.locconsign)) + t2.localloc AS loc_something,
        t1.lotqty,
        t1.account15_lot,
        t2.partno_alt
    FROM 
        mbc020 t2,
        mbc300 t1
    WHERE 
            t2."account15_alt" = t1."account15_lot"
        AND t2."partno_alt" = t1."partno_lot"
        AND t2."warehouse_alt" = t1."warehouse_lot"
        AND t2."lotnopre_alt" = t1."lotnopre"
        AND t2."lotno_alt" = t1."lotno"
        AND t1."lotnopre" <> ' '
  )
SELECT *
FROM mbc
WHERE mbc.account15_lot = '9 9 9'
    AND mbc.lotno IN ( SELECT t1.lotno 
                       FROM mbc t1 
                       WHERE t1.account15_lot = mbc.account15_lot
                       GROUP BY t1.lotno 
                       HAVING COUNT(t1.lotno) > 1 
                     )
ORDER BY 
    mbc.lotno;


lotnopre
lotno
locbin
locstor
loc_something
lotqty
account15_lot
partno_alt



SELECT 0
lotnopre	lotno	locbin	locstor	loc_something	lotqty	account15_lot	partno_alt
PRE1	L001	BIN1	STOR1	110	100	1 1 5	PROD1
PRE1	L001	BIN2	STOR2	65	50	1 1 5	PROD1
PRE4	L001	BIN7	STOR7	105	75	1 1 5	PROD4
PRE2	L002	BIN3	STOR3	210	200	1 1 5	PROD2
PRE2	L002	BIN4	STOR4	155	150	1 1 5	PROD2