Lateral Views in Oracle 12c.

#TGIF.

In Oracle 12c, we got introduce to LATERAL Views officially(Before same was getting use internally as part of Query Transformation) to join Inline views, Pre 12c we were not able to achieve same of joining inline views.

With Lateral View hint, we can have correlated inline views in FROM Clause itself.
It allows to refer Inline View to help achieve join in FROM Clause and help to restrict same.

Lets start with examples, Suppose we require data like below.

COL1     COL2
------- ----------
1       1
2       1
2       2
3       1
3       2
3       3
4       1
4       2
4       3
4       4

For a value in COL1, we need to display number from 1 till col1 values in COL2.

below is the sample SQL to achieve similar results,

SELECT L.COL1, M.COL2
FROM
(SELECT LEVEL COL1 FROM DUAL CONNECT BY LEVEL <= 4 ) L ,
(SELECT LEVEL COL2 FROM DUAL CONNECT BY LEVEL <= L.COL1) M

Without LATERAL View hint, it won’t allow joining within Inline Views.
It is bound to give below Exception.

ORA-00904: "L"."COL1": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 4 Column: 53

Lets try using LATERAL Hint.

SELECT L.COL1, M.COL2
FROM
(SELECT LEVEL COL1 FROM DUAL CONNECT BY LEVEL <= 4 ) L ,
LATERAL (SELECT LEVEL COL2 FROM DUAL CONNECT BY LEVEL <= L.COL1) M

COL1 COL2
---------- ----------
1       1
2       1
2       2
3       1
3       2
3       3
4       1
4       2
4       3
4       4

Lets have a look at underlying execution plan,

-------------------------------------------------------------------
| Id | Operation |                         Name | Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT                              | | 1 |
| 1 | NESTED LOOPS                                   | | 1 |
| 2 | VIEW                                          | | 1 |
|* 3 | CONNECT BY WITHOUT FILTERING | | |
| 4 | FAST DUAL                                       | | 1 |
| 5 | VIEW                                 | VW_LAT_A18161FF | 1 |
|* 6 | CONNECT BY WITHOUT FILTERING| | |
| 7 | FAST DUAL                                          | | 1 |
-------------------------------------------------------------------

Its work as nested loop or Filter clause in SQL Server, for each outer inner will get invoke\executed.

Had used Lateral Views to get all prime number till 100.
SQL for same,

SELECT 1 as prime_number FROM DUAL
UNION ALL
SELECT COL1
FROM
(SELECT COL1 ,
COUNT(
CASE
WHEN MOD(COL1 ,
CASE
WHEN COL2 = 1
OR COL2 = COL1
THEN NULL
ELSE COL2
END) = 0
THEN 1
ELSE NULL
END) CNT
FROM
(SELECT LEVEL + 1 COL1 FROM DUAL CONNECT BY LEVEL <= 99
) L ,
LATERAL
(SELECT LEVEL COL2 FROM DUAL CONNECT BY LEVEL <= L.COL1
)
GROUP BY COL1
ORDER BY 1
)
WHERE CNT = 0;

Advertisements
Posted in Oracle 12c New Features, Oracle Internal | Tagged , , | Leave a comment

SQL Server : Value Constructor Insight.

We all must be aware of VALUE Clause for Inserting values specified, in current blob we will explore different ways other then Insert were we can have use VALUE as handy.!

1. Generating Dummy Data!

we can use Value to generate tabular data for further joins or any functional usage.
we can have multiple column data as shown below.

SELECT TAB.COL1, TAB.COL2
FROM
(
VALUES (1,2),(3,4) ,(5,6)
) TAB(COL1,COL2);

Output :
COL1 COL2
----------- -----------
1 2
3 4
5 6

(3 row(s) affected)

2. Insert Multiple Records as one statement.

If can be done in a statement do it that way!
Instead of having multiple statement for same usage, it always efficient to accomplish same as single statement.

Using VALUES we can perform similar insert as one statement as compared to separate insert statement for each row..

CREATE TABLE TBL
(
C1 INT ,
C2 INT ,
C3 INT
);

INSERT INTO TBL(C1,C2 ,C3)
VALUES (1, 2, 3)
, (4, 5, 6)
, (7, 8, 9);

3. Get Max value from Set of Column using VALUES.

SELECT * ,
( SELECT MAX(COL) FROM (VALUES (C1) , (C2) , (C3) ) AS TAB(COL)) AS MAX_COL_VALUE
FROM TBL

OUTPUT:
c1 c2 c3 MAX_COL_VALUE
----------- ----------- ----------- -------------
1 2 3 3
4 5 6 6
7 8 9 9

