SQL table missing values
Home > IT Answers > Development > SQL table missing values
Broeren79
100 pts.
0
Q:
SQL table missing values
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?
ASKED: Jul 28 2009  2:33 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Carlosdl
29820 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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.

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


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

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


Please the field names as needed.

--------------------------

Answer by TaulPall

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
Last Answered: Aug 21 2009  1:56 PM GMT by Carlosdl   29820 pts.
Latest Contributors: TaulPall   35 pts., Broeren79   100 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29820 pts.  |   Jul 28 2009  8:56PM GMT

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 ?

 

Broeren79   100 pts.  |   Jul 29 2009  6:30AM GMT

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.

 

Carlosdl   29820 pts.  |   Jul 29 2009  1:05PM GMT

Thanks Broeren.

By “what database” I meant, what RDBMS (i.e. Oracle, SQL Server, etc…)are you using ?

 

Broeren79   100 pts.  |   Jul 31 2009  10:20AM GMT

Sorry, misunderstood your question. i am working with sql2005.

 

Broeren79   100 pts.  |   Aug 24 2009  6:35AM GMT

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….

 

Carlosdl   29820 pts.  |   Aug 25 2009  2:54PM GMT

Could you please clarify if any of the suggested solutions is suitable for you ?

 

Broeren79   100 pts.  |   Aug 26 2009  7:05AM GMT

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!

 
0