How to move spaces when date is null in table while Fast export

Database
Enthusiast

How to move spaces when date is null in table while Fast export

Hi All,

In Fast Export I am using:

CAST(B.EFF_DT AS DATE       format 'YYMMDD')

When this column is NULL in table,it is giving some junk value. How can I modify the fast export so that whenever it is Null, it move Spaces to output file otherwise Date in YYYYMMDD format.

Thanks,

Terankit

6 REPLIES
Senior Supporter

Re: How to move spaces when date is null in table while Fast export

try

coalesce(cast(CAST(B.EFF_DT AS DATE       format 'YYMMDD') as char(8)),'        ')

Enthusiast

Re: How to move spaces when date is null in table while Fast export

Hi Ulrich,

This is giving some junk in output which is behaving as end of line (i suppose) because the next script is reading the recod till that point only. Please let me know how to do that. Thanks again.

Enthusiast

Re: How to move spaces when date is null in table while Fast export

Hi Doneth,

Once again, I am looking for your help.

Thanks.

Junior Contributor

Re: How to move spaces when date is null in table while Fast export

The "junk" is probably a 2-byte varchar length, you need to cast the result of the coalesce as a CHAR:

coalesce((B.eff_dt (format 'yyyymmdd') (char(8))), '') (char(8))

Dieter

N/A

Re: How to move spaces when date is null in table while Fast export

Would anyone help with the following : The date is not null. If the date is '2011-01-31', move spaces. Greatly appreciated.

Re: How to move spaces when date is null in table while Fast export

case when(cast((X1_PRODUCT_EFF_DT(format 'YYYY-MM-DD')) as char(10)) is null) then '' 
              else
              cast((X1_PRODUCT_EFF_DT(format 'YYYY-MM-DD')) as char(10)) end

That worked for me.