4. SELECT within Values

We can have Select statement as part of VALUES.

INSERT INTO TBL(C1,C2 ,C3)
VALUES ((SELECT 1), (SELECT 2*7), (SELECT TOP 1 C1 FROM TBL))

(1 row(s) affected)

Hope it was a bit interesting and we learn something new.

Posted in SQL Server Learning | Tagged , , | Leave a comment

SQL Server : ExecuteNonQuery shows incorrect rows affected.

For one of client as per functionality we were invoking a SQL Server procedure using ExecuteNonQuery method.
Within procedure we had a DML statement performing update and on rows affected as return from ExecuteNonQuery performing further functional stuff.

In one of case for a table, though Rows impacted were only 1 but rows affected return through ExecuteNonQuery was more than 1.
On further analysis, identified we had a trigger enabled on updated table performing some DML actions.

From MSDN documentation:
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1

Let understand it better with field work :
We will create a simple procedure with an insert on table having trigger enabled.

drop table tab_row_affected;
create table tab_row_affected
(col1 int ,col2 datetime ,col3 varchar(10));

drop table tab_row_affected1;
create table tab_row_affected1
(col1 int , col2 datetime ,col3 varchar(10));

--Create procedure with insert on tab_row_affected
CREATE PROCEDURE USP_TESTROWSAFFECTED
AS
INSERT INTO tab_row_affected VALUES (1,GETDATE(),'INSERT');
PRINT @@ROWCOUNT

---create trigger on tab_row_affected
CREATE TRIGGER TRIG_TESTROWSAFFECTED on tab_row_affected
FOR INSERT
AS
INSERT INTO tab_row_affected1 VALUES (1,GETDATE(),'TRIGGER');

Sample C# Code :

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SqlConnection myConn = new SqlConnection(@"");
try
{
SqlCommand scommand = new SqlCommand("dbo.USP_TESTROWSAFFECTED", myConn);
scommand.CommandType = System.Data.CommandType.StoredProcedure;
myConn.Open();
Console.WriteLine("Calling Stored Procedure!!");
int rows_affected = scommand.ExecuteNonQuery();
Console.WriteLine("Rows Affected :: " + rows_affected.ToString());
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
myConn.Close();
}}}}

On Executing Above Code, below is the Console output :

As solution to get only Rows affected within procedure and ignore rows affected as part of trigger we can use”SET NOCOUNT ON” in Trigger.
When NOCOUNT is set as ON for a block, it stop sending Count of rows affected.

/*Changing Trigger with NOCOUNT*/
SET NOCOUNT ON, will
CREATE TRIGGER TRIG_TESTROWSAFFECTED on tab_row_affected
FOR INSERT
AS
SET NOCOUNT ON;
INSERT INTO tab_row_affected1 VALUES (1,GETDATE(),'TRIGGER');

Post Trigger changes, now rows affected is reflecting only rows impacted within procedure.

Posted in SQL Server Learning | Tagged , , , | Leave a comment

SQL Server : Get Numeric part from a varchar/string.

Found question to fetch only numeric information from a string/varchar in some of blogs, so thought let try and share my attempt.

Implement logic:
1. Using Recursive With clause, segregated each and every character as separate row.
2. Using Regex like check for numeric information [0-9]
3. Concat all number based on regex match and project same.

Declare @s varchar(100),@result varchar(100)
set @s='fdf4ff345rg564rt5434tr'
set @result='';
with alias1(data1, data2) as
(
select 1 as data1 , SUBSTRING(@s,1,1) as data2
union all
select data1+1 , SUBSTRING(@s,data1+1,1)
from alias1 where LEN(@s) > data1 --**Important, does not remove condition
)
select @result = @result + case when data2 like '[0-9]' then data2 else '' end
from
(select data2 from alias1) as final option (MAXRECURSION 0);
select @result as only_numbers;

only_numbers
-----------------
43455645434

please note :
Added option (MAXRECURSION 0) to handle default max recursion limit (100) exception.

Reference to blog for alternate solution :
https://madhivanan.wordpress.com/2017/03/21/extract-only-numbers-from-alphanumeric-string/

Posted in Helper SQL, SQL Server Learning | Tagged , , | Leave a comment

Date Generator in SQL Server using Recursive With Clause.

During one of performance analysis, identified lofty insert statements populating static tables with Date information.
As part of one of functionality, we were generating dates and applying necessary function on it within date range specified.

As part of performance improvement, need to move Date generation from Front end to database.
For generating date range and apply necessary function on it, had used Recursive with clause with termination based on Date range conditions.

