Data Insert in BODS 3.2

[Former Member]

Hello,

I am experiencing a weird issue in BODS3.2.

I use "Task_lookup" to lookup values in target table (task table). "Case" defines when lookup results (look up results of ID) is null, then move to next transformation. Insert maps the information and key generation generate the new key for records I want to insert.

I got records up to case statement but no record count for insert query. The lookup is based on 4 equal conditions.

Lookup logic:

Monitor log:

CASE:

Insert:

Target property.

Any comments is appreciated.

Thanks.

Terry

[Former Member · Accepted solution]

Hi Terry,

Can you use Join instead of lookup.. I know we cannot use target as source in the same dataflow.. try using with sql transform or by staging target data to a staging table and use it for join.

Once i faced similar issue when using multiple conditions in lookup and got the corect output using join.

Create a test job and try this once.

Thanks,

Ravi kiran.

[former_member187605 · Accepted solution]

I know we cannot use target as source in the same dataflow.. 

This is definitely not true!!!

[former_member187605]

Your lookup doesn't return any matches. The default value being null, there's no output for the Case transform.

[Former Member]

Hello Dirk,

Even I set default value at 99999999 for o_ID (ID lookup result), I still don't have any results. I see the case_Insert has 221 records but no output from Query. I guess the issue is around primary key restriction (ID is primary key).

To handle this primary key restriction, I am thinking of creating a custom function so I can do key gen within Query. When I tried to create custom function, I got syntax error. I can't figure out what is wrong even I compared with reference guard. Everything seems to be ok but doesn't work.

I specified my input table based on data store information so it sohuld be correct and the column name is ID.

Thank you for your helps.

Terry

[former_member187605]

I am sorry, my mistake. I misinterpreted the monitor log output. All 221 rows flow from the Case to the Query transform. But nothing flows out of the latter. This can only be due to a filter.

What do you have as Where-condition in the Query?

By the way, no need for a custom function, you can use the key_generation built-in function directly in the mapping. And the results will be identical to those of the Transform with the same name.

[Former Member]

Hello Dirk,

I totally agree with you. That was my original plan.I tried key_generation built-in function and did not get any results. That is why I started to try different approaches.

Query transformation has not filters as shown below.

Here is what happens when I try using Key_generation built-in function.

Data flow:

Key generation transformation:

Results:

Everything looks right to me but just no results.

I checked target table as well which only has 123 records so there must be something which need to be inserted.

Thanks.

Terry

[Former Member]

Hi Terry,

please add this Key_gen function [key_generation('DS_ORA_AACST.TH_AACST.TASK', 'ID', 1)] to "Insert" query transformation and add temp table to see the data Or give file target to view the data.

Remove the default value for lookup.

it should be null to satisfy your Case statement.

Thanks,

Sreenivas

[Former Member]

Hello sreenivas,

Thank you for helping. I

tried this approach as well. Here is what I inputted and the syntax error I got. I compared with reference guide but could not figure out what I did wrong.

key_generation('DS_ORA_AACST.TH_AACST.TASK', 'ID', 1)

Error:

(Ln 1) : Syntax error : near <END> found <END_TRY> expecting <a string>

The function <NewFunction1> contains an invalid expression. Additional information: <syntax error>. (BODI-1111182)

[Former Member]

Dont required to put it in any function

you can use it directly in mapping by following these steps:

1. click on the ID column which you want to generate the key

2. Goto mapping tab

3. paste key_generation('DS_ORA_AACST.TH_AACST.TASK', 'ID', 1)

4. click apply and try to execute the job

Note make sure that you removed the Key_generation step after the Insert Query

Thanks,

Sreenivas

[Former Member]

Thanks again. I tried it but still get 0 records. Must be some small things I missed.

Data flow:

Query:

Result:

[former_member187605]

I don't understand. Can you please post the From tab ?

[Former Member]

Hi Dirk,

I do see some information icon on From tab and i am suspecting if there are any filters or conditions defined in From clause.

i want to see the from clause for filters

Thanks,

Sreenivas

[former_member187605]

Yes. I got exactly the same idea 

[Former Member]

I almost lost my mind. I switched target table to be a template table but still get nothing. I have no filters in From tab or Where tab.

Lookup

From

where

result:

[Former Member]

Hi Terry,

Can you please run the job in debug mode and see where exactly data is being dropped.

Please post screenshots as well..

Thanks,

Ravi kiran.

[Former Member]

This message was moderated.

[Former Member]

Somehow the debug mode always stops here and doesn't move forward. 12583044 1 JOB 2016-09-14 10:07:28 AM Optimizing job .

[Former Member]

Hi Terry,

Atleast can you try taking a temp table after case transform and verify if the data is loading to temp table?

[Former Member]

Thank you so much Ravi. I tried temp table and figured out the issue was with lookup results. I removed CASE statement then got it work. Instead of case, I used where statement and it is working now. It is really weird. The case and where should do the same thing. Anyways, it is working now and thank you so much for all supports.

[former_member254877]

Hi - I can see that you are pushing records to next level only if Look_Up.ID is null.

Pls perform below steps:

1) Create a some test table after Case transform and see what is the ID value you are getting after Look up

2) other way you can run through the Debug mode and see how the records getting dropped

Let me know if this works.

[Former Member]

Hello Srinivas, Since the lookup result doesn't include the primary key (ID), I got an error when I tried to push results into a temp table which has the same structure. I ran the debug mode but it stopped at "optimizing query", did not move forward. Thanks. Terry

[Former Member]

Can you share the screen shot for Task_lookup and Case

its look like first column is having primary key (default) which you can see at CASE_INSERT(source)

if that is the problem remove the key by following steps:

1. goto Task_lookup

2. Right click on the column in schema Out for first column

3. un-check the Primary Key option]

Thanks,

Sreenivas

[Former Member]

Hello,

Here is the screen shot of TASK_Lookup.

I don't have the option of unchecking the primary key.

Thank you for your reply.

Terry

[Former Member]

Is that because "ID" is the primary key in target table so it fails in the "Insert" query before hitting key generation?