Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    54

    Unanswered: Duplicates n times a rocord

    Hi Guys,
    I'm in need of your help.
    This is what I have to do:
    I 've to clone all the fields stored in a table:
    acc1:
    Code:
    accounting                        other_fields           length
    soomecode1 somecode2           x                        2
    where accounting stores information which will be splitted in two or more records;
    Last edited by jsirico; 02-03-12 at 09:49.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you mean clone (IE an exact copy of the existing data, if so why?)

    you'd need to give mroe information than you have currently
    such as what data are you moving from and to
    somecode1 somecode2 x 2
    is hardly helpful.
    or do you mean you need to split accounting with a value of
    'somecode1 somecode2' into
    somecode1 x 2
    somecode2 x 2
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2012
    Posts
    54
    Ok I'm agree with you;
    Lets say that they have committed me to clone data;
    The reason is that there is a field which stores information that would be more readable if each part of this fields will stand alone ;
    I'll try to explain it using a table:
    Code:
    accounting                        other_fields           length
    soomecode1 somecode2           x                        2
    where the fields Lenght shows the number of "codes" stored in one record,
    and Id like to have somethings like this:

    Code:
    accounting                        other_fields           length
    soomecode1                           x                        1
    somecode2                           x                        1
    The fact is that I can't do an select * into the same table because there are some fields that have more than 2 code per record, so I'd like to find at least a way to clone my data in function of the fields lenght.. In others words I'd like to duplicates N times A recod!!!!!!
    is it more clear??????
    Last edited by jsirico; 02-03-12 at 09:48.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when you say current codes have more than one code in them does that mean you only have a maximum of two per block or could you have say 3, or even more
    splitting 2 or 3 codes is doable via an SQL insert into , 4 or more would I suspect require manual intervention
    it would help f you could give some sample data
    what separates code1,code2...codeN
    would you want to retain the original multi code AS well as the new codes
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2012
    Posts
    54
    Here is an example of my table when exactly starts the problems of the groth of the codes!!!!
    Code:
    Accounting                                                                                                                 lenght
    d40220100 c40320100	                                                                                                   2
    d40220100 c40320100                                                                                                     2
    d40220000 c40320000	                                                                                                   2
    d40220000 c40320000	                                                                                                   2
    d40220100 c40320100	                                                                                                   2
    d40220100 c40320100	                                                                                                   2
    d40220200 c40320200	                                                                                                   2
    d40220200 c40320200                       	                                                                           2
    d40200000 c70000000 d40301009 c74090000 d64005100 c40301003 d64025200 c40301001	9
    d40200000 c70000000 d40301009 c74090000 d64005100 c40301003 d64025200 c40301001	9
    d40200100 c70000000 d40301009 c74090000 d64005100 c40301003 d64025200 c40301001	9
    d40200100 c70000000 d40301009 c74090000 d64005100 c40301003 d64025200 c40301001	9
    d40200200 c70000000 d40301009 c74090000 d64005100 c40301003 d64025200 c40301001	9
    Last edited by jsirico; 02-03-12 at 10:06.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are these codes fixed length?
    ie always 9 characters?
    what you could do is write a series of queries that progressively chop out blocks of 9
    eg:-

    'extract first code
    Code:
    INSERT INTO Codes ( Code, NoCodes )
    SELECT mid(Code,1,9) AS NewCode, 1 AS NewCodeLength
    FROM codes
    WHERE len(codes.[NoCodes])>9);
    'then modify that for each remaining block

    'extract code 2
    Code:
    INSERT INTO Codes ( Code, NoCodes )
    SELECT mid(Code,11,9) AS NewCode, 1 AS NewCodeLength
    FROM codes
    WHERE len(codes.[NoCodes])>=19);

    'extract code 3
    Code:
    INSERT INTO Codes ( Code, NoCodes )
    SELECT mid(Code,21,9) AS NewCode, 1 AS NewCodeLength
    FROM codes
    WHERE len(codes.[NoCodes])>=29);
    'and so on
    you'd need to change the start point for the mid fucntion from 1,11,21 to alwasy be the start of the next code block eg 31, 41, 51, 61
    you'd need to change the where statement to reflect the length of the record that may have a value in (as your codes are 9 characters long then its on 9 or greater boundariews ie 9,19,29,39 and so on

    but that will work ONLY if your codes are 9 character with one space, if they are floating format then you are going to have to do some other method.

    if you had some records with more than one space between them then you'd need to pre process the codes to standardise at 10 character intervals

    if this is a regualr process then you coudl create a macro which ran a block of queries in succession
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2012
    Posts
    54
    ok I need to think about this, just the last question: this will automatically add the the new records to my table?
    sorry to insist is not possible to create something like this:
    If lenght=2
    than insert * into mytable from mytable
    if lenght = 4
    than copy ' the same record 3 times!!!!
    if lenght =11 than copy the same records 10 times!!!!


    ?????
    and just after add to the fields the right values using the mid function!!!!!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry this sounds like changing the requirement on the fly
    if you butcher those queries you will be able to split off specific codes and insert them into your table as single codes with a lenght of one. if you have a maximum of, say, 10 codes then you'd need 10 queries to chop each individual code and insert it into the table

    if you need to say start with 10 codes you could modify the query to write say
    9 codes, 8 codes, 7 codes and so on

    as to whether they work or not why not try 'em on some test data at your end
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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