Results 1 to 6 of 6

Thread: query

  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: query

    Hi,

    I am using db2 udb ese v8.1.4a on win2k platform.

    I have a table with the following details:

    TableA (Transactional table)
    (Col1 int,
    Col2 int
    Col3 varchar(30));

    TableA data:
    Col1 Col2 Col3
    1000 1000 Txt description
    1000 2000 Txt description
    1000 3000 Txt description
    1000 4000 Txt description
    1000 5000 Txt description
    ... ... ...
    ... ... ...
    ... ... ...
    1000 9000 Txt description
    1001 1000 Txt description
    1001 3000 Txt description
    1001 5000 Txt description
    1001 6000 Txt description
    1001 7000 Txt description
    1001 8000 Txt description

    TableB (Master Table):
    (Col1 int,
    Col2 varchar(30));

    TableB Master data:
    Col1 Col2
    1000 Txt description
    2000 Txt description
    3000 Txt description
    4000 Txt description
    5000 Txt description
    6000 Txt description
    7000 Txt description
    8000 Txt description
    9000 Txt description

    TableA contains transactional data sets.
    TableB contains master records which are static as shown above.

    Now the issue: In TableA for a single col1 value may contain values from master table i.e 1000 through 9000. Now I need to populate TableA with those missing records i.e for Col1 = 1001, I am missing master record entries for 2000, 4000 and 9000.

    My question is how do we make an entry into TableA for those missing record entries using SQL or otherwise. eg: In above case, I need to insert three rows in TableA with values
    1001, 2000, 'None'
    1001, 4000, 'None'
    1001, 9000, 'None'

    Appreciate your help. Please treat this as urgent. Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Posts
    456
    Not sure if you are looking for the syntax of insert command or an automated way to insert all the missing entries in table A. Well, for simple straight insert you can use this:

    insert into tableA values
    (
    1001, 2000, 'None',
    1001, 4000, 'None',
    1001, 9000, 'None'
    );

    and so on....for an automated way...you need to find out which column1 values do not contain 2000, 4000 and 9000 and use insert statement above. If they all do no contain those values then it's even simpler and the solution will depend on how many rows you will have to insert.

    HTH

    dollar

  3. #3
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    59
    Hi dude,
    I believe this is possible through many way, one way which i came out after reading ur post was this.

    1. first create a temp table(i will refer it as temp_table) with 2 columns (same as col1 of transaction and col1 of master table). The order when creating the table should be transaction column first and then the master column

    2. run the following query
    insert into temp_table select distinct a.col1,b.col1 from transaction a,Master b
    *This will get "all" combinations of transactions against each of the master in the temp_table.

    3. Alter table transaction to make col1,col2 as composite primary key.
    *This is important, to avoid duplicate entries in transaction.

    4. export from temp_table into ixf or csv file

    5. import into transaction table from ixf/csv file.
    *Because of the composite primary key in transaction table, duplicate entries will not be loaded and the rest will successfully be inserted.

    Note: If you need to insert values to the third column of transaction table then you can create the temp_table with 3 columns instead of 2 and change the query in step 2 as following

    insert into temp_table select distinct a.col1,b.col1,'some text' from transaction

    The only drawback to this method is the values of the third column need to be static. From your post I couldnt understand what kind of data would be there in column3

    HTH
    brat.

  4. #4
    Join Date
    Sep 2003
    Posts
    218
    Many thanks for your response.. Few explanations though.

    * TableA.col3 = TableB.Col2 and it is static in nature.

    * Simple insert statements (as shown below) doesn't solve the issue.

    * Brat4: I agree with your approach, I am constructing the temp table to process the same. But, no where close the result...

    The whole point is, the user can have any combination of entries in transactional table for instance: for a single value of col1 (1001 or any other id as generated by the application) he can have 9 possible values (coming from master table which is static in nature). However, it is not mandatory to have all the 9 records...so, our task is to find out which one of those record are missing from the combination and insert the same into TableA.

    Any help is greatly appreciated.

  5. #5
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    59
    Hi,

    Maybe I didnt percieve ur requirement .. .. I'll just iterate what i understand .
    "1. transaction table contains many values in col1 -dynamic.
    2. transaction table contains col2 - from master table
    3. master table contains 9 records in col1
    4. transaction table has col2 which MAY OR MAY NOT have all the combination of each of transaction.col1 with a value of master.col1

    Requirement: transaction table needs to be updated with the combination of each transaction.col1 along with each of master.col1
    ..

    am I right in the above?

    If yes read the following , if not corect then I'd require hmmm a little more detail

    Currently the transaction table has any combinations (tarnsaction.col1 along with master.col2) and to push in the missing combinations, the method I described below should work.

    The temp table (using the query in step 2) will contain all the possible combinations (including the ones already exisiting in the transaction table). When you import it Using your db2 control center or clp then the rows which are already exisitng would be rejected due to the constraint as described in step 3 As a result, all the missing entries will be inserted.

    But if you need to run it as a regular job, then maybe you could do it with a combination of a OS cron job (which imports into transaction table from a specific file generated by a stored procedure (which populates the temp_table and exports the ixf file)

    regards
    brat.
    Last edited by brat4; 07-05-04 at 04:19.

  6. #6
    Join Date
    Sep 2003
    Posts
    218
    Brat4 -- Thanx a ton for the quick response!!! Your solution was good enough - it worked. Data is getting migrated without any issue...

    Regards.

Posting Permissions

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