Data Insert in BODS 3.2

[#1]

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_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.