All Forums Database
terankit 77 posts Joined 03/12
23 May 2012
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

ulrich 816 posts Joined 09/09
23 May 2012

try

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

terankit 77 posts Joined 03/12
24 May 2012

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.

terankit 77 posts Joined 03/12
24 May 2012

Hi Doneth,

Once again, I am looking for your help.

Thanks.

dnoeth 4628 posts Joined 11/04
24 May 2012

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

Dieter

Dorji 1 post Joined 07/13
22 Jul 2013

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

Dorji

ananthpillai 1 post Joined 06/15
18 Aug 2015
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.
 
 
 
 
 
 
 

You must sign in to leave a comment.