Example SQL statements for transforms

Convert a number to a string to populate into a string column

UPDATE {temptable} SET RATESTRING = CONVERT(NVARCHAR, RATENUM) WHERE RATENUM IS NOT NULL

If Unicode is disabled for the string column, then convert to varchar instead.

Convert a string to a number to populate into a numeric column

UPDATE {temptable} SET RATENUM = CONVERT (BIGINT, RATESTRING) WHERE RATESTRING IS NOT NULL

or

UPDATE {temptable} SET ACCT=CAST (tAcct as BigInt)

If you are using Integer 32 or Integer 16, then convert to int or smallint instead.

Convert a datetime field to a concatenated string (yearmo)

UPDATE {temptable} SET YEARMO = YEAR(DateTimeField) * 100 + MONTH(DateTimeField)

Convert a datetime field to a concatenated string (yearmonthday)

UPDATE {temptable} SET YEARMONTHDAY = (YEAR(DateTimeField) * 100 + MONTH(DateTimeField)) * 100 + DAY(DateTimeField)

Other conversion statements

CONVERT(STRING(xx), RATENUM)

CONVERT(BIGINT, RATESTRING)

CONVERT(DECIMAL(28,14), RATESTRING)

Convert values to a negative number if a credit column exists in the temp table

UPDATE {temptable} SET M1 = - M1, M2 = -M2, M3 = -M3, M4 = -M4, M5 = -M5, M6 = - M6, M7 = -M7, M8 = -M8, M9 = -M9, M10 = -M10, M11 = -M11, M12 = -m12 WHERE Credit = 'C'

Perform math on a field if another field in the temp table contains a value

UPDATE {TempTable} SET M1 = M1 * Rate WHERE Rate <> ''

Round a value to 2 decimals and replace the value

UPDATE {temptable} SET RATE = ROUND(RATE, 2)

Divide two integer numbers and keep the decimal

Remember that an integer divided by an integer returns only an integer (example: 5/7 = 0). If you want to capture the decimal remainder, you must cast the integer values as decimal:

CAST(INT1 AS DECIMAL(28,14)) / CAST(INT2 AS DECIMAL(28,14))

Pivot incoming data with respect to time

UPDATE {temptable} SET
  M1 = case when (TheMonth=1) then Amt else 0 end,
  M2 = case when (TheMonth=2) then Amt else 0 end,
  ETC.

Pivot data from columns to rows (using a monthly variable)

Update {temptable} set Amt = 
  Case
    When ({VarMonth}=1) then M1
    When ({VarMonth}=2) then M2
    ETC.                            
    Else 0
  End  

Perform a find and replace in a data column to detect the # character and replace it with nothing

UPDATE {temptable} SET AcctDesc = REPLACE(AcctDesc,'#','')

Place zeros in a field rather than null values

UPDATE {TempTable} SET Rate = 0 WHERE Rate IS NULL

Insert new records into the temp table by summarizing transaction detail records

INSERT INTO {temptable} (DEPT,ACCT,TRANSID,M1,M2,M3,DELETE) SELECT DEPT,ACCT,’Summarized’,SUM(M1),SUM(M2),SUM(M3),‘DeleteFlag’ FROM {temptable} GROUP BY DEPT,ACCT

Add an identity or row number to each record, using the system column AxReference

UPDATE {temptable} SET MYROWNUMBER={temptable}.AxReference

Delete records from the temp table with a flag set

DELETE from {temptable} where FLAG = 'DeleteFlag'

Delete records from the temp table where the dimension combination already exists in the destination table

This example might be used to load only new transactional records and leave existing destination records untouched.

DELETE FROM {temptable} WHERE {temptable}.DEPT IN (Select DEPT from {TRANSACTIONTABLE} group by DEPT) and {temptable}.TRANSID IN (Select TRANSID from {TRANSACTIONTABLE} group by TRANSID)

Concatenate strings together with a hyphen in between

UPDATE {temptable} SET DESCRIPTION = DESC1 + ‘ – ‘ + DESC2

Fill an entire column with the same value

UPDATE {temptable} SET RATE = 0

Copy the left 5 characters into a new column

UPDATE {temptable} SET ShortDesc = LEFT(LongDesc,5)

Subtract 1 character from the end of a field and copy the remaining text to a new column

UPDATE {temptable} SET ShortDesc = LEFT(LongDesc, (LEN(LongDesc) – 1))

Aggregate balance sheet records

UPDATE {temptable} SET M1=BegBal+M1
UPDATE {temptable} SET M2=M2+M1
UPDATE {temptable} SET M3=M3+M2
UPDATE {temptable} SET M4=M4+M3
UPDATE {temptable} SET M5=M5+M4
UPDATE {temptable} SET M6=M6+M5
UPDATE {temptable} SET M7=M7+M6
UPDATE {temptable} SET M8=M8+M7
UPDATE {temptable} SET M9=M9+M8
UPDATE {temptable} SET M10=M10+M9
UPDATE {temptable} SET M11=M11+M10
UPDATE {temptable} SET M12=M12+M11

Import into only the current period using a variable

On the Mapping tab, for each column where you want to use a variable, set the destination column as {M1}...{M12} instead of the normal M1 … M12.

On the Transforms tab, create 12 statements—one for each data column that you set up with a variable. The SQL for each statement is as follows (each statement will have a different current period value and a matching column name):

SELECT RESULT =
  CASE
    WHEN {CurrentPeriod} = 1 THEN 'M1'
    ELSE ''
  END

In the Target Variable Name section of the transform, type the name of each variable. In the text above, the variable name would be M1. The next statement would be M2, then M3, and so on. This will set the variable value to the result of the SQL statement. Columns that do not match the current period will be set to ", which means they will not be imported to the destination table.

Abort the import if no data exists to import

IF (SELECT COUNT(*) FROM {temptable}) = 0

RAISERROR ('Temptable was empty, aborting import', 11, 0)

Update a reference table with new elements that currently exist in the temp table, but not in the reference table

Whenever possible, the new built-in function Add new dimension elements should be used for this instead of a SQL statement. See Add new dimension elements during an import. If this function cannot be used, then use a SQL statement like the following:

INSERT INTO {ACCT}(ACCT, Description) Select ACCT, 'Exception from Import' FROM {temptable} WHERE NOT EXISTS (SELECT ACCT FROM {ACCT} WHERE {temptable}.ACCT = {ACCT}.ACCT GROUP BY ACCT) GROUP BY ACCT

Zero out columns in a destination based on a flag in a reference table

UPDATE {{destinationtable}} set M1=0,M2=0,M3=0,M4=0, M5=0,M6=0,M7=0,M8=0,M9=0,M10=0,M11=0,M12=0 FROM {{destinationtable}} INNER JOIN {ACCT} ON {ACCT}.ACCT={{destinationtable}}.ACCT WHERE {ACCT}.ZEROME='1'

This example assumes that {destinationtable} is an import variable that resolves to a table name.

Delete large numbers of records from a table

DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1
WHILE (@RowsDeleted > 0)
  BEGIN
    DELETE TOP (10000) FROM MyTable [WHERE .....]  
    SET @RowsDeleted = @@ROWCOUNT
  END

The WHERE clause in the DELETE step is optional.