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

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: Upcoming Events: BI+Analytics Huntington Beach CA: Feb 21.

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
ASUG Icon
Posts: 8



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
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4269
Location: Denver Area


flag
PostPosted: Tue Jan 09, 2018 4:10 pm 
Post subject: Re: map the default keyword target table with default constr

I can write a query against the Oracle system tables to grab the default value for a column. You should be able to do the same thing against SQL Server.

I would write a custom function in Data Services that does the query against the system tables. The input would be the table and column names. Do not use this function within the mapping of columns that use a default. This would perform poorly. Instead, create parameters to the Dataflow and in the call to the Dataflow put the function there, one parameter for each column that uses a default.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
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.0111 seconds using 18 queries. (SQL 0.0031 Parse 0.0002 Other 0.0078)
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