Unconnected lookup input value not working
In Informatica, I am trying to get the date after certain working days (say 10,20,30) based on another conditions ( say prio 1,2,3). Already I have one DIM_DATE table where holidays and working days are configured. There is no relation with priority table and DIM_DATE table.Here I am using one unconnected lookup with doing the query override. Below the query I used:
select day_date as DAY_DATE
--,rank1
--,PRIORITY_name
from (
select day_date as DAY_DATE,DENSE_RANK() OVER (ORDER BY day_date) as RANK1,PRIORITY_name as PRIORITY_NAME from (
select date_id,day_date from dim_date where day_date between to_date('10.15.2018','MM.DD.YYYY') and to_date('10.15.2018','MM.DD.YYYY') +interval '250' DAY(3) and working_day=1
)
,DIM_PRIORITY
where DIM_PRIORITY.PRIORITY_name='3'
) where rank1=10
order by RANK1 --
In this example I have hardcoded the day_date,priority_name,rank1. But I need to pass all of them as input coming from mapping.
This hardcode is working but while taking as input like ?created? then it is not working. Here created is the date which will come from mapping flow.
Could you please suggest if this is feasible which I am trying?
?created?is giving error missing right paranthesis but the hardcoded query is running fine in sql.
informatica
edited Nov 12 '18 at 11:49
Corion
3,0661922
asked Nov 12 '18 at 9:42
Abhijit GuptaAbhijit Gupta
62
add a comment |
In Informatica, I am trying to get the date after certain working days (say 10,20,30) based on another conditions ( say prio 1,2,3). Already I have one DIM_DATE table where holidays and working days are configured. There is no relation with priority table and DIM_DATE table.Here I am using one unconnected lookup with doing the query override. Below the query I used:
select day_date as DAY_DATE
--,rank1
--,PRIORITY_name
from (
select day_date as DAY_DATE,DENSE_RANK() OVER (ORDER BY day_date) as RANK1,PRIORITY_name as PRIORITY_NAME from (
select date_id,day_date from dim_date where day_date between to_date('10.15.2018','MM.DD.YYYY') and to_date('10.15.2018','MM.DD.YYYY') +interval '250' DAY(3) and working_day=1
)
,DIM_PRIORITY
where DIM_PRIORITY.PRIORITY_name='3'
) where rank1=10
order by RANK1 --
In this example I have hardcoded the day_date,priority_name,rank1. But I need to pass all of them as input coming from mapping.
This hardcode is working but while taking as input like ?created? then it is not working. Here created is the date which will come from mapping flow.
Could you please suggest if this is feasible which I am trying?
?created?is giving error missing right paranthesis but the hardcoded query is running fine in sql.
informatica
edited Nov 12 '18 at 11:49
Corion
3,0661922
asked Nov 12 '18 at 9:42
Abhijit GuptaAbhijit Gupta
62
Formatting, highlighting of relevant parts
– Corion
Nov 12 '18 at 11:49
Any suggestion plz
– Abhijit Gupta
Nov 13 '18 at 3:21
add a comment |
In Informatica, I am trying to get the date after certain working days (say 10,20,30) based on another conditions ( say prio 1,2,3). Already I have one DIM_DATE table where holidays and working days are configured. There is no relation with priority table and DIM_DATE table.Here I am using one unconnected lookup with doing the query override. Below the query I used:
select day_date as DAY_DATE
--,rank1
--,PRIORITY_name
from (
select day_date as DAY_DATE,DENSE_RANK() OVER (ORDER BY day_date) as RANK1,PRIORITY_name as PRIORITY_NAME from (
select date_id,day_date from dim_date where day_date between to_date('10.15.2018','MM.DD.YYYY') and to_date('10.15.2018','MM.DD.YYYY') +interval '250' DAY(3) and working_day=1
)
,DIM_PRIORITY
where DIM_PRIORITY.PRIORITY_name='3'
) where rank1=10
order by RANK1 --
In this example I have hardcoded the day_date,priority_name,rank1. But I need to pass all of them as input coming from mapping.
This hardcode is working but while taking as input like ?created? then it is not working. Here created is the date which will come from mapping flow.
Could you please suggest if this is feasible which I am trying?
?created?is giving error missing right paranthesis but the hardcoded query is running fine in sql.
informatica
edited Nov 12 '18 at 11:49
Corion
3,0661922
asked Nov 12 '18 at 9:42
Abhijit GuptaAbhijit Gupta
62
In Informatica, I am trying to get the date after certain working days (say 10,20,30) based on another conditions ( say prio 1,2,3). Already I have one DIM_DATE table where holidays and working days are configured. There is no relation with priority table and DIM_DATE table.Here I am using one unconnected lookup with doing the query override. Below the query I used:
select day_date as DAY_DATE
--,rank1
--,PRIORITY_name
from (
select day_date as DAY_DATE,DENSE_RANK() OVER (ORDER BY day_date) as RANK1,PRIORITY_name as PRIORITY_NAME from (
select date_id,day_date from dim_date where day_date between to_date('10.15.2018','MM.DD.YYYY') and to_date('10.15.2018','MM.DD.YYYY') +interval '250' DAY(3) and working_day=1
)
,DIM_PRIORITY
where DIM_PRIORITY.PRIORITY_name='3'
) where rank1=10
order by RANK1 --
In this example I have hardcoded the day_date,priority_name,rank1. But I need to pass all of them as input coming from mapping.
This hardcode is working but while taking as input like ?created? then it is not working. Here created is the date which will come from mapping flow.
Could you please suggest if this is feasible which I am trying?
?created?is giving error missing right paranthesis but the hardcoded query is running fine in sql.
informatica
informatica
edited Nov 12 '18 at 11:49
Corion
3,0661922
asked Nov 12 '18 at 9:42
Abhijit GuptaAbhijit Gupta
62
edited Nov 12 '18 at 11:49
Corion
3,0661922
asked Nov 12 '18 at 9:42
Abhijit GuptaAbhijit Gupta
62
edited Nov 12 '18 at 11:49
Corion
3,0661922
edited Nov 12 '18 at 11:49
Corion
3,0661922
edited Nov 12 '18 at 11:49
Corion
3,0661922
3,0661922
asked Nov 12 '18 at 9:42
Abhijit GuptaAbhijit Gupta
62
asked Nov 12 '18 at 9:42
Abhijit GuptaAbhijit Gupta
62
asked Nov 12 '18 at 9:42
Abhijit GuptaAbhijit Gupta
62
62
Formatting, highlighting of relevant parts
– Corion
Nov 12 '18 at 11:49
Any suggestion plz
– Abhijit Gupta
Nov 13 '18 at 3:21
add a comment |
Formatting, highlighting of relevant parts
– Corion
Nov 12 '18 at 11:49
Any suggestion plz
– Abhijit Gupta
Nov 13 '18 at 3:21
Formatting, highlighting of relevant parts
– Corion
Nov 12 '18 at 11:49
Formatting, highlighting of relevant parts
– Corion
Nov 12 '18 at 11:49
Any suggestion plz
– Abhijit Gupta
Nov 13 '18 at 3:21
Any suggestion plz
– Abhijit Gupta
Nov 13 '18 at 3:21
add a comment |
1 Answer
1
active
oldest
votes
You match your incoming port against one of the return fields of one of the records in the cache via the lookup condition (not by feeding ports into the override itself).
If this is not possible for you for some unexplained reason then you could define 3 mapping variables and set them to be equal to each of the input ports you care about (using setvariable) before feeding the record in to the lookup. Then use the variables in your lookup override
answered Nov 13 '18 at 7:02
Daniel MachetDaniel Machet
552147
Hi Daniel,Thanks for the response. I tried using 2nd option using setvariable for 3 different mapping variable. But as expected mapping variable is not changing though out session. Input ports must change for each row based on the input source data. Using mapping variable it only picks the first row value and then not changing for the 2nd input row.
– Abhijit Gupta
Nov 22 '18 at 9:55
Hmmm fair point, only option I can think of is to remove the override (if that means creating a view in the db thdn so be it) , cache all the possible return values at the start of the sesdion and then use the input ports in your lookup condition. Not surd how many records you'll have to cache for this - could be better than querying the db for each input row
– Daniel Machet
Nov 23 '18 at 1:26
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53259424%2funconnected-lookup-input-value-not-working%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You match your incoming port against one of the return fields of one of the records in the cache via the lookup condition (not by feeding ports into the override itself).
If this is not possible for you for some unexplained reason then you could define 3 mapping variables and set them to be equal to each of the input ports you care about (using setvariable) before feeding the record in to the lookup. Then use the variables in your lookup override
answered Nov 13 '18 at 7:02
Daniel MachetDaniel Machet
552147
Hi Daniel,Thanks for the response. I tried using 2nd option using setvariable for 3 different mapping variable. But as expected mapping variable is not changing though out session. Input ports must change for each row based on the input source data. Using mapping variable it only picks the first row value and then not changing for the 2nd input row.
– Abhijit Gupta
Nov 22 '18 at 9:55
Hmmm fair point, only option I can think of is to remove the override (if that means creating a view in the db thdn so be it) , cache all the possible return values at the start of the sesdion and then use the input ports in your lookup condition. Not surd how many records you'll have to cache for this - could be better than querying the db for each input row
– Daniel Machet
Nov 23 '18 at 1:26
add a comment |
You match your incoming port against one of the return fields of one of the records in the cache via the lookup condition (not by feeding ports into the override itself).
If this is not possible for you for some unexplained reason then you could define 3 mapping variables and set them to be equal to each of the input ports you care about (using setvariable) before feeding the record in to the lookup. Then use the variables in your lookup override
answered Nov 13 '18 at 7:02
Daniel MachetDaniel Machet
552147
Hi Daniel,Thanks for the response. I tried using 2nd option using setvariable for 3 different mapping variable. But as expected mapping variable is not changing though out session. Input ports must change for each row based on the input source data. Using mapping variable it only picks the first row value and then not changing for the 2nd input row.
– Abhijit Gupta
Nov 22 '18 at 9:55
Hmmm fair point, only option I can think of is to remove the override (if that means creating a view in the db thdn so be it) , cache all the possible return values at the start of the sesdion and then use the input ports in your lookup condition. Not surd how many records you'll have to cache for this - could be better than querying the db for each input row
– Daniel Machet
Nov 23 '18 at 1:26
add a comment |
You match your incoming port against one of the return fields of one of the records in the cache via the lookup condition (not by feeding ports into the override itself).
If this is not possible for you for some unexplained reason then you could define 3 mapping variables and set them to be equal to each of the input ports you care about (using setvariable) before feeding the record in to the lookup. Then use the variables in your lookup override
answered Nov 13 '18 at 7:02
Daniel MachetDaniel Machet
552147
You match your incoming port against one of the return fields of one of the records in the cache via the lookup condition (not by feeding ports into the override itself).
If this is not possible for you for some unexplained reason then you could define 3 mapping variables and set them to be equal to each of the input ports you care about (using setvariable) before feeding the record in to the lookup. Then use the variables in your lookup override
answered Nov 13 '18 at 7:02
Daniel MachetDaniel Machet
552147
edited Nov 13 '18 at 7:24
answered Nov 13 '18 at 7:02
Daniel MachetDaniel Machet
552147
answered Nov 13 '18 at 7:02
Daniel MachetDaniel Machet
552147
answered Nov 13 '18 at 7:02
Daniel MachetDaniel Machet
552147
552147
Hi Daniel,Thanks for the response. I tried using 2nd option using setvariable for 3 different mapping variable. But as expected mapping variable is not changing though out session. Input ports must change for each row based on the input source data. Using mapping variable it only picks the first row value and then not changing for the 2nd input row.
– Abhijit Gupta
Nov 22 '18 at 9:55
Hmmm fair point, only option I can think of is to remove the override (if that means creating a view in the db thdn so be it) , cache all the possible return values at the start of the sesdion and then use the input ports in your lookup condition. Not surd how many records you'll have to cache for this - could be better than querying the db for each input row
– Daniel Machet
Nov 23 '18 at 1:26
add a comment |
Hi Daniel,Thanks for the response. I tried using 2nd option using setvariable for 3 different mapping variable. But as expected mapping variable is not changing though out session. Input ports must change for each row based on the input source data. Using mapping variable it only picks the first row value and then not changing for the 2nd input row.
– Abhijit Gupta
Nov 22 '18 at 9:55
Hmmm fair point, only option I can think of is to remove the override (if that means creating a view in the db thdn so be it) , cache all the possible return values at the start of the sesdion and then use the input ports in your lookup condition. Not surd how many records you'll have to cache for this - could be better than querying the db for each input row
– Daniel Machet
Nov 23 '18 at 1:26
Hi Daniel,Thanks for the response. I tried using 2nd option using setvariable for 3 different mapping variable. But as expected mapping variable is not changing though out session. Input ports must change for each row based on the input source data. Using mapping variable it only picks the first row value and then not changing for the 2nd input row.
– Abhijit Gupta
Nov 22 '18 at 9:55
Hi Daniel,Thanks for the response. I tried using 2nd option using setvariable for 3 different mapping variable. But as expected mapping variable is not changing though out session. Input ports must change for each row based on the input source data. Using mapping variable it only picks the first row value and then not changing for the 2nd input row.
– Abhijit Gupta
Nov 22 '18 at 9:55
Hmmm fair point, only option I can think of is to remove the override (if that means creating a view in the db thdn so be it) , cache all the possible return values at the start of the sesdion and then use the input ports in your lookup condition. Not surd how many records you'll have to cache for this - could be better than querying the db for each input row
– Daniel Machet
Nov 23 '18 at 1:26
Hmmm fair point, only option I can think of is to remove the override (if that means creating a view in the db thdn so be it) , cache all the possible return values at the start of the sesdion and then use the input ports in your lookup condition. Not surd how many records you'll have to cache for this - could be better than querying the db for each input row
– Daniel Machet
Nov 23 '18 at 1:26
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53259424%2funconnected-lookup-input-value-not-working%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Formatting, highlighting of relevant parts
– Corion
Nov 12 '18 at 11:49
Any suggestion plz
– Abhijit Gupta
Nov 13 '18 at 3:21