SQL table missing values
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 28, 2009 2:33 PM
UPDATED:
August 26, 2009 7:05 AM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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 ?
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.
Thanks Broeren.
By “what database” I meant, what RDBMS (i.e. Oracle, SQL Server, etc…)are you using ?
Sorry, misunderstood your question. i am working with sql2005.
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….
Could you please clarify if any of the suggested solutions is suitable for you ?
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!