On May 8, 10:06 am, gazzag <gar...@[EMAIL PROTECTED]
> wrote:
> On 8 May, 15:40, "ame...@[EMAIL PROTECTED]
" <ame...@[EMAIL PROTECTED]
> 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
> EMAIL_PRODUCT_HIST_HIST_ID_NN
> 6 NOT NULL,
> 7 CUSTOMER_ID NUMBER CONSTRAINT
> EMAIL_PRODUCT_HIST_CUST_ID_NN
> 8 NOT NULL,
> 9 EMAIL_PRODUCT_ID VARCHAR2(5));
>
> Table created.
>
> 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
Crap, forgot that column EMAIL_HIST_ID . You can put anything there,
put 0 there.......
|
