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

    Unanswered: multiply 2 columns

    INSERT INTO TRAP_RA_VCA_SC_W (VENDOR_ID, BATCH_NO, DATE_GENERATED, DATE_VALID_TO, STATUS, DATE_STATUS_CHANGE, DENOMINATION, AMOUNT, QUANTITY, NO_OF_TRANSACTIONS)

    (SELECT DISTINCT PROD_ID, BATCHNR, DATE_GENERATED, DATE_VALID_TO, STATUS, DATE_LSTATE, VALUE,
    (SELECT count(*) FROM TRAP_STAGE_VCA_SC_W a WHERE STATUS = '5' and a.prod_id=b.prod_id and a.batchnr=b.batchnr) , QUANTITY, (SELECT count(SERIAL_NUMBER) FROM TRAP_STAGE_VCA_SC_W a where a.prod_id=b.prod_id and a.batchnr=b.batchnr) FROM TRAP_STAGE_VCA_SC_W b)

    ------------------

    i have the above script and i need to (multiply the bolded with the column value) but i cant do so..
    (SELECT value * count(*) FROM TRAP_STAGE_VCA_SC_W a WHERE STATUS = '5' and a.prod_id=b.prod_id and a.batchnr=b.batchnr)

    its should look something like this
    anyone could help

    thanks !

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try nesting the query:

    Code:
    SELECT prod_id, batchnr, date_generated, date_valid_to, status, date_lstate, value, cnt1, cnt1*value AS result,
           quantity, cnt2
    FROM
    ( SELECT DISTINCT prod_id, batchnr, date_generated, date_valid_to, status, date_lstate, value,
             ( SELECT COUNT(*)
               FROM   trap_stage_vca_sc_w a
               WHERE  status = '5'
               AND    a.prod_id=b.prod_id 
               AND    a.batchnr=b.batchnr
             ) AS cnt1, 
             quantity, 
             ( SELECT COUNT(serial_number) 
               FROM   trap_stage_vca_sc_w a 
               WHERE  a.prod_id=b.prod_id 
               AND    a.batchnr=b.batchnr
             ) AS cnt2
      FROM   trap_stage_vca_sc_w b
    );
    (Also try being consistent with use of upper/lower case and layout to make your SQL 10 times easier to read!)

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

    inserting from multiple tables

    thx for the explanation and the re-formatting..
    it was very very useful..

    another question i have is...
    i have 3 tables which holds certain data and i need to populate it into one reporting table

    what i dont understand is, lets say the reporting table has a column called batch_no and the other three tables also has batch no, how am i going to insert into the reporting table.. and how to get the week number, would appreciate if somebody can start it off for me...

    Reporting_table column:
    batch no, week_no (**based on the date ordering), denomination,expiry_date,gap(**The difference (in percent) between no_of_transaction within Table P and W

    the tables contains:
    TableP
    vendor_id, batch_no, date_ordering, date_valid_to, denomination, amount, no_of_transaction, date_received
    001,005,29/9/2005,8/1/2006,10,1000,15,31/09/2005


    TableW
    vendor_id, batch_no, date_ordering, date_valid_to, denomination, amount, no_of_transaction, date_received
    001,005,29/9/2005,8/1/2006,10,1000,5,31/09/2005
    002,007,29/9/2005,9/1/2006,20,2000,20,31/09/2005



    so the reporting table result should look
    005,39,10,8/1/2006,0.1
    002,..........................


    INSERT INTO reporting
    (batch_no,week_no,denomination,expiry_date,gap)
    (SELECT DISTINCT (batch_no??from 3 tables)
    FROM
    ( ?? (3tables ??)

Posting Permissions

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