Date:         Mon, 19 Jul 2004 12:07:47 -0700
Reply-To:     David Fickbohm <DavidF@HOMEGAIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         David Fickbohm <DavidF@HOMEGAIN.COM>
Subject:      Trying to compare todays date to the contents of a date field in
              an oracle database.
Content-Type: text/plain; charset="iso-8859-1"

People,
I am working in SAS v8.1, in a windows environment. Oracle 8i.

I am trying to determine whether the contents of a field called
d_membership_expire_ts (Timestamp) is less than the current date. Currently
I get a message saying I have an invaled relational operator.

Any and All help will be greately appreciated.

Thanks
Dave


PROC SQL;
connect to oracle(user='megan' pass='mzdm2xt' path='reporting2');
create table agents as select
D_UID,
D_NAME,
AGENT_FIRST,
AGENT_LAST,
AGT_COMPANY,
REG_DT,
D_PAID_DT,
D_MEMBERSHIP_EXPIRE_TS,
D_FIRST_PAID_TS,
MEMB_TYPE from connection to oracle(select
A1."D_AGENT_UID",
A2."D_NAME",
A2."D_FIRST_NAME",
A2."D_LAST_NAME",
A2."D_ORGANIZATION",
A2."D_EMAIL",
ts_to_date(A2."D_CREATE_TS"),
ts_to_date(A3."D_PAID_TS"),
ts_to_date(A3."D_MEMBERSHIP_EXPIRE_TS"),
ts_to_date(A3."D_FIRST_PAID_TS")
FROM
USER_OWN.T_AGENT A1,
USER_OWN.T_USER A2,
USER_OWN.T_MEMBERSHIP A3
where
A1."D_AGENT_UID" = A2."D_UID"
and
A1."D_AGENT_UID" = A3."D_UID"
and
A3."D_PRODUCT_RELATIONSHIP" = 1
and
A3."D_PAID_TS" > 0
AND
A3."D_PAID" = 1
AND
(to_date(to_char(ts_to_date(A3."D_MEMBERSHIP_EXPIRE_TS")))) <
trunc(sysdate())
)
as t1(D_UID,
D_NAME,
AGENT_FIRST,
AGENT_LAST,
AGT_COMPANY,
EMAIL,
REG_DT,
D_PAID_DT,
D_MEMBERSHIP_EXPIRE_TS, D_FIRST_PAID_TS, MEMB_TYPE);
quit;

Dave Fickbohm
Data Mining Analyst
Homegain+
2450 45th St.
Emeryville, CA, 94608
Phone 510 655 0800 ext 4151