Add missing value with Case Statement
I have a view that when the MFR_IMPTR value is missing, it can grab it from a row in the table that has that value using a case statement where the value matching is matching on the serial number. I have tried the following case statement in my query, but it gives me the following error.
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." any help would be greatly appreciated.
SELECT TOP 100 PERCENT
CASE
WHEN MFR_IMPTR =''
THEN (SELECT MFR_IMPTR FROM SSG_ADRPT S2 WHERE S2.SERLNMBR = SERLNMBR AND MFR_IMPTR <>'')
ELSE MFR_IMPTR
END AS MFG_NAME,
--Replace(MFR_IMPTR,',',' ') AS MFG_NAME,
SERLNMBR AS SERIAL_NO,
'' AS MANUFACTURE_DATE,
'' AS IMPORT_DATE,
ACQ_DATE AS ACQUISITION_DATE,
ISNULL(ACQ_COUNTRY,'') AS MFG_COUNTRY,
[FFL Number] AS ORIGINATING_FFL,
Replace(ACQ_NAME,',','') AS ACQUIRED_FROM_NAME,
Replace(ACQ_ADD1,',',' ') AS ACQUIRED_FROM_ADDR1,
Replace(ACQ_ADD2,',',' ') AS ACQUIRED_FROM_ADDR2,
Replace(ACQ_CITY,',',' ') AS ACQUIRED_FROM_CITY,
Replace(ACQ_ST,',',' ') AS ACQUIRED_FROM_STATE,
Replace(ACQ_ZIP,',',' ') AS ACQUIRED_FROM_ZIP,
ISNULL(ACQ_FFL,'') AS ACQUIRED_FROM_LICENSE_NO,
--COALESCE(REPLACE(ACQ_IMPORTER,',',''),'')AS IMPORTER
REPLACE(ISNULL(ACQ_IMPORTER,''),',','')AS IMPORTER
FROM (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (PARTITION BY SERLNMBR ORDER BY ACQ_DATE DESC) AS RowNum, *
FROM SSG_ADRPT S1 INNER JOIN
EXT_FFLSETUP ON S1.ReportingFFL_ID = EXT_FFLSETUP.ID
WHERE ( S1.ACQ_DOCTYPE IN('SOPReturn', 'PO')) AND SERLNMBR NOT LIKE '%[^a-zA-Z0-9 ^-]%' AND ACQ_ITEMNMBR NOT LIKE '%TSAG2%'AND SERLNMBR NOT LIKE '0' AND SERLNMBR = 'XH003549'
) X
WHERE RowNum = 1
ASKER
I already tried THEN (SELECT TOP 1 MFR_IMPTR FROM SSG_ADRPT S2 WHERE S2.SERLNMBR = SERLNMBR AND MFR_IMPTR <>'') it just pulls the first rows MFR_IMPTR which is wrong and THEN (SELECT MIN(MFR_IMPTR) FROM SSG_ADRPT S2 WHERE S2.SERLNMBR = SERLNMBR AND MFR_IMPTR <>'') just pulls a random MFR_IMPTR.
This is my original code filter on a serial number that is missing the MFR_IMPTR; I need the missing data to be populated mached to the serial number where the MFR_IMPTR has a value
SELECT TOP 100 PERCENT
Replace(MFR_IMPTR,',',' ') AS MFG_NAME,
SERLNMBR AS SERIAL_NO,
'' AS MANUFACTURE_DATE,
'' AS IMPORT_DATE,
ACQ_DATE AS ACQUISITION_DATE,
ISNULL(ACQ_COUNTRY,'') AS MFG_COUNTRY,
[FFL Number] AS ORIGINATING_FFL,
Replace(ACQ_NAME,',','') AS ACQUIRED_FROM_NAME,
Replace(ACQ_ADD1,',',' ') AS ACQUIRED_FROM_ADDR1,
Replace(ACQ_ADD2,',',' ') AS ACQUIRED_FROM_ADDR2,
Replace(ACQ_CITY,',',' ') AS ACQUIRED_FROM_CITY,
Replace(ACQ_ST,',',' ') AS ACQUIRED_FROM_STATE,
Replace(ACQ_ZIP,',',' ') AS ACQUIRED_FROM_ZIP,
ISNULL(ACQ_FFL,'') AS ACQUIRED_FROM_LICENSE_NO,
--COALESCE(REPLACE(ACQ_IMPORTER,',',''),'')AS IMPORTER
REPLACE(ISNULL(ACQ_IMPORTER,''),',','')AS IMPORTER
FROM (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (PARTITION BY SERLNMBR ORDER BY ACQ_DATE DESC) AS RowNum, *
FROM SSG_ADRPT S1 INNER JOIN
EXT_FFLSETUP ON S1.ReportingFFL_ID = EXT_FFLSETUP.ID
WHERE ( S1.ACQ_DOCTYPE IN('SOPReturn', 'PO')) AND SERLNMBR NOT LIKE '%[^a-zA-Z0-9 ^-]%' AND ACQ_ITEMNMBR NOT LIKE '%TSAG2%'AND SERLNMBR NOT LIKE '0' AND SERLNMBR = 'XH003549'
) X
WHERE RowNum = 1
GO
this is the results.
This code gives me what I need, but I do not know how to add it to my query to replace all the serials with missing MFR_IMPTR
SELECT S1.MFR_IMPTR
FROM SSG_ADRPT AS S1 INNER JOIN
(SELECT SERLNMBR
FROM SSG_ADRPT AS S2) AS S ON S1.SERLNMBR = S.SERLNMBR
GROUP BY S1.MFR_IMPTR, S1.SERLNMBR
HAVING (S1.SERLNMBR = N'XH003549') AND S1.MFR_IMPTR <>''
... WHERE S2.SERLNMBR = S1.SERLNMBR ...
ASKER
Scott,
That was it. I tried that query but I didn't use the X alias on the joined SERLNMBR I was trying everything but, what an idiot.
As always you have come through, Thanks.
Thanks. The big problem is that if you don't use a table name/alias on a column in a subquery, SQL uses the column from the subquery table if possible. Thus, the first query was comparing the S2.SERLNMBR to itself, and so returning any random MFR_IMPTR
ASKER
Yeah, that makes sense I had forgotten that the whole query was aliased as X.
Thanks again I was pulling my hair out, LOL
So you need to decide what one you want.
If they are all the same, just add:
THEN (SELECT TOP 1 MFR_IMPTR FROM SSG_ADRPT S2 WHERE S2.SERLNMBR = SERLNMBR AND MFR_IMPTR <>'')
or MIN/MAX:
THEN (SELECT MIN(MFR_IMPTR) FROM SSG_ADRPT S2 WHERE S2.SERLNMBR = SERLNMBR AND MFR_IMPTR <>'')
That get's you past the error BUT I hate accesing the table more than once when you probably don't have to.
If you can provide a simplified data example of what you are trying to do, I'm sure we can get you the same output without the inline select against the same table.
Maybe something like this instead of the CASE:
select
...
max(MFR_IMPTR) over(partition by SERLNMBR) AS MFG_NAME,
...