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. 

gen_row_num_by_group function in SAP BODS not creating sequence properly [SOLVED]

Issue: A week back, I had written a post on how we used the gen_row_num_by_group function create a sequence of numbers based on a particular column in BODS. I ran into an issue sometime later, where the sequences were not getting properly generated.

Cause: This is because the input source was not properly sorted. When you use this particular function based on a column, you should note that the input to the query transform should be properly sorted first. This is very important, if you miss this then the data will not be properly sorted and hence create wrong sequence numbers.

Resolution: The way to resolve this issue is to have your input data sorted. For example, if you are creating a sequence based on ClassId column as below,

gen_row_num_by_group(ClassId)

You should in this case make sure that the data coming to the query transform be grouped on this particular column, i.e ClassId in this example. This makes sure the sequence gets generated accordingly. 

Please do write to us, if you found this useful. 

Found erroneous expression <> in SAP BODS Designer [SOLVED]

Issue: SAP BODS shows no error on validation, but comes up with this 'Found erroneous expression <> ' error at times. 

Found erroneous expression error in SAP Data Services Designer

Cause:  This is because your workflow, or dataflow might not have proper calls to variables/parameters that are being used. The variables may not be properly referenced. The value of the parameter probably is not being passed from the parent object(job,workflow) to the child object(workflow, dataflow). 

Resolution: Check for parameters used and whether they are correctly being referenced from the child object. Solved the problem for me.  

References

1. http://www.forumtopics.com/busobj/viewtopic.php?p=1036501

Dynamically Generate a Target Flat File Format from Dataflow in SAP BODS Designer

Scenario: To generate a flat file format from inside a data-flow rather than typing each column, datatype and size.

Solution: Being new to SAP BODS designer, I found it really hard to type out each column in a flat file format, along with the datatype and it's corresponding size. It can become a tiresome process. 

The easiest solution is to go to one of your query transforms and on the schema out pane(right pane), right click on the parent of your 'Schema out' as in screenshot below,

SAP BODS - Create File Format Generation Option ( Enlarge )

Now from the list that opened up, select 'Create File Format'. This will open up a flat file format window for you with same column names, datatypes and lengths as in the schema out part of your query. 

You can then save the same, edit the properties and use in your BODS dataflow for data loading.  

Generate a number sequence grouped on a column/key in SAP BODS

Scenario: Create a sequence number grouped or based on another key/column value from the source result set. 

eg: consider the following data-set

Now in the target dataset/table, we require a new column which would sequentially assign a number starting from 1 for each student under a particular class. The result set then would look something like below, 

Now we have this new column (StudentSeqNbr) which sequentially numbers each student under a particular ClassId. The aim is to create such a sequential column using SAP BODS.

Solution: SAP BODS has an inbuilt function which will let you create such a sequence and can be mapped inside a Query transform.

Gen Rownum function in SAP BODS( Click to enlarge )

Using this function and the below syntax, a sequence can be created for the scenario above.

gen_row_num_by_group(ClassId)

Syntax:

gen_row_num_by_group(ColumnName)

UPDATE: The input should be sorted for this function to work properly to create a sequence.