Look up from SP when column is Null

[Anonymous]

H

New at this tool. and I can't find the solution in forum so therefore my question.

This is my scenario.

I have an MSSQL sp that produces a surrogate key for me when I call it. So in my flow I want to call the SP every time my surrogate key column is NULL, so I set a new surrogate keys for the records that does not have one. 

So I need to look up the SP row by row (to get a unique surrogate key) and only when the surrogate key column is NULL. I can't get it to work. what do I do wrong?

[Anonymous]

Hi,

   I don't think you need a SP here. You can capture the last used surrogate key in a configuration table of database. Before the load starts, you can extract the data from that table and store to a context variable.

   Now, in the tMap, you can check whether the data is available or not using ISNULL function. If the data is not present, you can use a sequence to generate the next value in Talend. Once the data is loaded, you can push the last used value back to DB in the next subjob (you will have to capture this value first to a context variable for easy data transfer back to DB).

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[Anonymous]

Thanks for the respons, but it will not do for my solution. The surrogate key generator has some logic in it and it has to execute to get the correct formatted surrogate key.

This is what the SP does to create the surrogate key I need (the reasons why are multiple)

@Key = CAST(REVERSE(SUBSTRING(CONVERT(VARCHAR(10),SYSDATETIME(),112),3,8)+RIGHT(replicate('0',12)+(CAST(NEXT VALUE FOR DBO.FOCAL_KEY_SEQ AS VARCHAR(12))),12))AS BIGINT)

So I need to have the ability to "call" on the SP row by row where the surrogate key is NULL. Is that even possible in this tool? (easily done in Data Stage and Informatica but right now Im stuck with Talend). Wasted hours on this that should just be a simple row by row look up.

I made some changes to the logic (see below) I set another output from the SP with a value I can join on (It works when it only access the SP once (load once), but then all rows gets the same surrogate key) I have tried mulitple set ups on match method for row by row access , but it still throws some huge java error log.

[akumar2301]

Reload at each row is ok.

You need to output of SP in flow in lookup flow. Try below Link
https://help.talend.com/reader/V2ZZaxdS78HmPacuPcEwkw/_l7WigLwFFyngax3SVApNA

[Anonymous]

Thanks for the respons!

But I am not sure what you mean. In my flow the look up against the SP is Look up flow (see picture below). Is that what you mean? (I could not really relate the link to my use case)

[akumar2301]

Instead of using tMap.

Try to use tDBSP in flow . As discribe in link.

The example job in a sense doing same thing. Enriching information using SP
in a flow.

You can modify your SP which will generate key only if IN parameter is null
otherwise return IN parameter.

[akumar2301]

https://help.talend.com/reader/wDRBNUuxk629sNcI0dNYaA/WN0YcNvB1H0c15B8azu74w

This will give you more idea

[Anonymous]

Ok tried to set it up. But the example only sends one row, any maybe that is why it works. When I run the rows from a table through the SP component it fails. What do you see is wrong in my picture.

[akumar2301]

try to use tparserecord
Try example :
Scenario 2: Using PreparedStatement objects to query Path
https://help.talend.com/reader/wDRBNUuxk629sNcI0dNYaA/WN0YcNvB1H0c15B8azu74w

[Anonymous]

Thanks for your effort but I could not make it work, so I went for a totally different solution with temp table and some other stuff.

Regards

Patrik