It will generate dates between specified dates range and get below attribute for each and every date.

1. DATETIME
2. NEXT_DAY_DATE
3. PREV_DAY_DATE
4. QUARTER
5. YEAR
6. MONTH
7. MONTH_NAME
8. WEEK
9. WEEK_DAY
10. DATE_YYYYMMDD
11. DATE_MMYYYY
12. YEAR_YYYY
13. YEAR_YY
14. MONTH_MM
15. WEEK_WW
16. DAY_DD
17. DAY_OF_WEEK
18. DAY_OF_MONTH
19. DAY_OF_YEAR
20. START_OF_YEAR_DATE
21. END_OF_YEAR_DATE
22. NEXT_YEAR_DATE
23. START_OF_WEEK_DATE (based on @@DATEFIRST being default as SUNDAY)
24. END_OF_WEEK_DATE
25. START_OF_MONTH_DATE
26. END_OF_MONTH_DATE
27. NEXT_MONTH_DATE
28. DAYS_IN_YEAR
29. DAYS_IN_MONTH
30. IS_LEAP_DAY (Check for 29th Feb)
31. IS_LEAP_YEAR

Code base :

DECLARE @StartDateTime DATE
DECLARE @EndDateTime DATE
SET @StartDateTime = '2000-01-01'
SET @EndDateTime = '2017-12-31';
WITH DateRange
AS
SELECT
@StartDateTime AS 'DATE_TIME', --CURRENT DATE
'D' AS 'DATE_TYPE' , --DATE TYPE AS NORMAL DAY IT IS HARDCODED.
DATEADD(D, 1, @StartDateTime) AS 'NEXT_DAY_DATE', --NEXT DAY FROM CURRENT DATE BY USING DATEADD TO ADD 1.
DATEADD(D, - 1, @StartDateTime) AS 'PREV_DAY_DATE', --PREVIOUS DAY FROM CURRENT DATE BY USING DATEADD TO ADD -1.
DATEPART(Q, @StartDateTime) AS 'QUARTER', --GET QUARTER OF THE YEAR USING DATEPART FUNCTION.
DATEPART(YYYY, @StartDateTime) AS 'YEAR', --GET YEAR PART OF CURRENT DATE USING DATEPART FUNCTION
DATEPART(MM, @StartDateTime) AS 'MONTH', --GET MONTH PART OF CURRENT DATE USING DATEPART FUNCTION
DATENAME(month, @StartDateTime) AS 'MONTH_NAME' , --GET NAME OF MONTH BASED ON CURRENT DATE PASSED.
DATEPART(WK, @StartDateTime) AS 'WEEK', --GET WEEK PART OF CURRENT DATE USING DATEPART FUNCTION
DATENAME(weekday, @StartDateTime) AS 'WEEK_DAY', --GET WEEKDAY OF CURRENT DATE USING DATEPART FUNCTION
CONVERT(char(10), @StartDateTime, 112) AS 'DATE_YYYYMMDD', --GET DATE AS FORMATTED AS YYYYMMDD USING FORMAT 112
RIGHT (REPLICATE('0', 6) + CAST(DATEPART(MM, @StartDateTime) AS VARCHAR) + CAST(DATEPART(YYYY, @StartDateTime) AS VARCHAR), 6) AS 'DATE_MMYYYY', --GET DATE AS MMYYYY FORMAT.
DATEPART(YYYY, @StartDateTime) AS 'YEAR_YYYY', --GET YEAR PART FROM CURRENT DATE
SUBSTRING(CAST(DATEPART(YYYY, @StartDateTime) AS varchar), 3, 2) AS 'YEAR_YY', --GET YEAR PART AS YY FROM CURRENT DATE USING SUBSTRING FUNCTION AND DATEPART
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(mm, @StartDateTime) AS VARCHAR), 2) AS 'MONTH_MM', --GET MONTH PART AND PADDED WITH LEADING ZERO UPTO LENGTH TWO (01,02 ..)
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(WK, @StartDateTime) AS VARCHAR), 2) AS 'WEEK_WW', --GET WEEK PART AND PADDED WITH LEADING ZERO UPTO LENGTH TWO (01,02 ..)
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(DD, @StartDateTime) AS VARCHAR), 2) AS 'DAY_DD', --GET DAY PART ND PADDED WITH LEADING ZERO UPTO LENGTH TWO (01,02 ..)
DATEPART(WK, @StartDateTime) AS 'DAY_OF_WEEK', --GET WEEK NUMBER FOR CURRENT DATE WITHIN YEAR
DATEPART(DD, @StartDateTime) AS 'DAY_OF_MONTH', --GET DAY OF MONTH FOR CURRENT DATE
DATEPART(DY, @StartDateTime) AS 'DAY_OF_YEAR', --GET DAY NUMBER OF THE YEAR BASED ON CURRENT DATE
DATEADD(yy, DATEDIFF(yy, 0, @StartDateTime), 0) AS 'START_OF_YEAR_DATE', --GET START DATE OF THE YEAR
DATEADD(yy, DATEDIFF(yy, 0, @StartDateTime) + 1, - 1) AS 'END_OF_YEAR_DATE', --GET END DATE OF THE YEAR
DATEADD(year, DATEDIFF(year, - 1, @StartDateTime), 0) AS 'NEXT_YEAR_DATE', --GET NEXT START DATE OF YEAR BASED ON CURRENT DATE
DATEADD(DD, - (CHOOSE(DATEPART(dw, @StartDateTime), 1, 2, 3, 4, 5, 6, 7) - 1), @StartDateTime) AS 'START_OF_WEEK_DATE', --ASSUMING WEEK START FROM SUNDAY, GET START DATE OF WEEK BASED ON CURRENT DATE.
DATEADD(DD, 7 - CHOOSE(DATEPART(dw, @StartDateTime), 1, 2, 3, 4, 5, 6, 7), @StartDateTime) AS 'END_OF_WEEK_DATE', --ASSUMING WEEK START FROM SUNDAY, GET END DATE OF WEEK BASED ON CURRENT DATE.
DATEADD(DAY, 1, EOMONTH(@StartDateTime, - 1)) AS 'START_OF_MONTH_DATE', --GET START OF MONTH DATE BASED ON DATE
EOMONTH(@StartDateTime) AS 'END_OF_MONTH_DATE', --GET MONTH END DATE BASED ON DATE
DATEADD(DD, 1, EOMONTH(@StartDateTime)) AS 'NEXT_MONTH_DATE', --GET START OF NEXT MONTH DATE
DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, @StartDateTime), 12, 31)) AS 'DAYS_IN_YEAR', --GET DAYS WITHIN A YEAR (365/366)
DATEPART(DD, EOMONTH(@StartDateTime)) AS 'DAYS_IN_MONTH', --GET DAYS WITHIN A MONTH
CASE WHEN SUBSTRING(CONVERT(char(10), @StartDateTime, 112), 5, 4) = '0229' THEN 'Y' ELSE 'N' END AS 'IS_LEAP_DAY', --CHECK WHETHER CURRENT DATE IS LEAP DAY (29-FEB)
CASE WHEN DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, @StartDateTime), 12, 31)) = 366 THEN 'Y' ELSE 'N' END AS 'IS_LEAP_YEAR' --CHECK WHETHER CURRENT YEAR IS LEAP YEAR.
UNION ALL
SELECT
DATEADD(D, 1, DATE_TIME) AS 'DATE_TIME',
'D' AS 'DATE_TYPE',
DATEADD(D, 1, DATEADD(D, 1, DATE_TIME)) AS 'NEXT_DAY_DATE',
DATEADD(D, - 1, DATEADD(D, 1, DATE_TIME)) AS 'PREV_DAY_DATE',
DATEPART(Q, DATEADD(D, 1, DATE_TIME)) AS 'QUARTER',
DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS 'YEAR',
DATEPART(MM, DATEADD(D, 1, DATE_TIME)) AS 'MONTH',
DATENAME(month, DATEADD(D, 1, DATE_TIME)) AS 'MONTH_NAME',
DATEPART(WK, DATEADD(D, 1, DATE_TIME)) AS 'WEEK',
DATENAME(weekday, DATEADD(D, 1, DATE_TIME)) AS 'WEEK_DAY',
CONVERT(char(10), DATEADD(D, 1, DATE_TIME), 112) AS 'DATE_YYYYMMDD',
RIGHT (REPLICATE('0', 6) + CAST(DATEPART(MM, DATEADD(D, 1, DATE_TIME)) AS VARCHAR) + CAST(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 6) AS 'DATE_MMYYYY',
DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS 'YEAR_YYYY',
SUBSTRING(CAST(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS varchar), 3, 2) AS 'YEAR_YY',
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(mm, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 2) AS 'MONTH_MM',
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(WK, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 2) AS 'WEEK_WW',
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(DD, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 2) AS 'DAY_DD',
DATEPART(WK, DATEADD(D, 1, DATE_TIME)) AS 'DAY_OF_WEEK',
DATEPART(DD, DATEADD(D, 1, DATE_TIME)) AS 'DAY_OF_MONTH',
DATEPART(DY, DATEADD(D, 1, DATE_TIME)) AS 'DAY_OF_YEAR',
DATEADD(yy, DATEDIFF(yy, 0, DATEADD(D, 1, DATE_TIME)), 0) AS 'START_OF_YEAR_DATE',
DATEADD(yy, DATEDIFF(yy, 0, DATEADD(D, 1, DATE_TIME)) + 1, - 1) AS 'END_OF_YEAR_DATE',
DATEADD(year, DATEDIFF(year, - 1, DATEADD(D, 1, DATE_TIME)), 0) AS 'NEXT_YEAR_DATE',
DATEADD(DD, - (CHOOSE(DATEPART(dw, DATEADD(D, 1, DATE_TIME)), 1, 2, 3, 4, 5, 6, 7) - 1), DATEADD(D, 1, DATE_TIME)) AS 'START_OF_WEEK_DATE',
DATEADD(DD, 7 - CHOOSE(DATEPART(dw, DATEADD(D, 1, DATE_TIME)), 1, 2, 3, 4, 5, 6, 7), DATEADD(D, 1, DATE_TIME)) AS 'END_OF_WEEK_DATE',
DATEADD(DAY, 1, EOMONTH(DATEADD(D, 1, DATE_TIME), - 1)) AS 'START_OF_MONTH_DATE', EOMONTH(DATEADD(D, 1, DATE_TIME)) AS 'END_OF_MONTH_DATE',
DATEADD(DD, 1, EOMONTH(DATEADD(D, 1, DATE_TIME))) AS 'NEXT_MONTH_DATE',
DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)), 12, 31)) AS 'DAYS_IN_YEAR',
DATEPART(DD, EOMONTH(DATEADD(D, 1, DATE_TIME))) AS 'DAYS_IN_MONTH',
CASE WHEN SUBSTRING(CONVERT(char(10), DATEADD(D, 1, DATE_TIME), 112), 5, 4) = '0229' THEN 'Y' ELSE 'N' END AS 'IS_LEAP_DAY',
CASE WHEN DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)), 12, 31)) = 366 THEN 'Y' ELSE 'N' END AS 'IS_LEAP_YEAR'
FROM DateRange
WHERE DATE_TIME < @EndDateTime -- **IMPORTANT** DONT CHANGE, IT MIGHT IMPACT AS NEVER ENDING SQL (AS MAXRECURSION IS SET TO 0)
)
SELECT *
FROM DateRange
OPTION (MAXRECURSION 0) --HINT ADDED TO AVOID RECURSION EXCEPTION CAUSE IN RECURSIVE WITH CLAUSE.

