Re: 2 version comparison howto ?

j wrote:

> HI all ,
>
> I have a table,
> T_PRODUCT_BACKUP {
> BackupDate Date,
> ProductId char(8),
> Status char(1)
> }
> which backup all the data from
> T_PRODUCT {
> ProductId char(8),
> Status char(1)
> }
> every week. Now I want make a comparison between product status of 2 user
> specified Date (say, version1_date and version2_date) of backup into a table
> T_PRODUCT_CMP {
> ProductId char(8),
> Status_1 char(1),
> Status_2 char(2)
> }
> And I have to insert the productId into T_PRODUCT_CMP also if it doesn't
> exist in one of the version. So what's the sql that you would suggest ? Or I
> have to do it in stored proc ?if yes, What's the approach to do that in
> stored proc ? Thanks.
>
> Perseus

First approach would be to dump those disgusting CHAR fields for VARCHAR2. I'll bet a Starbuck's latte' you are a student. Didn't your instructor explain why you should not use CHARs because of the problems with string comparisons?

Anyway ... the approach is to use a subselect on an insert statement. In its simplest form:

INSERT INTO table
FROM whatever
WHERE something NOT IN (

   SELECT ...
   FROM ...
   WHERE ...);

Visit http://tahiti.oracle.com for more.

Daniel Morgan Received on Sat Feb 22 2003 - 13:38:13 CST