SAP Data Services SQL Transform with variable - Invalid Pseudocolumn error [Solved]

Scenario: I had a scenario where in I needed to achieve a lead, lag column logic in BODS and had to do with a SQL Transform. Here I have a filter on date, i.e filter CreateDateTime column less than extract date. So I have a variable $PV_ExtractDate where in I pass my extract date value.

which would make my query look something as below,

SELECT COL_A, COL_B FROM SALES WHERE CreateDateTime < $PV_ExtractDate

The above syntax in SQL Transform was creating an error for me as the value was not being passed to the variable.

Error: SQL submitted to ODBC data source <SAMPLE> resulted in error <[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid pseudocolumn "$PV_ExtractDate".>

Cause: BODS doesn't recognize $PV_ExtractDate as a variable value in SQL Transform, this can be corrected by using the following syntax,

SELECT COL_A, COL_B FROM SALES WHERE CreateDateTime < {$PV_ExtractDate}

Note: It is advised not to go with a SQL Transform, unless no workaround is feasible in SAP BODS. This is because you are writing code that is specific to a particular database, hence if the source system is changed to a new type of database an issue might occur.

New column in SAP BODS Template Table not reflecting in another Dataflow

Scenario: I am pivoting and creating a few flags into a template table in BODS and in the corresponding workflow I am using this template table in another dataflow to aid the data load.

I have now added a new flag to the template table, ran the load for a first time. The template table now has the new flag column in it in the database. Now I go to the dataflow and try to use the new flag that was created, but I am not able to see the same. 

Cause: This is a common issue, changes to template tables not getting reflected in BODS. It is not possible to import template table into BODS, if you right click a template table and do it, the table will move to table definitions in the datastore.  

Resolution: The resolution is to drag in another instance of the temp table, say if your template table name is TEMP in flow, dragging a new table and joining to existing source will create a new alias named TEMP_1. Now delete the old template table reference and again drag in a new reference, now since the existing alias(TEMP) is gone, this new reference will be named as TEMP. Now you may delete the TEMP_1 reference.  

Once done you should be able to see the change, i.e the new flag in the dataflow. 

tags: SAP BODS Data Services Designer template table not reflecting in new dataflow.