BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

map the default keyword target table with default constraint


 
Search this topic... | Search Data Integrator... | Search Box
Register or Login to Post    Forum Index -> Data Integrator  Previous TopicPrint TopicNext Topic
Author Message
asrafi
Forum Member
Forum Member



Joined: 23 Jan 2013

Posts: 3



PostPosted: Fri Dec 01, 2017 10:32 am 
Post subject: map the default keyword target table with default constraint

Hi

We have an issue with BODS and SQL Server. We have tables that have default constraints managed by the server, and which BODS is not allowed to apply a value for.

Take a look on this sample table:

Code:
--drop table testDefaultValue
create table testDefaultValue
(
            Col1 int not null,
            Col2 varchar(30) not null,
            Col3 varchar(30) not null constraint df_testDefaultValue_col3 default 'Default Value',
            [Action] varchar(100) not null
)



So far the solution was not to map these columns to begin with when adding them to the data store. This led to BODS creating SQL that did not apply any values to these columns, and SQL Server used the default value.

Code:
/*1 - insert a row with out the column that has the default constraint*/
insert into testDefaultValue (Col1, Col2,Action)
values (1,'something','insert a row with out the column that has the default constraint');

select *
from testDefaultValue as tdv;



However, we want to be able to do initial load, and here we would like to provide the values for these columns for the full load data flows only. Therefore we must add these columns to the data store definition.

What I need to know is how I can make BODS to use the “default” key word in SQL, as a value for a column:

Code:
/*2 - insert a row providing a value for all 3 columns*/
insert into testDefaultValue (Col1, Col2, Col3,Action)
values (2,'something', 'manualy added value', 'insert a row providing a value for all 3 columns');

select *
from testDefaultValue as tdv;


/*3 - insert a row using the default keyword*/
insert into testDefaultValue (Col1, Col2, Col3, Action)
values (3,'something', default, 'insert a row using the default keyword');

select *
from testDefaultValue as tdv;


How can I make bods act like the third insert query?[/code]
Back to top
brudosm
Forum Member
Forum Member



Joined: 12 Dec 2014

Posts: 4



PostPosted: Wed Dec 06, 2017 9:23 am 
Post subject: Re: map the default keyword target table with default constr

Hi - I'm not aware of a way to make DS work like the 3rd query. SQL Server identities and defaults are a hassle to work with in Data Services. Typically I will bring the table in as-is and have separate branches in my DF for insert and update. For example, if there is an identity column, on the insert branch I will not pass that column to the target table.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0134 seconds using 18 queries. (SQL 0.0034 Parse 0.0002 Other 0.0098)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo