Sunday, March 18, 2018

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. 




Thursday, March 15, 2018

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

Tuesday, February 27, 2018

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.  

Friday, February 23, 2018

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. 

Monday, October 23, 2017

Conditional OR joins in SQL query returns duplicate rows

Scenario: Whenever I have a particular OR condition join in my query, I get a duplicated record. The case is when the two join conditions before and after OR are satisfied.

SELECT A.Column1,A.Column2, Name, Address FROM A, B
WHERE (
(A.COLUMN1 = B.COLUMN1) OR (A.COLUMN2  = B.COLUMN2)
)

Cause: Imagine there are rows in A and B that satisfy the two join conditions used in OR, In such a scenario the database will return two rows with same value.


Here as per the query, for row 'Athul' in Table A, both the join conditions match with B and hence will return below output resultset,



As you can see in the result above, 'Athul' is resulted twice.  However for the second recrod

Solution: This can be avoided by using a LEFT join instead of an OR join condition. Do not consider using DISTINCT as it is a fairly cost consuming alternative.