DSXchange

If I recall, DB2 has a listagg function? If your version does have this function you could get cute and use something like the following to generate your select statement: select concat(concat(concat('select ',column_list),' from '),tbname) from (select listagg(name,',') within group (order by case ...

If you have a dynamic list of keys, then you need to be able to identify those key fields. Using your DBs system tables should allow you to do that. Using the system tables to identify the key values you can then generate an SQL statement which concatenates those values into a single output column a...

And what error do you get with those settings in the DB2 connector stage? Rrrrr, not cool ! :evil: bye bye awesome select * from table + RCP :cry: And the generation of the select statement, whilst a little more complex does not remove the awesomeness of an RCP job. Its just replacing how the select...

Its been a while since I used DB2, but I don't remember having that issue. What error do you actually receive? Is your array size set to 1? Anyway if that doesn't work, and you want to use RCP then you will need to look into generating a select statement prior to executing your job. You could use th...

chulett wrote:Interesting, didn't consider that this might be a lookup. My response was based on your rather sparse post making me think this was the source in your job.

I was basing that assumption on the OP thread title.

It is possible, but the question is how you should accomplish it. You could use a sparse lookup, whereby you pass an individual SQL query per input row. This can be highly inefficient when you have many rows. Generally these are only used when the reference data is far larger than the input data. To...

What is the column data type you are using for the column carrying this data?

If you are not already - try setting it to either NVarChar or Varchar Unicode.

Just one addon to ShaneMuir solution - You need to add 0 in your minutes and seconds if they are single digit, So add zero by checking if len(val)=1 else val Actually that was there in my solution. 2. Use Field() to split the converted value into separate parts (ie mins and seconds). Remember to pa...

So it becomes a simple string manipulation. In a transformer 1. Convert your input decimal to a string value, using DecimalToString() remember to add the option to suppress_zero 2. Use Field() to split the converted value into separate parts (ie mins and seconds). Remember to pad your values to 2 ch...

So it is stored in your DB as a decimal? Does this mean that that the highest number available is 59.59?

If that is the case, just use a string manipulation to separate the parts and output as the required string.

I am sure there is a better way but you could do the following (not sure it would work in all circumstances) Have 3 stage variables: svConvert=Convert('1234567890','', Lnk_input.STRING) svPart1=If svConvert ='' then Lnk_input.STRING else Lnk_input.STRING[svConvert,1,1] svPart2=If svConvert ='' then ...

devsonali wrote:i am using Oracle but the concatenation fails with the same error

What is the exact sql you have in your sparse query?
What is the exact error you are getting?
What is the exact column name you are using in the lookup stage?

Not sure what your reference DB is but what you want to do is write the where clause as some sort of concatenated string eg In oracle the following does work

select field1, field2 from TableName
where Tab_Col like '%'||ORCHESTRATE.LKP_STRING||'%'

Actually now that i think about it, Letter and Digits wouldn't work in all your examples.

For '12/2' it would return '112' for Digits and '' for letters.

So can you manually insert those characters into the DWH? (Just trying to ascertain if the DWH can actually accept those char)

Do you have the column data types set appropriately in your DS job?