logic required

[hargun]

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.

[chulett]

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

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

[qt_ky]

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

[chulett]

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

[ssnegi]

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

[ray.wurlod]

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

Overkill. Since WEEK will never have more than two digits, the original formulation will work.

Code: Select all

DSFIELD1 : "-" : Right("0" : DSFIELD2, 2)

The expression "0" : DSFIELD2 can only ever return two or three characters, since WEEK must be between 1 and 53.

[jerome_rajan]

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