DSXchange

hi,

I am using the db2 stage to extract the using the query to fetch the records from column using the logic expression
YEAR(TO_DATE(AUC_SALE_DATE,'yyyy-mm-dd')) || '-' || WEEK(TO_DATE(AUC_SALE_DATE,'yyyy-mm-dd'))

the results from this query coming as
2012-5
2012-44
2011-47
2012-18
2013-32

I want the last two numbers of first field like 2012-05 instead of 2012-5 .

the design of job is


db2 stage................xfm...................seqfile

in tried with following way in transformer

right((0:AUC_SALE_DATE),2) but not getting the right result like 2012-50 can you please help me about this.

hargun wrote:I want the last two numbers of first field like 2012-05 instead of 2012-5.

Doesn't DB2 have a "to char" function?

Try adding quotes around the zero inside your Right() function.

You can't just use 'right' in the job as you want weeks not days. Look into the YearFromDate and YearweekFromDate functions.

DSFIELD1 : YEAR(TO_DATE(AUC_SALE_DATE,'yyyy-mm-dd'))
DSFIELD2 : WEEK(TO_DATE(AUC_SALE_DATE,'yyyy-mm-dd'))

Transformer Stage :

Column Derivation
DSFIELD1 : '-' : Str('0',2-len(DSFIELD2)):DSFIELD2

ssnegi wrote:Column Derivation
DSFIELD1 : '-' : Str('0',2-len(DSFIELD2)):DSFIELD2

Overkill. Since WEEK will never have more than two digits, the original formulation will work. The expression "0" : DSFIELD2 can only ever return two or three characters, since WEEK must be between 1 and 53.

Which version of Db2 do you have? It's been more than a year since I last worked with Db2 but I do remember using either 'CHAR()' or 'TO_CHAR()' in a way very similar to Oracle.

(Edit)
Found this -
http://www-01.ibm.com/support/knowledge ... 007108.htm