Paul Kalathas
Reputation: 47
SQL Query Returning Multiple Records when only single records should be returned
I have a SQL query that I am running with multiple joins to return the required results however, when add into my select statement this field requirements MX.TFNO AS 'BULK TO SPLIT REFERENCE' I get duplicate rows of data that are incorrect yet when I comment out this line, the query works correctly yet without the data that should be returned into the above mentioned column.
Below is my query. Please note that I have a join that I have commented out for table MPEXOR as I have yet set up this table in our datalake to pull in that data.
SELECT distinct
MP.SUNO AS 'SUPPLIER',
CI.SUNM AS 'SUPPLIER NAME',
MP.FACI AS 'FACILITY',
MP.WHLO AS 'WAREHOUSE',
MP.OURR AS 'SUPPLIER PO',
CONCAT(MM.BUAR ,'/', MP.PROJ) AS 'PO GROUP',
MP.PUNO AS 'PURCHASE ORDER NUMBER',
IIF (MP.PUNO LIKE '533%', 'SPLIT',
IIF (MP.PUNO LIKE '511%', 'BULK', '')) as 'PO TYPE',
--MX.TFNO AS 'BULK TO SPLIT REFERENCE',
MP.PNLI AS 'PO LINE NUMBER',
MP.ITNO AS 'ITEM NUMBER',
MP.PITD AS 'STYLE',
MH.TX15 AS 'SIZE',
MH.TY15 AS 'COLOR',
MM.BUAR AS 'BRAND',
CONVERT(varchar,MP.DWDT, 101) AS 'EX FACTORY ORIGINAL DATE',
CONVERT(varchar,MP.CODT,101) AS 'REVISED EX FACTORY DATE',
IIF (MP.MODL = 'S', 'OCEAN',
IIF (MP.MODL = 'C', 'COURIER',
IIF (MP.MODL = 'G', 'CONSIGNEE OPTION',
IIF (MP.MODL = 'H', 'CUSTOMER PICK UP',
IIF (MP.MODL = 'M', 'MOTOR',
IIF (MP.MODL = 'A', 'AIR', '')))))) as 'DELIVERY METHOD',
MP.ORQA AS 'ORDERED QTY',
MF.UCOS AS 'COST PRICE',
MP.PUPR AS 'PURCHASE PRICE',
MP.PUST AS 'PO LOW STATUS',
MP.PUSL AS 'PO HIGH STATUS',
MP.ORCO AS 'COUNTRY OF ORIGIN'
--MX.HREF AS 'PO REFERENCE'
FROM MPLINE MP
INNER JOIN CIDMAS CI WITH (NOLOCK)
ON CI.CONO = MP.CONO
AND CI.SUNO = MP.SUNO
AND CI.DELETED = 'N'
LEFT JOIN MITFAC MF WITH (NOLOCK)
ON MF.CONO = MP.CONO
AND MF.FACI = MP.FACI
AND MF.ITNO = MP.ITNO
AND MF.DELETED = 'N'
LEFT JOIN MITMAH MH WITH (NOLOCK)
ON MH.CONO = MP.CONO
AND MH.ITNO = MP.ITNO
AND MH.DELETED = 'N'
LEFT JOIN MITMAS MM WITH (NOLOCK)
ON MM.CONO = MP.CONO
AND MM.ITNO = MP.ITNO
AND MM.DELETED = 'N'
LEFT JOIN MPHEAD MX WITH (NOLOCK)
ON MX.CONO = MP.CONO
AND MX.SUNO = MP.SUNO
AND MX.FACI = MP.FACI
AND MX.WHLO = MP.WHLO
AND MX.ORTY = MP.ORTY
AND MX.DELETED = 'N'
--LEFT JOIN MPEXOR ME
--ON ME.CONO = MP.CONO
--AND ME.PUNO = MP.PUNO
WHERE MP.PUST < 50
AND MP.PUSL < 50
AND MP.PUNO = '5330000172'
--ORDER BY MP.PUNO, MP.PNLI
;
Here is the return result with MX.TFNO in place
SUPPLIER SUPPLIER NAME FACILITY WAREHOUSE SUPPLIER PO PO GROUP PURCHASE ORDER NUMBER PO TYPE BULK TO SPLIT REFERENCE PO LINE NUMBER ITEM NUMBER STYLE SIZE COLOR BRAND EX FACTORY ORIGINAL DATE REVISED EX FACTORY DATE DELIVERY METHOD ORDERED QTY COST PRICE PURCHASE PRICE PO LOW STATUS PO HIGH STATUS COUNTRY OF ORIGIN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 1 P001656-03162XL ALAYA-SF 2XL Juniper SK 20210809 0 OCEAN 3.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 2 P001656-0316L ALAYA-SF L Juniper SK 20210809 0 OCEAN 20.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 3 P001656-0316M ALAYA-SF M Juniper SK 20210809 0 OCEAN 25.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 4 P001656-0316S ALAYA-SF S Juniper SK 20210809 0 OCEAN 16.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 5 P001656-0316XL ALAYA-SF XL Juniper SK 20210809 0 OCEAN 12.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 6 P001656-0316XS ALAYA-SF XS Juniper SK 20210809 0 OCEAN 4.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29371 1 P001656-03162XL ALAYA-SF 2XL Juniper SK 20210809 0 OCEAN 3.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29371 2 P001656-0316L ALAYA-SF L Juniper SK 20210809 0 OCEAN 20.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29371 3 P001656-0316M ALAYA-SF M Juniper SK 20210809 0 OCEAN 25.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29371 4 P001656-0316S ALAYA-SF S Juniper SK 20210809 0 OCEAN 16.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29371 5 P001656-0316XL ALAYA-SF XL Juniper SK 20210809 0 OCEAN 12.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29371 6 P001656-0316XS ALAYA-SF XS Juniper SK 20210809 0 OCEAN 4.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29373 1 P001656-03162XL ALAYA-SF 2XL Juniper SK 20210809 0 OCEAN 3.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29373 2 P001656-0316L ALAYA-SF L Juniper SK 20210809 0 OCEAN 20.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29373 3 P001656-0316M ALAYA-SF M Juniper SK 20210809 0 OCEAN 25.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29373 4 P001656-0316S ALAYA-SF S Juniper SK 20210809 0 OCEAN 16.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29373 5 P001656-0316XL ALAYA-SF XL Juniper SK 20210809 0 OCEAN 12.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#29373 6 P001656-0316XS ALAYA-SF XS Juniper SK 20210809 0 OCEAN 4.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#427 1 P001656-03162XL ALAYA-SF 2XL Juniper SK 20210809 0 OCEAN 3.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#427 2 P001656-0316L ALAYA-SF L Juniper SK 20210809 0 OCEAN 20.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#427 3 P001656-0316M ALAYA-SF M Juniper SK 20210809 0 OCEAN 25.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#427 4 P001656-0316S ALAYA-SF S Juniper SK 20210809 0 OCEAN 16.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#427 5 P001656-0316XL ALAYA-SF XL Juniper SK 20210809 0 OCEAN 12.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT PO#427 6 P001656-0316XS ALAYA-SF XS Juniper SK 20210809 0 OCEAN 4.000000 58.750000 47.500000 20 20 CN
Here is the return result without MX.TFNO in place
SUPPLIER SUPPLIER NAME FACILITY WAREHOUSE SUPPLIER PO PO GROUP PURCHASE ORDER NUMBER PO TYPE PO LINE NUMBER ITEM NUMBER STYLE SIZE COLOR BRAND EX FACTORY ORIGINAL DATE REVISED EX FACTORY DATE DELIVERY METHOD ORDERED QTY COST PRICE PURCHASE PRICE PO LOW STATUS PO HIGH STATUS COUNTRY OF ORIGIN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 1 P001656-03162XL ALAYA-SF 2XL Juniper SK 20210809 0 OCEAN 3.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 2 P001656-0316L ALAYA-SF L Juniper SK 20210809 0 OCEAN 20.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 3 P001656-0316M ALAYA-SF M Juniper SK 20210809 0 OCEAN 25.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 4 P001656-0316S ALAYA-SF S Juniper SK 20210809 0 OCEAN 16.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 5 P001656-0316XL ALAYA-SF XL Juniper SK 20210809 0 OCEAN 12.000000 58.750000 47.500000 20 20 CN
5046 ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01 SK/FW21 5330000172 SPLIT 6 P001656-0316XS ALAYA-SF XS Juniper SK 20210809 0 OCEAN 4.000000 58.750000 47.500000 20 20 CN
Upvotes: 1
Views: 73
Answers (1)
Paul Kalathas
Reputation: 47
The issue found was actually an issue in the database which we needed the software developer to provide a fix. When the fix was installed and I ran the query the issues was resolved and the report for this was pushed into production environment and now being used.
Upvotes: 1
Related Questions
- Query is returning to rows instead of just one
- Select which returns multiple values doesn't work as expected
- Query returning the same row multiple times
- SQL subquery returned more than 1 result
- SQL Query returning multiple values
- Query returning multiple results. Only expecting 1.
- Results being returned in multiple rows
- SQL query returns same record multiple times
- Sql query returns single or multiple records?
- SQL Query returning repeating information