NVL(column,0) will convert a null to 0 in Oracle, but you'd have to use pass-through SQL for that.

-Mary

--- Ya.Huang@AMYLIN.COM wrote:

From: "Huang, Ya" <Ya.Huang@AMYLIN.COM> To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Somewhat OT: Length=0 Oracle column cause trouble Date: Mon, 20 Jun 2011 10:42:15 -0700

Thanks Bob,

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?

Ya

-----Original Message----- From: DUELL, BOB (ATTCINW) [mailto:bd9439@att.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).

Good luck!

Bob

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ya Huang Sent: Monday, June 20, 2011 8:29 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Somewhat OT: Length=0 Oracle column cause trouble

Hi there,

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; run;

data aersdly.DE_SUSPECT_DRUGS ; set myorac.DE_SUSPECT_DRUGS (obs=10); run;

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?"

Thanks

Ya