|Date: ||Mon, 20 Jun 2011 13:29:49 -0700|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||Mary <mlhoward@AVALON.NET>|
|Subject: ||Re: Somewhat OT: Length=0 Oracle column cause trouble|
|Content-Type: ||text/plain; charset="UTF-8"|
NVL(column,0) will convert a null to 0 in Oracle, but you'd have to use pass-through SQL for that.
--- Ya.Huang@AMYLIN.COM wrote:
From: "Huang, Ya" <Ya.Huang@AMYLIN.COM>
Subject: Re: Somewhat OT: Length=0 Oracle column cause trouble
Date: Mon, 20 Jun 2011 10:42:15 -0700
You are right, I was told those Oracle "tables" are indeed views.
You said, "I suspect that GENERIC_NAME is NULL", Do you mean the underline Oracle table has
a column called "GENERIC_NAME" and all the values are NULL?
From: DUELL, BOB (ATTCINW) [mailto:email@example.com]
Sent: Monday, June 20, 2011 10:27 AM
To: Huang, Ya; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Somewhat OT: Length=0 Oracle column cause trouble
It's just a guess, but I bet myorac.DE_SUSPECT_DRUGS is a "view" not a "table". I didn't think it was possible to define a column with a zero length.
If it is a view, I suspect that GENERIC_NAME is NULL. In that case, I'd think your DBA should provide an explicit data type. If it's NOT a view, I don't have a clue. At least the SAS site gives you some solutions (to generalize, use a SAS SQL step to alter the column length to one if it's zero).
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ya Huang
Sent: Monday, June 20, 2011 8:29 AM
Subject: Somewhat OT: Length=0 Oracle column cause trouble
I tried to download some data from Oracle, and got the message
"ERROR: Invalid sequence of commands for file
MYORAC.DE_SUSPECT_DRUGS.DATA.". After some research (support.sas.com),
I found that it is most likely casued by some of the length=0 columns
in Oracle. So I did a proc contents against the Oracle table and
indeed it shows some columns have length=0.
myorac is a libname with Oracle lib engine.
proc contents data= myorac.DE_SUSPECT_DRUGS;
data aersdly.DE_SUSPECT_DRUGS ;
set myorac.DE_SUSPECT_DRUGS (obs=10);
This is from proc contents:
6 GENERIC_NAME Char 0 $. $. GENERIC_NAME
Now my question is "what does a length=0 column in Oracle do? Is there a way
to avoid this when mass download data from Oracle, without knowing what
columns are zero length column?"