Expected Output:

Posted in Helper SQL, SQL Server Learning | Tagged , , , | Leave a comment

#SQLSERVERLearning : Order By Learning.

Most of folks feel , both(Oracle-SQLServer) are traditional RDBMS database and should or need to work some what “SIMILAR”.
But, But when we actually start working on core with queries, development and tuning.We identified a lot of difference between any two database.

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.

We had a requirement To have Custom Order on Varchar data as per numeric part in data.
For instance for below information.

select distinct ',' + QUOTENAME (c.b)
from (select 'p' + '1' as b
union all
select 'p' + '10'
union all
select 'p' + '2') as c
for xml path('') , type ;

Output was ,[p1],[p10],[p2] and our requirement was it should be on basis of numeric part i.e. ,[p1],[p2],[p10].

1. ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

We try to add Custom order ” Order by CAST(SUBSTRING(b,2,len(b)) as INT )” in same query but got a exception.

select distinct ',' + QUOTENAME (c.b)
from (select 'p' + '1' as b
union all
select 'p' + '10'
union all
select 'p' + '2') as c
order by cast (SUBSTRING(b,2,len(b)) as INT )
for xml path('') , type ;

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Whenever we have DISTINCT, we can only order by based on column projected in output and can’t have any other Column or expression as part of order by.

