September 7, 2018 at 11:05 am
I have table named family with familyid,totalcount,adultcount,childcount
now i need to create derive table with following criteria
insert into derivefamily table familyid, type(adult,child,adult_child), count (can be adultcount, childcount or totalcount )
1. if totalcount is 5 with adultcount 3 and childcount 2 insert 2 records into dervive table with type as adult and count as 3 in 1st record and type as child and count as 2 in next record
2. if totalcount is 5 with adultcount as 5 and childcount as 0 then insert one record into derive table with type as adult and count as 5
3. if totalcount as 5 with adultcount as 0 and childcount as 0 then insert one record into derive table with type as adult_child and count as 5
Please advise the code.
September 7, 2018 at 11:11 am
saptek9 - Friday, September 7, 2018 11:05 AMI have table named family with familyid,totalcount,adultcount,childcountnow i need to create derive table with following criteria
insert into derivefamily table familyid, type(adult,child,adult_child), count (can be adultcount, childcount or totalcount )
1. if totalcount is 5 with adultcount 3 and childcount 2 insert 2 records into dervive table with type as adult and count as 3 in 1st record and type as child and count as 2 in next record
2. if totalcount is 5 with adultcount as 5 and childcount as 0 then insert one record into derive table with type as adult and count as 5
3. if totalcount as 5 with adultcount as 0 and childcount as 0 then insert one record into derive table with type as adult_child and count as 5Please advise the code.
Curious, what have you attempted so far to solve this problem?
September 8, 2018 at 4:22 pm
saptek9 - Friday, September 7, 2018 11:05 AMI have table named family with familyid,totalcount,adultcount,childcountnow i need to create derive table with following criteria
insert into derivefamily table familyid, type(adult,child,adult_child), count (can be adultcount, childcount or totalcount )
1. if totalcount is 5 with adultcount 3 and childcount 2 insert 2 records into dervive table with type as adult and count as 3 in 1st record and type as child and count as 2 in next record
2. if totalcount is 5 with adultcount as 5 and childcount as 0 then insert one record into derive table with type as adult and count as 5
3. if totalcount as 5 with adultcount as 0 and childcount as 0 then insert one record into derive table with type as adult_child and count as 5Please advise the code.
UNPIVOT or CROSS APPLY with a "0" rejection filter.
Since this is likely homework, give it a try on your own and post what you've tried. Please be sure to include readily consumable test data as you've been asked to do several times on other posts now. Please see the first link in my signature line below for one way to do that properly.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2018 at 10:05 am
saptek9 - Friday, September 7, 2018 11:05 AMI have table named family with familyid,totalcount,adultcount,childcountnow i need to create derive table with following criteria
insert into derivefamily table familyid, type(adult,child,adult_child), count (can be adultcount, childcount or totalcount )
1. if totalcount is 5 with adultcount 3 and childcount 2 insert 2 records into dervive table with type as adult and count as 3 in 1st record and type as child and count as 2 in next record
2. if totalcount is 5 with adultcount as 5 and childcount as 0 then insert one record into derive table with type as adult and count as 5
3. if totalcount as 5 with adultcount as 0 and childcount as 0 then insert one record into derive table with type as adult_child and count as 5Please advise the code.
>> I have table named family with family_id,family_cnt, adult_cnt,childcount <<
Why do you feel you don't have to post DDL for us? Did you read the forum etiquette? Since this table has summary data, it looks like it might have been a view in a properly designed schema now we got to start guessing because of your bad manners. You might also want to read some of the ISO standards on naming conventions; a table models a set of things. Therefore, its name has to be a plural or collective noun. You just told us you have only one family. You also don't seem to know the difference between rows and records. So here's my guess!
CREATE TABLE Families
(familly_id CHAR(10) NOT NULL PRIMARY KEY,
adult_cnt INTEGER DEFAULT 1 NOT NULL
CHECK (adult_cnt > 0),
child_cnt INTEGER DEFAULT 0 NOT NULL
CHECK (child_cnt >= 0),
family_cnt AS adult_cnt + child_cnt );
In particular, I want you to pay attention to the constraints and defaults I put on the columns. By definition, a table must have a key. Your narrative does not have any DDL, so there's no way could ever have a key. I'm going to assume that a family must have at least one adult member and may or may not have any kids.
We also have no idea what happens when a child turns 18 or 21 or whatever makes them into an adult. This table should have a date on it, but I'm leaving it off. Why are you carrying a total of two columns? You can either make it into computed column or do something in the view? There's no need to waste physical storage on it
>> now I need to create derive table with following criteria <<
No, this should be a view. Otherwise you're going to be constantly going over and updating the table every time the families change.
>> insert into derivefamily l family_id, type(adult,child,adult_child), count (can be adult_cnt, childcount or family_cnt ) <<
Why did you bother to create this "family_member_type"? In your original table, those "types" were attributes, not values. Do you understand the difference between an entity and attribute, and a value?
The rest of your posting has some other serious problems. Rows are nothing like records; it's a totally different concept. The rows in a table have no ordering, so talking about the n-th record makes no sense in RDBMS
>>1. if family_cnt is 5 with adult_cnt 3 and childcount 2 insert 2 records [sic] into dervive table with type as adult and count as 3 in 1st [sic] record [sic] and type as child and count as 2 in next record [sic]<<
Why do you say hate normal form so much? This is what happens when you don't know the difference between an attribute and a value and start confusing them. Every row in a table represents the fact about an entity. You're creating things out of thin air!
CREATE VIEW Families_of_Five
AS
SELECT familly_id, adult_cnt, child_cnt, family_cnt,
CASE WHEN child_cnt = 2 THEN 'type-1'
WHEN child_cnt = 0 THEN 'type-2
ELSE 'type-3' END AS family_type
FROM Families
WHERE family_cnt = 5;
>> 3. if family_cnt as 5 with adult_cnt as 0 and child_cnt as 0 then insert one record [sic] into derive table with type as adult_child and count as 5 <<
How does the family exist without any adults or children? Since the family count in my table is computed, the else clause in the case expression should never be executed. Do we count the family pets to get the family size?
Please post DDL and follow ANSI/ISO standards when asking for help.
September 10, 2018 at 1:29 pm
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply