Home > Software > Data-Warehouse > DataStage
Interview Questions   Tutorials   Discussions   Programs   Discussion   

DataStage - How do u set a default value to a column if the column value is NULL?




2913
views
asked mar September 20, 2014 06:33 AM  

How do u set a default value to a column if the column value is NULL?


           

1 Answers



 
answered By vishnoiprem   0  
When Come To RDBMS Northing Plays an important role .It Must be handled With Care
While Processing Data in Any ETL.Here Some of Predefined Null Handling Functions in Datastage
Usage: Transformer(Field Derivation ,Output Link Constraints, Stage Variables  and      Loop Variables)
IsNotNull
Returns true when an expression does not evaluate to the null value.
  • Input:     any
  • Output:  true/false (int8)
  Examples.
If the Derivation field for an output column contained the following code, then the Transformer stage checks if the input column named mylink.mycolumn contains a null value. If the input column does not contain a null, the output column contains the value of the input column. If the input column does contain a null, then the output column contains the string NULL.
If IsNotNull(mylink.mycolumn) Then mylink.mycolumn Else "NULL"
IsNull
Returns true when an expression evaluates to the null value.
  • Input:    any
  • Output: true/false (int8)
  • Examples. If the Derivation field for an output column contained the following code, then the Transformer stage checks if the input column named mylink.mycolumn contains a null value. If the input column contains a null, the output column contains the string NULL. If the input column does not contain a null, then the output column contains the value of the input column.

If IsNull(mylink.mycolumn) Then "NULL" Else mylink.mycolumn
NullToEmpty
Returns an empty string if the input column is null, otherwise returns the input column value.
  • Input:     input column
  • Output: input column value or empty string
  • Examples.
If the Derivation field for an output column contained the following code, then the Transformer stage checks if the input column named mylink.mycolumn contains a null value. If the input column contains a null, the output column contains an empty string. If the input column does contain a null, then the output column contains the value from the input column.
                      NullToEmpty(mylink.mycolumn)
NullToZero
Returns zero if the input column is null, otherwise returns the input column value.
  • Input:    Input column
  • Output: input column value or zero
  • Examples.
If the Derivation field for an output column contained the following code, then the Transformer stage checks if the input column named mylink.mycolumn contains a null value. If the input column contains a null, the output column contains zero. If the input column does contain a null, then the output column contains the value from the input column.
                      NullToZeroy(mylink.mycolumn)
NullToValue
Returns the specified value if the input column is null, otherwise returns the input column value.
·         Input:    input column, value
·         Output: input column value or value
·         Examples.
If the Derivation field for an output column contained the following code, then the Transformer stage checks if the input column named mylink.mycolumn contains a null value. If the input column contains a null, the output column contains 42. If the input column does contain a null, then the output column contains the value from the input column.
               NullToValue(mylink.mycolumn,42)
SetNull
Assigns a null value to the target column.
·         Input:    - N/A
·         Output: - NULL (DS)
·         Examples.
If the Derivation field for an output column contained the following code, then the Transformer stage sets the output column to null:
flag   
   add comment

Your answer

Join with account you already have

FF

Preview


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert