SQL table missing values

100 pts.
Tags:
SQL
Dear all, I have a table that typically has the following collumns: partnumber Company Division Responsible Records usually look like: partnumber company division responsible A 1 14 AAA A 1 13 BBB A 1 CCC B 1 14 AAA B 1 12 BBB B 1 DDD Now I need to create a query, that returns the following: A 1 14 AAA A 1 13 BBB A 1 12 CCC A 1 11 CCC B 1 14 AAA B 1 13 DDD B 1 12 BBB B 1 11 DDD So, in words, there are 4 possible combinations for Company/division, 1/11, 1/12, 1/13, 1/14. If the combination is available in the table, it needs to show as is. If the record is not shown, e.g. p/n A, comp 1, division 11 the query needs to show a record that takes the responsible from the record that does show company 1, but has a blank division. in the end, a record should be uniquely identified by the combination of partnumber/company/division Any idea how I would go about that?

Answer Wiki

Thanks. We'll let you know when a new response is added.

I think you will need to create a function.

I will assume that you have a lookup table for your divisions, called “division”, and that 1 and only 1 record with null division will exist for each partnumber-company combination.

This is not very efficient, because the function needs to be executed for each row, and some other way to do it should exist.

<pre>CREATE FUNCTION get_responsible
( @partnumber varchar(50),
@company_id integer,
@division_id integer
)
RETURNS varchar(50)
AS
BEGIN
DECLARE @Result varchar(50)
SELECT @Result=responsible
FROM part
WHERE partnumber=@partnumber
AND company_id=@company_id
AND division_id=@division_id
IF @@ROWCOUNT = 0
SELECT @Result=responsible
FROM part
WHERE partnumber=@partnumber
AND company_id=@company_id
AND division_id IS NULL
RETURN @Result
END
GO</pre>

Then, you use the function in your query, like this:

<pre>SELECT p.partnumber,p.company_id,
d.division_id,<b>dbo.get_responsible(p.partnumber,p.company_id,
d.division_id)</b>
FROM part p JOIN division d
ON p.division_id IS NULL
ORDER BY 1,2,3 DESC</pre>

Please the field names as needed.

————————–

<b>Answer by TaulPall</b>

Here’s a generic (platform agnostic) example:

create table bogus (
partnumber char,
company int,
division int,
responsible varchar(3)
);

insert into bogus values(‘A’, 1, 14, ‘AAA’);
insert into bogus values(‘A’, 1, 13, ‘BBB’);
insert into bogus values(‘A’, 1, null, ‘CCC’);
insert into bogus values(‘B’, 1, 14, ‘AAA’);
insert into bogus values(‘B’, 1, 12, ‘BBB’);
insert into bogus values(‘B’, 1, null, ‘DDD’);

/* uses nested (derived) queries */

select * from
(select * from bogus where division is not null) d
union
(select
b.partnumber
,b.company
,c.division
,b.responsible
from
( select distinct
*
from
bogus
where
division is null
) b
,( select
*
from
( select 11 as division
union
select 12 as division
union
select 13 as division
union
select 14 as division
) a
) c
where
b.partnumber+cast(b.company as char(1))+cast(c.division as char(2))
not in ( select
partnumber+cast(company as char(1))+cast(division as char(2))
from
bogus
where
division is not null
)
)
order by
partnumber
,division desc

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    What database are you using ? Do you want to do this with pure SQL, or would you use a stored procedure if needed ? For each part number, is it not possible to have more than one record with null division ?
    68,795 pointsBadges:
    report
  • Broeren79
    Hi, Thanks for your reply. I am using the production database for our ERP system. Let's call it "DUMMY". I need it in pure SQL, since I need to join the result with another query. The result is for reporting purposes. The table itself should not undergo any changes.
    100 pointsBadges:
    report
  • carlosdl
    Thanks Broeren. By "what database" I meant, what RDBMS (i.e. Oracle, SQL Server, etc...)are you using ?
    68,795 pointsBadges:
    report
  • Broeren79
    Sorry, misunderstood your question. i am working with sql2005.
    100 pointsBadges:
    report
  • Broeren79
    perhaps i have not mentioned it clear enough. I can not make updates or changes to the database, since it is a database belonging to an external erp-system, and support will be void if changes to the database structure is made. Thus i simply need a select-type query that retrieves the info....
    100 pointsBadges:
    report
  • carlosdl
    Could you please clarify if any of the suggested solutions is suitable for you ?
    68,795 pointsBadges:
    report
  • Broeren79
    i think the solution with creating the "bogus"-table might work, if I change it into a view. That'll allow me to join it to the rest of the query without making changes to the table structure. Thanks you all!
    100 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following