Hardcoded column datatype changing in dynamic input tool

[Krish]

I have a dynamic input tool ,in which I connect to db and get some details .But I need to hardcode  some value from other source.

Something like :

Select 'ID' as ID,<Other Columns from db>

from databasetable where <Condition>

Problem is the 'ID' part which I modify using 'Replace specific string' gets different data type.

Basically replacement value from the source is double but after this dynamic input it gets changed to string .sometime it gets converted to Int.

For now I used select tool to convert to uniform format .But not sure why datatype getting changed.

Please advise How to solve this.

Thanks,

Krish

[SeanAdams · Accepted solution]

Hey Krish, 

There's a simple work-around - which is to use an Append after your dynamic input to add on this hard-coded value.    This has the additional benefits of being much simpler to execute and maintain, and far more efficient in terms of data - if you're bringing back 10 million rows, if you bring back the ID using a dynamic query then you have to transport this ID 10 million times across the network; but if you just append it after the query you don't have this overyead.   So appending after the query is simpler and more efficient, and happy to mock this up with/for you.  

But I am curious about the particular case you raised and very happy to help unpick this with you  - I have a SQL server at home and can mock this up.

Are you able to provide 2 things, and then we can whip through this?

- A simple sample table definition or two that we can use for testing (just a create table SQL).   Don't provide the whole table you're battling with at work, just the smallest subset of mocked up columns that still generate the error

- The workflow that is creating the problem.   Again, if you can strip this back to just the 2 or 3 components that make up the problem, and stub out the rest using Text Inputs, then we can get you to the issue.

Cheers Krish

Sean

[SeanAdams]

Hey @Krish,

Did this help to get you to a solution, or is it worth us continuing to iterate?    If this solved your problem, would you mind marking this as solved?

Let me know - if we need to work this further, if you can provide a little extra data we can probably help to get this through?

[Krish]

Apologies for the delay in response.

I was getting type change issue when i connected to sql server.I could not reproduce dummy scenario reproducing issue.