Question Re: Data Insert in BODS 3.2 in Technology Q&A

Data Insert in BODS 3.2

Former Member — Mon, 12 Sep 2016 16:21:29 GMT

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

Re: Data Insert in BODS 3.2

Former Member — Mon, 12 Sep 2016 17:07:50 GMT

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

Re: Data Insert in BODS 3.2

Former Member — Mon, 12 Sep 2016 19:39:11 GMT

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

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 04:33:00 GMT

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.

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 06:30:13 GMT

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.

Re: Data Insert in BODS 3.2

former_member187605 — Tue, 13 Sep 2016 07:05:17 GMT


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

This is definitely not true!!!

Re: Data Insert in BODS 3.2

former_member187605 — Tue, 13 Sep 2016 07:07:18 GMT

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

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 14:06:25 GMT

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

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 14:09:02 GMT

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

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 14:21:04 GMT

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

Re: Data Insert in BODS 3.2

former_member187605 — Tue, 13 Sep 2016 14:34:53 GMT

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.

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 14:52:28 GMT

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

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 14:54:33 GMT

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

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 15:41:24 GMT

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)

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 16:15:15 GMT

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

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 16:21:35 GMT

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

Data flow:

Query:

Result:

Re: Data Insert in BODS 3.2

former_member187605 — Tue, 13 Sep 2016 17:25:52 GMT

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

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 17:50:21 GMT

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

Re: Data Insert in BODS 3.2

former_member187605 — Tue, 13 Sep 2016 17:55:19 GMT

Yes. I got exactly the same idea

Re: Data Insert in BODS 3.2

Former Member — Tue, 13 Sep 2016 20:20:10 GMT

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: