Date: Mon, 5 Dec 2011 12:24:05 -0800
Reply-To: "Huang, Ya" <Ya.Huang@AMYLIN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Huang, Ya" <Ya.Huang@AMYLIN.COM>
Subject: Re: Oracle SQL pass through to_date again
In-Reply-To: <AC5F423B9AB19C43BE62C86BAF8113CC1919C4@WABOTH9MSGUSR8C.ITServices.sbc.com>
Content-Type: text/plain; charset="us-ascii"
Thanks,
Now I know it is a character field:
Obs COLUMN_NAME TYPE LENGTH NULLABL
1 CASE_ID VARCHAR2 500 N
8 DATE_COMPLETED VARCHAR2 8 Y
I also found that date_completed could have partial date like '2007', '200710' etc.
So, what the best way to select the records based on such a character column and
a specified time period?
-----Original Message-----
From: DUELL, BOB [mailto:bd9439@att.com]
Sent: Monday, December 05, 2011 12:10 PM
To: Huang, Ya; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Re: Oracle SQL pass through to_date again
Hmmm, it sounds like "date_completed" is not a data column. A quick way to check the table structure is to look at the Oracle metadata. Try running this to see all the columns:
proc sql;
CONNECT TO ORACLE (your login info);
create table temp as
select * from connection to oracle (
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE
from ALL_TAB_COLUMNS
where TABLE_NAME='PC_INVESTIGATIONS'
order by column_id
);
quit;
If you do not have access to ALL_TAB_COLUMNS, ask your friendly neighborhood DBA for help.
Bob
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Huang, Ya
Sent: Monday, December 05, 2011 10:48 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Oracle SQL pass through to_date again
I actually don't know what it is in Oracle. If I just download
the data (libname engine), I can see it in SAS as a string
like '20071231'. So, could it be that it is actually a string
in Oracle?
-----Original Message-----
From: Bolotin Yevgeniy [mailto:YBolotin@schools.nyc.gov]
Sent: Monday, December 05, 2011 10:45 AM
To: Huang, Ya
Subject: RE: Re: Oracle SQL pass through to_date again
b.date_completed is a date variable and not a string, right? you might
have invalid data in it
-----Original Message-----
From: Ya Huang [mailto:ya.huang@AMYLIN.COM]
Sent: Monday, December 05, 2011 1:43 PM
To: SAS-L@LISTSERV.UGA.EDU; Bolotin Yevgeniy
Cc: Ya Huang
Subject: Re: Oracle SQL pass through to_date again
Yes, it works. It also works if I just treat it as
string:
b.date_completed between '20071231' and '2008-01-06'
But the query result seems wrong (with much less records
then I expected)
On Mon, 5 Dec 2011 13:39:36 -0500, Bolotin Yevgeniy
<YBolotin@SCHOOLS.NYC.GOV> wrote:
>Just as a sanity check - can you remove the BETWEEN condition entirely
>(along with the dates) and run the query again?
>
>If you get the same error again, must be something else wrong.
>
>
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>Huang, Ya
>Sent: Monday, December 05, 2011 1:11 PM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Re: Oracle SQL pass through to_date again
>
>Same message:
>
>b.date_completed between date '2007-12-31' and date
>'2008-01-06' )
>78 )
>79 ;
>ERROR: ORACLE execute error: ORA-01861: literal does not match format
>string.
>
>-----Original Message-----
>From: DUELL, BOB [mailto:bd9439@att.com]
>Sent: Monday, December 05, 2011 10:05 AM
>To: Huang, Ya; SAS-L@LISTSERV.UGA.EDU
>Subject: RE: Oracle SQL pass through to_date again
>
>Try changing the != symbol to <>. Also, you can avoid the TO_DATE
>function by using an ANSI date string. In other words:
>
>%macro aepccase;
>(select
> a.case_id,
> a.delete_flag,
> a.prod_cd,
> a.adverse_event_flag,
> a.product_complaint_flag,
> a.case_create_dt,
> b.date_completed
> from &schema..ae_cases a,
> &schema..pc_investigations b
> where a.case_id = b.case_id and
> (upper(a.delete_flag) <> 'Y' or a.delete_flag is null) and
> upper(a.prod_cd) in ('XXX','YYY') and
> upper(a.ADVERSE_EVENT_FLAG)='Y' and
> upper(a.PRODUCT_COMPLAINT_FLAG)='Y' and
> b.date_completed between date '2007-12-31' and
> date '2008-01-06'
>)
>%mend aepccase;
>
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ya
>Huang
>Sent: Monday, December 05, 2011 9:43 AM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Re: Oracle SQL pass through to_date again
>
>Tried and still not working. Besides, for testing, those date are
really
>hardcoded in the marco:
>
>%macro aepccase;
>(select
> a.case_id,
> a.delete_flag,
> a.prod_cd,
> a.adverse_event_flag,
> a.product_complaint_flag,
> a.case_create_dt,
> b.date_completed
> from &schema..ae_cases a,
> &schema..pc_investigations b
> where a.case_id = b.case_id and
> (upper(a.delete_flag) !='Y' or a.delete_flag is null) and
> upper(a.prod_cd) in ('XXX','YYY') and
> upper(a.ADVERSE_EVENT_FLAG)='Y' and
> upper(a.PRODUCT_COMPLAINT_FLAG)='Y' and
> b.date_completed between to_date('20071231', 'YYYYMMDD') and
> to_date('20080106', 'YYYYMMDD')
>)
>%mend aepccase;
>
>So, the quoting for 20071213 is no difference from all the other
>single quote here.
>
>
>On Mon, 5 Dec 2011 11:30:44 -0600, Joe Matise <snoopy369@GMAIL.COM>
>wrote:
>
>>Macro quoting issue maybe? Does it work if you paste in the %aepccase
>>text directly? I checked that your general syntax should work:
>>
>>with b as (select to_date('20080105','YYYYMMDD') as date_completed, 1
>>as id from dual),
>> a as (select 1 as id from dual)
>> select * from a,b where a.id = b.id and b.date_completed between
>>to_date('20071231',
>>'YYYYMMDD') and to_date('20080106', 'YYYYMMDD')
>>
>>returns
>>ID DATE_COMPLETED ID
>>---------------------- -------------------------
----------------------
>>1 05-JAN-08 1
>>
>>-Joe
>>
>>On Mon, Dec 5, 2011 at 11:23 AM, Ya Huang <ya.huang@amylin.com> wrote:
>>> I thought it would work too, but somehow it didn't. There is nothing
>>> leading to this, this is the first step:
>>>
>>> 75 proc sql;
>>> 76 connect to oracle(path=&path user=&userid pass=&pwd
>>> dbmax_text=25000);
>>> 77 create table here.aepccase as
>>> 78 select *
>>> 79 from connection to oracle
>>> 80 (select *
>>> 81 from %aepccase
>>> MPRINT(AEPCCASE): (select a.case_id, a.delete_flag, a.prod_cd,
>>> a.adverse_event_flag, a.product_complaint_flag, a.case_create_dt,
>>> b.date_completed from aers.ae_cases a, aers.pc_investigations b
where
>>> a.case_id = b.case_id and (upper(a.delete_flag) !='Y' or
>>> a.delete_flag is null) and upper(a.prod_cd) in ('XXX','YYY')
>>> and upper(a.ADVERSE_EVENT_FLAG)='Y' and
>upper(a.PRODUCT_COMPLAINT_FLAG)
>>> ='Y' and b.date_completed between to_date('20071231',
>>> 'YYYYMMDD') and to_date('20080106', 'YYYYMMDD') )
>>> 82 )
>>> 83 ;
>>> ERROR: ORACLE execute error: ORA-01861: literal does not match
format
>>> string.
>>> NOTE: Table HERE.AEPCCASE created, with 0 rows and 7 columns.
>>>
>>>
>>> Any idea?
>>>
>>>
>>> On Mon, 5 Dec 2011 11:12:51 -0600, Joe Matise <snoopy369@GMAIL.COM>
>wrote:
>>>
>>>>Looks fine to me; see
>>>>http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html for
>>>>example. Works fine for me also (Oracle 10g):
>>>>
>>>>select to_date('20071231', 'YYYYMMDD') from dual
>>>>
>>>>returns
>>>>
>>>>TO_DATE('20071231','YYYYMMDD')
>>>>-------------------------
>>>>31-DEC-07
>>>>
>>>>Maybe you have something else wrong [missing semicolon type problem]
>>>>that is leading to this error?
>>>>
>>>>-Joe
>>>>
>>>>On Mon, Dec 5, 2011 at 11:02 AM, Ya Huang <ya.huang@amylin.com>
>wrote:
>>>>> Hi there,
>>>>>
>>>>> What should I use to marsk the date format:
>>>>>
>>>>> ORA-01861: literal does not match format string was caused by this
>line
>>>>>
>>>>> to_date('20071231', 'YYYYMMDD')
>>>>>
>>>>> I tried YYYY/MM/DD and failed too.
>>>>>
>>>>> Thanks
>>>>>
>>>>> Ya
| 
