Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: Insert and count with condition.

    I have a problem here..
    I have two tables and I am trying to copy from Table1 into 2 and also count some records and insert into Table1
    Maybe any of u can help me here

    Table 2 Table 1
    SERIAL_NUMBER
    VENDOR_ID PROD_ID Copy
    BATCH_NO BATCHNR Copy
    DATE_GENERATED DATE_GENERATED Copy
    DATE_VALID_TO DATE_VALID_TO Copy
    STATUS STATE Copy
    DATE_STATUS_CHANGE DATE_LSTATE Copy
    VALUE VALUE Copy
    AMOUNT Count from column status where only records with status P
    QUANTITY QUANTITY Copy
    NO_OF_VOUCHERS Count all serial numbers from column Table1.serial number


    insert into Table2 value(
    select PROD_ID, BATCHNR, DATE_GENERATED, DATE_VALID_TO, STATUS, DATE_LSTATE, VALUE
    from Table1
    minus
    select VENDOR_ID, BATCH_NO, DATE_GENERATED, DATE_VALID_TO, STATUS, DATE_STATUS_CHANGE, DENOMINATION from Table2)


    I have done this but I got stuck with the count.. I dun know where to place it in the code and how to do the count with condition moreover i.e: count only from column status with status 'p'.. Anyone could help

    Urgently need this going !!

  2. #2
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    sorry the above is not clear

    Table 2
    VENDOR_ID **Copy from table1.PROD_ID
    BATCH_NO **Copy from table1.BATCHNR
    DATE_GENERATED **Copy from table1.DATE_GENERATED
    DATE_VALID_TO **Copy from table1.DATE_VALID_TO
    STATUS **Copy from table1.STATE
    DATE_STATUS_CHANGE **Copy from table1.DATE_LSTATE
    DENOMINATION **Copy from table1.VALUE
    AMOUNT **Count from column table1.STATE where only records with status 'P'
    QUANTITY **Copy from table1.QUANTITY
    NO_OF_VOUCHERS **Count all serial numbers from column Table1.SERIAL_NUMBER

    Table 1
    SERIAL_NUMBER
    PROD_ID
    BATCHNR
    DATE_GENERATED
    DATE_VALID_TO
    STATE
    DATE_LSTATE
    VALUE
    QUANTITY

  3. #3
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    any idea ?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, this might (or might not) work ... I don't understand what will you do with the same data stored into the "amount" and "no_of_vouchers" columns in all "table_2" rows. Of course, it is possible that I didn't understand your question at all ...
    Code:
    INSERT INTO table_2
                (vendor_id, batch_no, date_generated, date_valid_id, status,
                 date_status_chanage, denomination, quantity, amount,
                 no_of_vouchers)
       (SELECT prod_id, batchnr, date_generated, date_valid_to, state,
               date_lstate, VALUE, quantity, 0, 0
          FROM table_1);
    
    UPDATE table_2
       SET amount = (SELECT COUNT (*)
                       FROM table_1
                      WHERE status = 'P'),
           no_of_vouchers = (SELECT COUNT (DISTINCT serial_numbers)
                               FROM table_1);

  5. #5
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    Littlefoot,
    I managed to confirm and get some sample data, was wondering about what you said.. and yes, its wrong..
    the below is correct..
    Last edited by shatishr; 09-26-05 at 07:07.

  6. #6
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    Littlefoot,
    I managed to get some sample data today

    I managed to get the sample data

    The table1 looks like this,

    PROD_ID, BATCHNR, SERIAL_NUM, DATE_GENERATED, DATE_VALID_TO, STATE, DATE_LSTATE, VALUE, QUANTITY
    001,00011520,1112,8/22/2005,1/1/2007,P,8/22/2005,30,1
    001,00011520,1113,8/22/2005,1/1/2007,P,8/22/2005,30,1
    001,00011520,1114,8/22/2005,1/1/2007,P,8/22/2005,30,1
    001,00011520,1115,8/22/2005,1/1/2007,W,8/22/2005,30,1
    001,00011521,1116,8/22/2005,1/1/2007,P,8/22/2005,30,1
    001,00011521,1117,8/22/2005,1/1/2007,P,8/22/2005,30,1


    The table2, It should look like this:

    VENDOR_ID, BATCH_NO, DATE_GENERATED, DATE_VALID_TO, STATUS, DATE_STATUS_CHANGE, VALUE, AMOUNT(**calculated based on number of 'p' in a particular batch), QUANTITY, NO_OF_VOUCHERS(**calculated based on the number OF SERIAL_NUM IN A BATCH)
    001,00011520,8/22/2005,1/1/2007,P,8/22/2005,30,3,4
    001,00011520,8/22/2005,1/1/2007,W,8/22/2005,30,1,4
    001,00011521,8/22/2005,1/1/2007,P,8/22/2005,30,2,2

    Hope u can assist me here
    Last edited by shatishr; 09-26-05 at 07:02.

  7. #7
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    i did this
    but its giving me some error on the partition part and its not counting the serial_numbers according to the batch as well.... since i want it all summarized..
    can u guys check this for me ??

    INSERT INTO TRAP_RA_VCA_SC_P (VENDOR_ID, BATCH_NO, DATE_GENERATED, DATE_VALID_TO, STATUS, DATE_STATUS_CHANGE, DENOMINATION, AMOUNT, QUANTITY, NO_OF_VOUCHER)
    (SELECT DISTINCT PROD_ID, BATCHNR, DATE_GENERATED, DATE_VALID_TO, STATUS, DATE_LSTATE, VALUE,
    (SELECT count(*) FROM TRAP_STAGE_VCA_SC_P WHERE STATUS = '2') , QUANTITY, (SELECT sum(SERIAL_NUMBER) FROM TRAP_STAGE_VCA_SC_P) FROM TRAP_STAGE_VCA_SC_P over (partition by PROD_ID, BATCHNR)
    MINUS
    SELECT VENDOR_ID, BATCH_NO, DATE_GENERATED, DATE_VALID_TO, STATUS, DATE_STATUS_CHANGE, DENOMINATION, AMOUNT, QUANTITY, NO_OF_VOUCHER FROM TRAP_RA_VCA_SC_P)


    thanks
    (the desired output is in the above thread)
    Last edited by shatishr; 09-27-05 at 23:26.

  8. #8
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    maybe this attachment would be clearer with the screen shots
    i has my staging table and how the real table should look like with the summarized data
    Attached Files Attached Files

  9. #9
    Join Date
    Sep 2003
    Posts
    4

    u can try this

    for the thing with P
    try using sum(decode (x,'p',1,0))
    x- beging some kind of substr or left or something on the field u want to check like substr(status,1,1).....
    and then for each time there is a P add 1 else 0 and then it will sum the 1's.

    goodluck

Posting Permissions

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