blanks to null
A poster wanted blank char values converted to NULL inside an INSERT ... SELECT, and DECODE did not work. Wolfgang Zager suggested a stored procedure returning NULL for blank input. The poster felt procedures were less efficient than built-in functions. Andrew Hamm called that concern naive and asked whether it had been measured. No built-in solution given.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I need to move data from one table to other. basically I am doing
insert into new_table
select fld1, fld2, ....
from old_table
where ....
now one of the char columns in old_table has many blank data. I want to insert
it to the new_table as null when blank. How can I accomplish this in the
select statement itself. I tried to use DECODE function but is not working.
any pointers.
TIA.
Just create a stored procedure, which returns NULL, if the orginal value
contains only blanks.
triggerfish2001@hotmail.com schrieb:
>
> I need to move data from one table to other. basically I am doing
> insert into new_table
> select fld1, fld2, ....
> from old_table
> where ....>
> now one of the char columns in old_table has many blank data. I want to insert
> it to the new_table as null when blank. How can I accomplish this in the
> select statement itself. I tried to use DECODE function but is not working.
>
> any pointers.
>
> TIA.
--
*******************************************************************************
* Any opinions written in this e-mail are my own. They may not
correspond to *
* those of my
company. *
*******************************************************************************
* Wolfgang Zager Berghauser Str.
104 *
* BAB Data-Systems GmbH D-42349
Wuppertal *
* Abt. Programmierung Tel. : +49 202
479870 *
* Fax : +49 202
470035 *
* E-Mail:
zager@gmx.de *
*******************************************************************************
Wolfgang says... >Just create a stored procedure, which returns NULL, if the orginal value >contains only blanks. yup I did the same already. However I think stored procedures are not as efficient as using informix functions like DECODE,NVL etc.
triggerfish2001@hotmail.com wrote in message <90r19r017ov@drn.newsguy.com>... >Wolfgang says... > >>Just create a stored procedure, which returns NULL, if the orginal value >>contains only blanks. > >yup I did the same already. However I think stored procedures are not as >efficient as using informix functions like DECODE,NVL etc. Now you are treading on thin ice... I think you may be about to provoke an argument on this newsgroup. For me, such a complaint about "efficiency" is completely pointless and naive. Do you want to get the job done, or do you want to be efficient? Have you actually measured the performance of the load with and without the stored procedure call? Compared it to having a separate update run?