below query will also not work,

select distinct ',' + QUOTENAME (c.b)
from (select 'p' + '1' as b
union all
select 'p' + '10'
union all
select 'p' + '2') as c
order by c.b
for xml path('') , type ;

It is tightly coupled with Expression/Column Projected as per Distinct Expression.

2.The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Then we try to Move Order by inline after Distinct and before XML PATH operation.
select ',' + QUOTENAME (c.b)
from
(select B from
((SELECT DISTINCT C.B
FROM (SELECT 'P' + '1' AS B
UNION ALL
SELECT 'P' + '10' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '3' AS Expr1
UNION ALL
SELECT 'P' + '22' AS Expr1) AS C)) as c order by cast (SUBSTRING(b,2,len(b)) as INT ) ) as c
for xml path('') , type ;

But it give exception and does not allow inline order by.

Exception :
Msg 1033, Level 15, State 1, Line 15
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

So thought to push order by inline by using “TOP 100 PERCENT”, it will cause to fetch all rows and adhere order by constraint.

select ',' + QUOTENAME (c.b)
from
(select TOP 100 PERCENT B from
((SELECT DISTINCT C.B
FROM (SELECT 'P' + '1' AS B
UNION ALL
SELECT 'P' + '10' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '3' AS Expr1
UNION ALL
SELECT 'P' + '22' AS Expr1) AS C)) as c order by cast (SUBSTRING(b,2,len(b)) as INT ) ) as c
for xml path('') , type ;

