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)

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.

Friday, October 13, 2017

Oracle Dual Table alternative equivalent in Microsoft SQL Server

Here is another post addition to the Oracle to Microsoft SQL Server series, This time we discuss about the DUAL table feature that is available in Oracle. However SQL server doesn't support queries on DUAL table as it is not present in the database. 

SELECT 1 FROM DUAL  returns invalid object name error in MSSQL Server.  -

Msg 207, Level 16, State 1, Line 1
Invalid column name 'SYSDATETIME'.

Dual object invalid in MS SQL Server.

Dual table is a one row table that Oracle provides where you can do one row calculations, use to display SYSDATE, test functions with default values etc. eg:- 

SELECT SYSDATE FROM DUAL - returns the system date from the database server. 
SELECT (9*50) FROM DUAL; - returns 450
Solution or Alternative to DUAL in MS SQL server is as below,  

In MSSQL, Any function, calculation, string after SELECT works, Please find the below examples along with screenshot.

SELECT GETDATE()  - returns the system date.
SELECT (9*50) - returns 450 
SELECT  'Hello World' 

Dual alternative in SQL Server.

An alternative to this syntax is to create a DUAL table in MS SQL server with the Dummy column and value 'X' under it. However you have to make sure you that all the users have access this to table to be used just as in Oracle database.

Tuesday, October 10, 2017

Performance difference between != and <> for NOT EQUAL filters in MSSQL / Oracle

Question: Is there a considerable performance improvement when using <> over the != operation in Microsoft SQL Server or Oracle Database ?

Answer: The answer is 'NO'. I had from somewhere heard the same that the <> operator performs better than the != operator. The < > operator for NOT EQUAL to can be preferred as the <> syntax is ANSI compliant unlike the != operator. Chances are when you migrate the same code to a different database, the <> operator is more likely to work without any compilation errors. 

So use <> operator instead of != as it is ANSI compliant and will help you reduce probable effort if a database migration happens. 

References: Stack Overflow




Tuesday, September 26, 2017

CREATE TABLE from SELECT query in Microsoft SQL Server


Scenario: Oracle has spoiled us so much, We recently came across a scenario where we wanted to have the result set of a SQL query to be saved as a table. In Oracle we have the following syntax to achieve the same,


CREATE TABLE STUDENTS AS SELECT STUD_ID, STUD_NAME FROM STUDENT;

This syntax would create me a table instantly, which I would be able to refer elsewhere. The same feature is available in Microsoft SQL Server as well, just that the syntax is different.  

Resolution: The SQL server equivalent of the above query would be as below, 


SELECT A.* INTO STUDENTS FROM( SELECT STUD_ID, STUD_NAME FROM STUDENT) A
syntax: 

SELECT A.* INTO Insert_Table_Name FROM 

Your query here.
) A

Note that you need not create the table upfront before running this script. The query creates the table and it's structure based on the result set.

We hope this came of help to you. For more such posts relating Oracle and Microsoft SQL Server check the label - Oracle to SQL Server .