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

    Unanswered: inserting from 2 tables

    hie all,

    im trying to write this script but i cant..

    i have a table called a and also b, ill have to compare and enter into th report table

    for ex,
    i have table a
    reload_date, loyalty_id, reload_value
    25/11/2005,1,15
    26/11/2005,2,30
    25/11/2005,2,15
    25/11/2005,3,20

    i have table b
    reload_date, loyalty_id, reload_value
    25/11/2005,1,20
    25/11/2005,2,15
    25/11/2005,3,20

    the reporting table should look:
    reload_date,loyalty_id, reload_value_table_a, reload_value_table_b, gap, gap_percentage
    25/11/2005,1,15,20,5,0.0014
    25/11/2005,2,15,15,0,0
    25/11/2005,3,20,20,0,0
    26/11/2005,2,30,0,30,100


    pls help since im only experienced in one table but when it comes to two tables, im having multiple problems...
    thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    according to your data and result expectation this returns exactly what you posted:
    Code:
    select 
    a.reload_date, 
    a.loyalty_id, 
    a.reload_value as reload_value_table_a,
    case when b.reload_date = a.reload_date 
    	then b.reload_value 
    	else 0 
    end as reload_value_table_b,
    abs(a.reload_value - case when b.reload_date = a.reload_date 
    			then b.reload_value 
    			else 0 
    		     end) as gap
    from a, b
    where a.loyalty_id = b.loyalty_id
    order by a.reload_value, a.loyalty_id
    the only thing missing is gap_percentage column as I have no idea how you calculated your 0.0014 value

  3. #3
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    hie, this is what im trying to do

    Code:
    Insert into J01_CLT_LOYAL_PROG_TO_BAL_UPD
                (date_loaded, week_no, reload_date, loyalty_id,   
                 total_reloads_value_j2, total_reloads_value_j1, gap, gap_percentage
    	)
                (select current_date, week_no, reload_date, loyalty_id,
                 reloads_value_j2, reloads_value_j1, gap, gap_percentage
    FROM
    ( SELECT (sysdate) FROM dual
    ) AS current_date,
    ( SELECT (sysdate, 'WW') FROM dual
    ) AS week_no,
      a.reload_date, a.loyalty_id, a.reloads_value_j2 as reloads_value_j2,
      case when b.reload_date = a.reload_date 
      then b.reloads_value_j1 
      else 0 
      end as reloads_value_j1,
      abs(a.reloads_value_j2 - case when b.reload_date = a.reload_date 
      then b.reloads_value_j1 
      else 0 
      end) as gap
      ?? as gap_percentage
    from trap_ra_ppb_balance a, trap_ra_clp_balance b
    where a.loyalty_id = b.loyalty_id
    order by a.reloads_value_j2, a.loyalty_id
    );
    the gap is actually the
    gap / (reloads_value_j1+reloads_value_j2) * 100


    can u pls help..
    Last edited by shatishr; 11-25-05 at 00:23.

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    The basic syntax you need is

    Code:
    INSERT INTO tablename
         ( col1
         , col2 )
    SELECT expr1
         , expr2
    FROM   othertable;
    Note there are no brackets around the query. Also if expr1 (for example) needs to be SYSDATE, just use

    Code:
    INSERT INTO tablename
         ( col1
         , col2 )
    SELECT SYSDATE
         , expr2
    FROM   othertable;

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    try this:
    Code:
    Insert into J01_CLT_LOYAL_PROG_TO_BAL_UPD
    	(
    	date_loaded, week_no, 
    	reload_date, loyalty_id,   
    	total_reloads_value_j2, 
    	total_reloads_value_j1, 
    	gap, 
    	gap_percentage
    	)
    select 
    	sysdate 		AS current_date,
    	to_char(sysdate, 'WW') 	AS week_no,
    	a.reload_date 		AS reload_date, 
    	a.loyalty_id 		AS loyalty_id,
    	a.reloads_value_j2 	AS reloads_value_j2, 
      	case when b.reload_date = a.reload_date then 
                 b.reloads_value_j1 
      	else 
                 0 
      	end AS reloads_value_j1,
      	abs(a.reloads_value_j2 - case when b.reload_date = a.reload_date then 
    					b.reloads_value_j1 
    				 else 
    					0 
      				 end
            ) as gap,
      	abs(a.reloads_value_j2 - case when b.reload_date = a.reload_date then 
    					b.reloads_value_j1 
    				 else 
    					0 
      				 end
            )/(a.reloads_value_j2 + case when b.reload_date = a.reload_date then 
                 				b.reloads_value_j1 
      				else 
                 				0 
      				end)*100 AS gap_percentage
    from trap_ra_ppb_balance a, 
         trap_ra_clp_balance b
    where a.loyalty_id = b.loyalty_id
    order by a.reloads_value_j2, a.loyalty_id;

  6. #6
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    okay by the advise of william, i tried inserting successfully but the data is not correct

    ive added an attachment of the result
    this is how the code looks like

    Code:
    insert into J01_CLT_LOYAL_PROG_TO_BAL_UPD
    (reload_date, loyalty_id, total_reloads_value_j2, total_reloads_value_j1, gap)
    select
      trunc(a.reload_date), a.loyalty_id, a.reloads_value_j2 as   
      total_reloads_value_j2,
      case when trunc(b.reload_date) = trunc(a.reload_date) 
      then b.reloads_value_j1 
      else 0 
      end as total_reloads_value_j1,
      abs(a.reloads_value_j2 - case when trunc(b.reload_date) = trunc(a.reload_date) 
      then b.reloads_value_j1 
      else 0 
      end) as gap
    from trap_ra_ppb_balance a, trap_ra_clp_balance b
    where a.loyalty_id = b.loyalty_id
    and to_char(a.reload_date, 'mm/dd/yyyy') = '11/20/2005'
    order by a.reloads_value_j2, a.loyalty_id
    Attached Files Attached Files
    Last edited by shatishr; 11-25-05 at 07:22.

  7. #7
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I tested my last posted statement on data provided by you. Now I stored dates in format: 'DD/MM/YY HH:MIS' and I used trunc() function to transform dates to 'DD/MM/YY' as I assume your dates are stored as DATETIME in tables. Here is result:
    Code:
    current_date		reload_date		loy_id	rel_val_j2	reloads_val_j1	gap	gap_percentage
    ------------------------------------------------------------------------------------------------------------------
    2005-11-25 06:49:04	2005-11-25 11:20:03	1	15.0		20.0		5	14.285714285714285
    2005-11-25 06:49:04	2005-11-25 09:24:03	2	15.0		15.0		0	0.0
    2005-11-25 06:49:04	2005-11-25 09:30:34	3	20.0		20.0		0	0.0
    2005-11-25 06:49:04	2005-11-26 11:24:03	2	30.0		0.0		30	100.0
    It works perfect for me. That's all I can do for you.

Posting Permissions

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