Output ::
,[P1],[P10],[P2],[P22],[P3]

Though we had order by, but same was completely ignored by SQL SERVER.
Order by is still based on Distinct operation. and provided Order by expression is ignored.

Underlying Execution plan has an explanation for same.Sort operation is ordering based on default expression projected for XML elements.

orderby1

Sorting is due to DISTINCT operation and not due to our custom provided Order by.
So though we have use TOP 100 PERCENT and Custom Ordering, it was completely ignore by SQL Server.

Final Solution :
Provide required ordering in XML scope and let it get applied after Default Distinct Order by.

select ',' + QUOTENAME (c.b)
from
(select B from
((SELECT DISTINCT C.B
FROM (SELECT 'P' + '1' AS B
UNION ALL
SELECT 'P' + '10' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '3' AS Expr1
UNION ALL
SELECT 'P' + '22' AS Expr1) AS C)) as c ) as c
order by cast (SUBSTRING(b,2,len(b)) as INT )
for xml path('') , type ;

Output ::
,[P1],[P2],[P3],[P10],[P22]

Execution Plan :

orderby2

Now we have Two Order by, one implicit as per Distinct and other for our custom ordering.

Posted in SQL Server Learning | Tagged , , , , , | Leave a comment

#SQLSERVERLearning : String Padding in Sql Server (LPAD/RPAD)

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.

Oracle provide LPAD and RPAD function for padding a specific characters as per specified length.
SQL Server directly don’t provide any in-build function to achieve similar functionality.

We will try to understand, on how similar functionality can be implemented in SQL Server.
Using standard function SQL server provide we will try to implement LPAD and RPAD functionality.

1. LEFT and RIGHT
Return varchar from left or right up to length specified.

SELECT LEFT('abcdefg',1) , RIGHT('abcdefg',1);

LEFT RIGHT
---- -----
a         g

2. REPLICATE
Return replication of characters based on number of times specified.

SELECT REPLICATE('AB', 4) "REPLICATE"

REPLICATE
---------
ABABABAB

We will try to recreate Oracle LPAD and RPAD function using UDF in SQL Server.

--Creating LPAD Function
CREATE FUNCTION LPAD(@STR VARCHAR, @PADD_LEN INT , @PAD_STR VARCHAR = ' ')
RETURNS VARCHAR(max)
AS
BEGIN
RETURN RIGHT( REPLICATE(@PAD_STR, @PADD_LEN) + @STR, @PADD_LEN);
END;

--Creating RPAD Function
CREATE FUNCTION RPAD(@STR VARCHAR, @PADD_LEN INT , @PAD_STR VARCHAR = ' ')
RETURNS VARCHAR(max)
AS
BEGIN
RETURN LEFT(@STR + REPLICATE(@PAD_STR, @PADD_LEN) , @PADD_LEN);
END;

Example from newly created function,

SELECT DBO.LPAD('A',2,'_') "LPAD" , DBO.RPAD('A',2,'_') "RPAD"

LPAD RPAD
----- -----
_A A_

We can implement same in SQL Server to get similar LPAD and RPAD functionality as in Oracle.

Posted in Helper SQL, SQL Server Learning | Tagged , , , , , , | Leave a comment

#SQLSERVERLearning : Export Table data as Insert statement in SQL Server.

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.

With Oracle, SQL Developer and Toad console provide an option to export Table data from Result set itself.
With SQL Server, we would need to follow series of steps to export table as Insert statement.

Lets Iterate through steps to Export table data in SQL Server. (Tested on SQL Server 2012)
For demonstration purpose, we would take “AdventureWorks2012” database as sample data-set.

