On 8 May, 15:40, "ame...@[EMAIL PROTECTED]
" <ame...@[EMAIL PROTECTED]
> wrote:
> Understood. =A0In the example below, I would want to ignore customer
> 35666699 because he falls out of the date range, 01/01/2005 -
> 12/31/2005. =A0I'd ignore customer 21435557 because his status is D.
=A0I
> would include customer 32547687 because his MAX date has a status of
> A. =A0Likewise, I'd want customer 35666687 since his MAX date has a
> status of A. =A0I would not want customer 22222222 because his MAX date
> has a status o D.
>
> Hopefully I got it right.......
>
> CREATE TABLE EMAIL_PRODUCT_HIST (
> =A0 ACTION_TYPE =A0 =A0 =A0 VARCHAR2(2),
> =A0 STATUS =A0 =A0 =A0 =A0 =A0 =A0VARCHAR2(2),
> =A0 ACTION_DATE =A0 =A0 =A0 DATE,
> =A0 EMAIL_HIST_ID =A0 =A0 NUMBER CONSTRAINT
EMAIL_PRODUCT_HIST_HIST_ID_NN
> NOT NULL,
> =A0 CUSTOMER_ID =A0 =A0 =A0 NUMBER CONSTRAINT
EMAIL_PRODUCT_HIST_CUST_ID_N=
N
> NOT NULL,
> =A0 EMAIL_PRODUCT_ID =A0VARCHAR2(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
quo=
ted 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
|
