Tag: Oracle

ListAgg function – Oracle


List aggregation – LISTAGG()

It aggregates values from multiple rows into a single list.

Syntax:

LISTAGG(

Column [, Delimiter]

)

WITHIN GROUP (Order by Col_name)

  [Over(Partition by Col_name)]

This function comes handy when you are looking for an answer with concatenated strings.

Usage:

Select

ListAgg(department_name, ‘-‘) within group (order by

Depart_sort_no)

From department_master;

This query gives output of all department names in a – delimited list.

In some cases you have to deal with obtaining distinct values in the list.

Using the keyword distinct within the function gets the unique list.

ListAgg(distinct column_name)

Finally, how to deal with overflow. It means if the length of the string exceeds the standard data boundary. What should we do?

Let the system fail by throwing value exceed the length.

Do not worry, to do this there is an option in this function.

ON OVERFLOW TRUNCATE [<filler>] WITH[OUT] COUNT

This option is the last part of ListAgg()

Usage:

ListAgg(col1, ‘delimiter’ on overflow truncate)

Listagg(col1, ‘delimiter’, on overflow truncate with count)

Listagg(col1, ‘delimiter’, on overflow truncate without count)

Filler – can be used to represent obvious continuous characters such as …

ListAgg(col1, ‘delimiter’ on overflow …)

That is it, learn by doing, happy coding.

DECODE – Function – Oracle


The word decode triggers our mind to think of a mysterious task. But, in this context, it is a function in DBMS Oracle to perform a search task.

Functional usage:

DECODE compares expression to each search value one by one.

If expression is equal to a search, then it returns the corresponding result.

If no match is found, then it returns the assigned default value.

If default is omitted, then it returns null.

In a simple words to remember, this function is a simplified form of nested IF-Then-Else or a expression based Case statement.

Syntax:

DECODE(expression, search, result [, search, result ]...  [, default ])

Example:

This example is to give you a basic understanding on the function decode in the SQL statement. This example has hard coded values to show directions. But, you can also use this function in PL/SQL program.

SELECT 
       DECODE (3, 1, 'East', 
                  2, 'West', 
                  3, 'North', 
                  4, 'South',
                     'You are here') "Direction" 
  FROM Dual;

Thanks for reading. You can write the comment and share the usage of this function in your code.

ADO .Net Source Floating issue


I would like to discuss one of my migration project experience using SQL Server 2008 R2 SSIS.

The spot light issue is, i would like to make my article very simple. I have oracle as data source with few huge tables that is column wise and row wise as well.

I used SSMA tool to migrate oracle schema to sql server. it has created the data type mapping for number to float[53] or float in SQL server.

but, in my case data in Oracle number column has more the 40 scale length. in case of SQL Server float keep very less scale length approximately 15 scale length.

Now, I come to the matter. when I design a ssis package to transfer data from oracle to sql server, I preferred to use ado. net source and destination task. But ado.net source refused to handle the number data with more scale.

So, I was trying to find out the alternative solution to fix this issue. Immediately, my programming brain gave me one clue to change ado.net source to ole db source. Luckily, it works fine……

I said thanks to ???…. 🙂 but the solution was compromising not resolved. Because, while data transfer, it automatically truncated the float scale values after the maximum limit.

Anyway, I was able to convince the customer and completed the project 🙂

This is my experience with ado.net source task in ssis.

I will post another article with an interesting issue with solution….

Thanks
Ayyappan