SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DI studio question: defaulting a Sysdate into a table column

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

DI studio question: defaulting a Sysdate into a table column

Ok folks, here is my problem of the day:

 

I am using DI Studio (v4.901)to pull in files, perform some minor mapping, and format changes, then output to a permanant table.

As I am populating the result table, I have 4 extra columns that my table loader procedure has no possible mapping for since they are not in the source table.

 

3 of them I am defaulting to 0 or blanks using a case statement to avoid null values.(they are for usage later)

The 4th (is actually the last column in the result table.. is a 'Last Update Date' column that I need to default the SYSDATE into as the records are written.

 

I haven't found an expression/process that I can use to populate this column.

I'm from an AS400 backround where a simple command can accomplish this.. SAS doesnt seem quite as easy.

 

I have attached a screen shot to give you an idea what I'm looking at.

 

 Any help would be appreciated!

 

-Chris N.

 

 

 

- Chris N.

Accepted Solutions
Solution
‎03-06-2017 10:40 AM
SAS Employee
Posts: 5

Re: DI studio question: defaulting a Sysdate into a table column

Hi Chris,

 

If you are using the “Table Loader” transformation, it has a “Load Time Column” option on the Options tab. The option is in the Additional Loader Options group.

 

If you want to use an expression try INPUT("&SYSDATE9",DATE9.) in the Expression field on the Mappings tab of the Table Loader properties.

 

Regards,

Robert

View solution in original post


All Replies
Super User
Posts: 5,257

Re: DI studio question: defaulting a Sysdate into a table column

I think that there is a Load time column option in almost all Table Loader transformations.

As for the zeros, no need for case, just enter a 0 in the mapping expression, it will resolve to
0 as column_name
Data never sleeps
Contributor
Posts: 31

Re: DI studio question: defaulting a Sysdate into a table column

I tried removing the case statements and simply putting a zero into the Expression column..

this worked for the 3 non-date columns.

 

For the LAST_UPDATE_DT column I could not find a load time option to default in the SYSDATE.

Adding simply 'SYSDATE' into the expression is not valid. Leaving the column unmapped/without expression also gives me a run time error. You cannot do a null value insert into the column. I also tried to use a case staement to put in SYSDATE or TIMESTAMP.  the code validator was not very happy with either idea. It says that they are not columns found in my source table

 

this is the error I receive:

 

Cannot insert the value NULL into column 'LAST_UPDATE_DT', table 'MDMCDISTGDB.LANDINGZONE.CUSTOMER'; column does not allow nulls. INSERT fails. : [SAS/ACCESS to SQL Server][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The statement has been terminated.

 

any other Ideas?

-Chris N.

- Chris N.
Solution
‎03-06-2017 10:40 AM
SAS Employee
Posts: 5

Re: DI studio question: defaulting a Sysdate into a table column

Hi Chris,

 

If you are using the “Table Loader” transformation, it has a “Load Time Column” option on the Options tab. The option is in the Additional Loader Options group.

 

If you want to use an expression try INPUT("&SYSDATE9",DATE9.) in the Expression field on the Mappings tab of the Table Loader properties.

 

Regards,

Robert

Contributor
Posts: 31

Re: DI studio question: defaulting a Sysdate into a table column

Hi Robert,

 

Success!!

Thanks for the help. I found the additional loader column and the date populated perfectly. The fast track courses I have taken probably touched on this, but it was a lot to digest in such a short time frame.. 

 

I'm going to test the other method as well to see what happens. Its always good to learn more than one way to do something.

 

Thank you again for the help!  Smiley Happy

 

-Chris N.

 

 

- Chris N.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 157 views
  • 0 likes
  • 3 in conversation