Re: query

On 8 May, 15:40, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> Understood.  In the example below, I would want to ignore customer
> 35666699 because he falls out of the date range, 01/01/2005 -
> 12/31/2005.  I'd ignore customer 21435557 because his status is D.  I
> would include customer 32547687 because his MAX date has a status of
> A.  Likewise, I'd want customer 35666687 since his MAX date has a
> status of A.  I would not want customer 22222222 because his MAX date
> has a status o D.
>
> Hopefully I got it right.......
>
> CREATE TABLE EMAIL_PRODUCT_HIST (
>   ACTION_TYPE       VARCHAR2(2),
>   STATUS            VARCHAR2(2),
>   ACTION_DATE       DATE,
>   EMAIL_HIST_ID     NUMBER CONSTRAINT EMAIL_PRODUCT_HIST_HIST_ID_NN
> NOT NULL,
>   CUSTOMER_ID       NUMBER CONSTRAINT EMAIL_PRODUCT_HIST_CUST_ID_NN
> NOT NULL,
>   EMAIL_PRODUCT_ID  VARCHAR2(5));
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('09032005','MMDDYYYY'),32547687,'PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','D',TO_DATE('08092005','MMDDYYYY'),32547687,'PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('03042005','MMDDYYYY'),32547687,'PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','D',TO_DATE('03042005','MMDDYYYY'),21435557,'PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('10092005','MMDDYYYY'),35666687,'PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('12092005','MMDDYYYY'),35666687,'PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','D',TO_DATE('12152005','MMDDYYYY'),68888687,'PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('12202006','MMDDYYYY'),35666699,'PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('03152005','MMDDYYYY'),22222222,'PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','D',TO_DATE('05202005','MMDDYYYY'),222222222,'PPM');- Hide quoted text -
>
> - Show quoted text -

Almost ;)

CREATE TABLE EMAIL_PRODUCT_HIST (

  2    ACTION_TYPE       VARCHAR2(2),
  3    STATUS            VARCHAR2(2),
  4    ACTION_DATE       DATE,
  5    EMAIL_HIST_ID     NUMBER CONSTRAINT

INSERT INTO EMAIL_PRODUCT_HIST
  2 VALUES ('H','A',TO_DATE('09032005','MMDDYYYY'),32547687,'PPM'); INSERT INTO EMAIL_PRODUCT_HIST
            *
ERROR at line 1:
ORA-00947: not enough values

-g Received on Thu May 08 2008 - 10:06:11 CDT