Step 1:Use Generate Scripts Task for exporting table data.
step1_export_table

Step 2:Select Database object to export
We can select multiple object for export as insert scripts from table or view.
step2_export_table

Step 3:Select Option on how to Save Export Insert Script.
step3_export_table

Step 4:Select Export Type for database object.
Click on Advanced option to change default export properties.
By default only Schema creation script can be exported, if data is also need we can select “Schema and Data Option”.
If only Data is needed, we can select only data options.
step4_export_table

Step 5:Export Summary.
Check and Validate option chosen for export, before proceeding further.
step5_export_table

Step 6:Export Execution Status.
step6_export_table

Step 7:Final Export Script.
step7_export_table

Using above steps, we can export complete set of records for a table.
But in case we need to export only specific set of records from a tables, not sure whether we have any option provided by SSMS.

Posted in SQL Server Learning | Tagged , , , | Leave a comment

#SQLSERVERLearning : Column Data Type Conversion in Oracle and SQL Server.

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.

Ideally converting data type after having underlying data on table is tedious.It required special consideration(Index/Change Code/Conversion) to avoid any failure or run-time exceptions.
Once Schema is design , data getting stored in Column need to adhere its type.

Only Number can be stored in NUMBER datatype and so on.
We can say it is a datatype constraint getting enforce for a column, it define type of data we would be storing as strong handshake.

Let start with an example in oracle,
We will create a table with a column as Varchar2 datatype and try to modify it to Number.

DROP TABLE TEST123;

CREATE TABLE TEST123
(COL1 VARCHAR(10) , COL2 INTEGER);

INSERT INTO TEST123 VALUES ('11', 112);
INSERT INTO TEST123 VALUES ('12', 112);
INSERT INTO TEST123 VALUES ('1343', 112);
INSERT INTO TEST123 VALUES ('-1133', 112);

we are storing number in varchar data type.
Also note all data currently in Col1 is also a Legal Number, hence conversion should not be a problem.
We should not get any data type mismatch for same.

ALTER TABLE TAB_TEST123 MODIFY COL1 NUMBER(10,0); -- modify column to be NUMBER

SQL Error: ORA-01439: column to be modified must be empty to change datatype
01439. 00000 - "column to be modified must be empty to change datatype"


Oracle does not allow to typecast column data type to a Number, though all data was valid Number.
For data type modification column should be empty, it should not have any data.

We have many approach for data type modification, but personally prefer adding Virtual column with desired data type.

ALTER TABLE TEST123 ADD (COL1_NUM AS (TO_NUMBER(COL1))); --adding virtual column
Table TEST123 altered.

It will add one more column with data type as NUMBER and whose values is getting source from Col1.

DESC TEST123;

Name Null Type
-------- ---- ------------
COL1           VARCHAR2(10)
COL2          NUMBER(38)
COL1_NUM NUMBER

Using Virtual Column we would not need to change most of logic or index structure and many more constraint.

Let see how we can achieve same in SQL Server.we will be using similar table as created for Oracle.
Let try to convert same to Integer datatype

DROP TABLE TEST123;

CREATE TABLE TEST123
(COL1 VARCHAR(10) , COL2 INTEGER);

INSERT INTO TEST123 VALUES ('11', 112);
INSERT INTO TEST123 VALUES ('12', 112);
INSERT INTO TEST123 VALUES ('1343', 112);
INSERT INTO TEST123 VALUES ('-1133.12', 112); -- Added a Decimal Values

ALTER TABLE TEST123 ALTER COLUMN COL1 INT;

SQL Server will try to modify data type for column and will perform implicit check on column data for exact data type match.
In current data set for value -1133.12, it can’t be cast to int datatype.
It will give below exception.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '-1133.12' to data type int.
The statement has been terminated.

As compare to oracle, SQL server will cast data type of column though it has some existing data.

Conversion to Float will be successful.
ALTER TABLE TEST123 ALTER COLUMN COL1 FLOAT;

TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
------------- ------------------------ ----------
TEST123 COL1 6 float
TEST123 COL2 4 int

Posted in Oracle Internal, SQL Server Learning | Tagged , , , | Leave a comment

Interesting New WITH Clause Feature in Oracle 12c.

#TGIF
In Oracle 12c, WITH clause is capable to have PL-SQL type Function or procedure inline with underlying SQL.
Function or Procedural within WITH clause is scope till SQL Execution lifetime and cease to exist after execution completion.

Having Function as part of WITH clause has it owns advantages, some as below.

