i am trying to write sql over ride , if suppose i have a,b,c,d,e columns in source table i need to select a, cast(substr(d,1,10) AS DATE FORMAT 'MM-YYY-DD'), 'hello' from source table
my question is about text 'hello' , after writing sql over ride how to extract that text field from Source Qualifier since that is not there , shall i just cretae one port in source qualifier ( not in source analyzer) and then pull and mapp into target? does it works or if i just pull 3rd field (c) from source then does it take 'hello' and mapp into into target field?
similarly for d field i am doing substring and then cast in sql override but i am trying to bring d port position to 2 nd position in source qualifier( not in source analyzer) and then taking d column from 2 nd position and mapping to target. does this works
please let me know , thanks
Yes you can write sql override. For hello text filed you have to create one attribute in source qualifier, but make sure you have string data type attribute in Source else mapping will be invalid.
Source Qualifier should have as many outputs as your select statement fields in sql override.,
For giving "hello" text you can also use a expression t/f in between and create a output port thr which has an expression field filled with tht "hello" text
Whatever you have mentioned in the question you can try and check if its work or not.
But the purpose of SQ is to convert the source type datatype to the data type that informatica recognize.
At the same time you can use the additional functionality in SQ like filter, join, order by etc depends on the source type i.e. oracle.
All your stated requirement can easily met be using a expression transformation and that will reduce the complexity of the code, please do let me know if there is any specific need of using these requirement in SQ only?
Amit Kumar Sinha