Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Breaking column data using file layouts

    Hi DBA's,

    I need to break up a 34 byte column into several columns. Here is my problem.

    Table A:

    Col1..Col2..Col3..Col4..Col5..Col6..Col7..Col8..Co l9..Col10

    Col 9 is char (2) and Col10 is char (34). It is this column that needs to be broken up into several columns depending on the value in Col 9.

    Col1 to Col3 is the key to the record.

    So say if record 1 has Col 9 value 'AA' then Col10 ( 34 bytes) is to be spilt into 10+10+10+4 (four columns).The value 'AA' can repeat for several records and the value in Col 10 can change for the same value 'AA'.

    Now say record 27 has Col 9 value 'BB' then Col 10 is to be split as 5+25+4 (3 columns).

    There are 15 such unique values of Col 9. I have the file layouts for Col 10 for each distinct value of Col 9. So using the file layouts and Table A which exists in my database how do I proceed.

    Need I make 15 tables ( one each for the 15 unique Col 9 values). These structure
    Col1..Col2..Col3...Col9 (the key fields and Col 9) will be common to every table. Plus the file layouts will serve as additional columns specific to each of these tables.

    Kindly help.

    Vivek

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The logical answer would be to load the ten columns into a staging table that will become the "base" table for your design. Create 15 tables that contain the PK from the base table, and the additional columns needed for each sub-type of data. Make 15 passes through the base table, filtering on one sub type (one value in column 9) per pass and split the tenth column into N additional columns based on the sub type.

    Whew! Not really complex, but tough to describe when I don't know the underlying details of what you're doing.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Boy, there's lots of ways to handle this and none of them are too pretty. In addition to Pat's solution, another possibility is a UNION query with 15 different SELECT statements filtering your dataset by column 9


    select col1, col2, .... col9, left(col10, 10) as col10a, mid(col10, 11, 10) as col10b.....
    where col9 = 'AA'
    UNION
    select col1, col2, .... col9, left(col10, 5) as col10a, mid(col10, 6, 25) as col10b.....
    where col9 = 'BB'
    UNION
    .
    .
    .
    blah blah blah...

    A lot depends on what you want to do with the data when you are done. Store it in a new table? Present it immediately to an interface? Is this a one-time deal or will it be a scheduled or repeated task?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    this looks like an extract from a legacy system. of course you need 15 tables. i don't think union is appropriate here. and you will have to fire 15 insert...select to populate those tables.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Of course?

    You don't think?

    Of course you don't think.

    How about some explanation for your opinion?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    are you fighting everything that moves around here today?

    ok, i'm nice, so i'll just explain, since you're so persistent

    your union solution will not work because vivek will have to store the results of this parsing excersise somewhere. how would your union contribute to this particular task?

    and...i do think with the same head all the time, so plz skip the reply before you embarrass yourself

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There is nothing that prevents one from storing the results of a union query.

    And since you didn't pay attention to my previous post, I'll quote it again for you:

    "A lot depends on what you want to do with the data when you are done. Store it in a new table? Present it immediately to an interface? Is this a one-time deal or will it be a scheduled or repeated task?"

    Please don't expect me or anyone else to let you get away with saying "Of course" a solution is wrong or inappropriate and not give a shred of information to back up what is clearly just your personal opinion.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    i think you've had enough for today. may be you need to go rest, have a beer or two, just make sure the bar is not too far from your house. or you can always call your wife to pick you up if you can't stop at 2...but then again, there may be no wife to call for a lift, with this costic attitude...do you have friends? acquantences? i just don't feel like fighting with you, it's not my nature, and i hope it's not yours either, just a very long day...right, mate?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm sorry, was there an explanation or technical fact burried in there? Perhaps in some secret code? "Of course" not.

    You do the "Passive Aggressive" thing very well...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Sep 2003
    Posts
    522
    and you are just good at aggressive, huh? i'm not giving anything to someone like you because you're planning to sit here and defend your union until the last person finally falls exhasted off the chair. and pat already answered the question many hours ago, and vivek is not even on any more because pat's answer was confirming vivek's hunch on what the solution needs to be, and you...goodnight, mate, have another one on me

  11. #11
    Join Date
    Sep 2003
    Posts
    176
    DBA's,

    You asked : "A lot depends on what you want to do with the data when you are done. Store it in a new table? Present it immediately to an interface? Is this a one-time deal or will it be a scheduled or repeated task? "

    The data is legacy data from AS400. The data is made available every month-end. I extract this data and store it in Table A. I am building a warehouse. So I need to fetch the data for the past months ( 2 years worth) and store it in Table A. Now I mentioned that Col1..Col3 is the key. Now the key is for one month only. So if I get the data for several months then I have Col5 (Month End Date) which will give the month the data is for. So I will need to make this the key as well,correct? I will have 15 new tables of the format-

    Col1..Col2..Col3..Col5...Col9..Col10a..Col10b..10c ...10d

    Col1..Col2..Col3..Col5...Col9..Col10a..Col10b..10c ...10d..10e..10f




    I need to store the 15 unique file layouts in a separate table. Also Col10 is split into more than al least more than 5 columns for every unique value of Col9. So just using left,mid and right won't work. I may need to use substring. Can you help me with this info.

    Thanks,

    Vivek

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The only place that I can see that might need to know about the "splitting details" is the script to actually do the split itself. This isn't rocket science at least in my opinion anyway.

    With a four column key, I'd be sorely tempted to create a surrogate key using the identity attribute (even though that would drive r937 mad), and put a unique constraint on the other four columns (the real key). This is primarily because I'm lazy, and it is easier to track one column than it is to track four!

    -PatP

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think I'd build a rules table, put all of your rules in it..

    Then build dynamic sql to populate your table

    Do you know the max number of columns of Col10?

    CREATE TABLE (Col4Value varcxhar(20), TargetColumn sysname, ColStartPos int, ColLength int)

    The generate sql based on this table...

    You could either create a view or execute INSERTS
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Wow, sounds like Ms. SQL DBA was right on the target, - legacy system extract, 15 tables, etc.

    I'd join that solution with parsing against the staging table, and then doing 15 inserts based on the record type. And of course (hope Lindman is not around) pick a 1-field PK/FK. Identity sounds perfect, Pat.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd say she's strong on guessing, but short on design, sql coding, and solution-providing.

    Vivek,
    You asked
    "Need I make 15 tables ( one each for the 15 unique Col 9 values). "

    I think you should consider making 16 tables. One to hold the common fields and additional to hold the unique fields - assuming that none of these formats share any common attributes in column 10.

    If some of these formats do share common fields, then consider a single table holding all the columns required to support all the formats. The Union query would be one option for filling such a table.

    A description of the data you are storing, its purpose, and the reason why the content of column 10 is so volatile would be helpful.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •