asked on

How to use coalesce function or a better practice or option

When I encounter a duplicate PN in this case example 001030-503, I want to choose the one with MFG_CODE = 'SG361' and MFG_NAME = 'AEROLITE MAX BUCHER AG', not MFG_CODE '99999' and MFG_NAME = 'DEFAULT'. I need the select statement to do this for all duplicate PN's that contain a MFG_CODE = 99999 with it's corresponding MFG_NAME.

Would coalesce be a good option? If yes, what is best practice?
data_example.csv
Capture.PNG

Bill Prew

Your image doesn't seem to match the question?

»bp

Bill Prew

This doesn't really feel like an obvious use for COALESCE, it's more designed to weed out NULL values, and find the first non NULL value among several choices, like if you had several phone numbers for a person, and wanted to take the first non NULL one you find, but looking in a particular order (home, office, cell, ...).

Since you don't have any NULL values to work around COALESCE may not be a good fit. There *may* be a way to JOIN the data in such a way NULLs could be produced, but it isn't obvious yet.

»bp

Bill Prew

You probably need to provide more info too, for experts to be helpful. Some questions that came to my mind:

Will "duplicate" PN's only be two rows, or could there be any number of rows for the same PN?
What if there isn't a duplicate, but it is the 99999 row, just let that through to the results?
What columns do you want in the results?
What if there are duplicates, but none are a 99999 row, what then?

»bp

maximus1974

ASKER

Bill, Sorry about that, uploaded new screenshot. The data example is correct. Here are the answers to the questions:

Will "duplicate" PN's only be two rows, or could there be any number of rows for the same PN?
Yes
What if there isn't a duplicate, but it is the 99999 row, just let that through to the results?
Yes, let it through to the results
What columns do you want in the results?
[PN]
,[DESCRIPTION]
,[MFG_CODE]
,[MFG_NAME]
,[CMM]
,[MIA]
,[PHX]
,[SDF]
,[DOR]
,[MED]
,[ATL]
,[GSTE]
,[BIC_2YR]
,[ARO_2YR]
,[PEL_2YR]
What if there are duplicates, but none are a 99999 row, what then?
Please let them through to the results.

SELECT [PN]
      ,[DESCRIPTION]
      ,[MFG_CODE]
      ,[MFG_NAME]
      ,[CMM]
      ,[MIA]
      ,[PHX]
      ,[SDF]
      ,[DOR]
      ,[MED]
      ,[ATL]
      ,[GSTE]
      ,[BIC_2YR]
      ,[ARO_2YR]
      ,[PEL_2YR]
  FROM BA_VIEW_INTERNAL_CAPA_MATRIX

Open in new window

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial