Need Assistance for solving the Error

[Guest]

hi experts,
i need some assistance from all u experts… pLZ.

Here in our scenario, we pull the data using selectsql statement and inserts that selected data using 2 Adaptor service. But i m selecting only few number of columns from the source side and inserting few feilds of the destination. the rest of the destination feilds are notnull feilds.
Now it is giving me errror to Enter those feilds. How can i solve this problem.

Plz help me. I cant take all the feilds from source side and cannot enter all the feilds at destination because it doesnot match there. ( that is) at source side i have 90 fields out of which i m selecting only 15 feilds and at destination i have 30 feilds , in which 15 are simillar to the abouve 15 and rest are different.

Hope you got my questions. Plz help me in this…

Waiting eagerly
Thanks

[Guest]

Hello Experts
Can any body help me in solving this plzzzzzzzzzzzz

Best Regards
Yanna

[Satyam_Durvasula]

hi Yanna

If i understood fine then, may be u r trying to insert the rest of the fields that are notnull and trying it with the same insert SQL but rather go along and try with the update the rest of the fields that is instead of insert for the second time try to update the existing fields.

[Guest]

Hi SatYam,
Thanks for your reply. no no, i have to insert into 2 tables “X” and “Y” , for that purpose i m using 2 adapter services. i m trying to insert only 4 Fields of data into Table “X” and rest of the Fields in that table is not null( means it is must to insert the data into them and they are all 86 feilds left). How do i do that.

Plz help me
Thanks and Regards

[Guest]

Hi Yanna,
Sorry that I may have to ask you the following questions, but it will greatly help in understanding your complete problem.

1. The exact error message that you see in Server/Error Log.
2. Do the two tables that you are trying to insert into have referential constraints?
3. Do data types of the tables and the data types that you are sending match one to one?
4. Is it possible to use a single Adapter Service with Custom SQL to insert in both the tables … You will save a lot on making unnecessary DB I/O calls to external systems.

[Yemi_Bedu]

Hello,

Your question was how can you auto enter data you need but don’t want to fill. You can go to the database and set default values for those fields. You can also use a customsql or dynamicsql statement and do a more textual setup of those fields.

I know the insert adapter is a pain. I personally don’t use it except for three services I worked on in 2003. It is more cryptic for me to know what is going on in some places, but I then to wrap the dynamic call in a flow that would provide the visible interface for other services. You check out Favorite Debugging Tips …

Beyond that, you can enter in all the inputs in the insert adapter. Good day.

Yemi Bedu.

[Tim_Dwyer]

We personally don’t have any problems with the insertSQL template, but it’s good to know there are options.

I don’t know if you are using Oracle db, but we utilize the NVL (no value) function quite a bit to handle this type of situation. Not sure if other db vendors have something similar. Start by setting up the adapter service as normal, but then on the INSERT tab, double click inside the Expression and edit it to look like

      NVL(?, 'X')    

where ‘X’ is whatever default value you need to insert. Then in your flow when you call the adapter service, you only need to map the values you have. The rest get filled in by the nvl function at the db level.

[Yemi_Bedu]

Hello,

That sounds good. Can you use multiple of those? Like would it work if I had a date field, character, and number? I don’t use Oracle, but I am glad to here of this possible shortcut. What would I do if I want some null fields can be like that stay null? Good day.

Yemi Bedu

[Tim_Dwyer]

We use this for dates and numbers as well. You could do nvl(?, null) I suppose. We just haven’t had a need for that.

We do however use multiple functions at the same time. For instance we have used Expressions like

substr( nvl(?, ‘defaultvalue’), 1, 30)

This gets us a default if no value is provided and also trims the data to 30 characters if it is longer than 30. Useful and efficient way to handle truncating data to db field lengths.

Of course you need to be careful with this because the data manipulation is not visible in the flow service. It also binds the adapter service to the db vendor since these functions are Oracle specific.