selecting column as null from oracle

[ArunaDas_Maharana]

hi,

I want to select some of my columns as null from the database oracle like

select t1.fd_id,
t2.px_number,
null cr_code,
null pt_code
from table1 t1, table2 t2


this query works fine in sqlplus and sqldeveloper where as in datastage
it giving following error
db_Tx_data: Error when checking operator: Caught parsing exception during wrapDescribeOperator(): In field "col": Parsing parameters "max=0" for schema type "string": Max length must be positive, got: "0"

i am using datatype varchar and nullability yes for such columns.

Please provide some pointers!

Thanks,
Aruna

[cmunikoti]

Try '' instead of null
or else
use transformer stage and create those two colums and assign them with setnull()

Regards,
Chandrakiran

[Sainath.Srinivasan]

Just a guess.....

What happens if you try

Code: Select all

select t1.fd_id, 
t2.px_number, 
CAST(null AS VarChar2(10)) cr_code, 
CAST(null  AS VarChar2(10)) pt_code 
from table1 t1, table2 t2 

in your source ?

[chulett]

How exactly did you define those two fields in the job? We got the "varchar, nullable" part but it looks like you may not have specified a size for them.

[ArunaDas_Maharana]

sorry to miss the length it's 10.

[ArunaDas_Maharana]

i will go with the suggestion provided CAST(null AS VarChar2(10)) cr_code and with below theory not sure though that it's fully correct!

oracle nulls can be identified where as for datastage null should be converted to a form or metadata datastage is aware off hence it worked once null is explicitly casted to a data type!

Thanks to all!
Aruna

[ray.wurlod]

The error message has nothing to do with null - it has to do with your metadata, in which you have specified VarChar(0). This is not permissible.

[ArunaDas_Maharana]

i had never specified varchar(0), it was always varchar(10), i said i was sorry i missed to share this info in this forum. In datastage it was always there Ray

[ray.wurlod]

The error message suggests otherwise. Maybe you have a VarChar column in which the precision (length) field has not been filled in at all, somewhere in the job.

[ArunaDas_Maharana]

I have 15 columns all defined with datatype & length.

You can produce this error by selecting null as column, although datatype(length) is defined.

Thanks,
Aruna