1. Inline function within SQL reduces traditional Context Switch between SQL Engine and Pl-SQL Engine.
2. Computational function specific to a SQL can be inline instead of being an object in database Schema.
3. For Read only database, we can have Function inline with SQL.
4. WITH clause help SQL to use programming logic as with PLSQL (Pls. Note := it has some limitations.)
5. We can test a function without creating it as part of database schema.
6. Procedural or Function within WITH clause get priority over objects defined with same name in database schema.

Lets start with some example around New WITH clause.
We will create function as part of WITH clause and use same in underlying SQL.

WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER
IS
BEGIN
RETURN OPERAND1 + 10;
END;
FUNCTION FN_ADD_11(OPERAND1 NUMBER) RETURN NUMBER
IS
BEGIN
RETURN OPERAND1 + 11;
END;
SELECT FN_ADD_10(1) ADD_10,FN_ADD_11(1) ADD_11 FROM DUAL;

ADD_10 ADD_11
———- ———-
11               12

Now lets evaluate different scenario and get deep understanding of WITH Clause.
Demonstrate SQL were not working on my VM SQL Developer version (Version 4.1.1.19), So for demonstrate purpose let use Oracle Live SQL.
Oracle Live SQL :: Nice online console to play around SQL with Oracle latest version.

1. WITH_PLSQL Hint.
If With clause has a function/procedure and it is not the Top level declaration then SQL fails.

with_plsql

Not Sure, why getting Invalid Statement while running same in Oracle Live SQL.please ignore same.

SELECT * FROM ( WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN OPERAND1 + 10; END; SELECT FN_ADD_10(2) FROM DUAL);

SQL will failed with below Error.
ORA-32034: unsupported use of WITH clause

To avoid such error, use WITH_PLSQL hint.
assuming it is some sort of compiler directive to tell oracle, WITH clause is not top level declaration but part of SQL with function.

SELECT /*+ WITH_PLSQL*/* FROM ( WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN OPERAND1 + 10; END; SELECT FN_ADD_10(2) FROM DUAL)


output :
FN_ADD_10(2)
----------
12

2.Priority over function defined in Schema
Creating function with same name “FN_ADD_10”, but subtracting 10 from input variable.

CREATE OR REPLACE FUNCTION FN_ADD_10(
OPERAND1 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN OPERAND1 - 10;
END;

within SQL will use same Name function as part of WITH clause.

SELECT
/*+ WITH_PLSQL*/
ADD_10 ,
FN_ADD_10(2)
FROM
( WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN OPERAND1 + 10;
END;
SELECT FN_ADD_10(2) AS ADD_10 FROM DUAL
)

Function as part of WITH clause is consider over schema level functions.
we can use similar method for any testing before implementing same at schema level.

with_plsql_1

3.Context Switch Performance
As function defined is inline it give advantage over context switch betn SQL and PLSQL engine.

SELECT SYSTIMESTAMP FROM DUAL;
DECLARE
L_CURSOR SYS_REFCURSOR;
TYPE T_TAB IS TABLE OF NUMBER;
L_TAB T_TAB;
BEGIN
DBMS_OUTPUT.PUT_LINE('WITHOUT WITH CLAUSE');
OPEN L_CURSOR FOR 'SELECT FN_ADD_DD(ROWNUM) COL1 FROM DUAL CONNECT BY ROWNUM < 1000000';
FETCH L_CURSOR BULK COLLECT INTO L_TAB;

CLOSE L_CURSOR;
END;
SELECT SYSTIMESTAMP FROM DUAL;
DECLARE
L_CURSOR SYS_REFCURSOR;
TYPE T_TAB IS TABLE OF NUMBER;
L_TAB T_TAB;
BEGIN
DBMS_OUTPUT.PUT_LINE(' WITH CLAUSE');
OPEN L_CURSOR FOR 'WITH FUNCTION FN_ADD_DD(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN (OPERAND1 - TO_NUMBER(TO_CHAR(SYSDATE , ''DD''))); END; SELECT FN_ADD_DD(ROWNUM) COL1 FROM DUAL CONNECT BY ROWNUM < 1000000';
FETCH L_CURSOR BULK COLLECT INTO L_TAB;

CLOSE L_CURSOR;
END;
SELECT SYSTIMESTAMP FROM DUAL;

with_plsql_2

Traditional function took roughly 11 sec and with inline function it took 5 sec.
With advantages, it also has many restriction and limitations.it is always wise to test and test and test before making any permanent changes.

Posted in Oracle 12c New Features | Tagged , , , , , , | Leave a comment