SQL Tips For Oracle Pivots

Every SQL writer has at times wanted to write a Select statement that presented the data horizontally as a series of columns rather than vertically as a series rows.  Displaying the data as a series of columns rather than roles is called pivoting.  In order to accomplish pivoting the SQL writer had to employ various coding techniques such as unioned Select statements or using the Case statement.  Oracle 11g has given us a new operator called Pivot that allows us to pivot data with a much smaller statement.  This article describes how to use this operator in your statements.

First let's look at some sample data.  Listing 1 portrays a Select statement that displays the number of employees in the various departments.  The statement displays the employee count as a series of rows.  Each row displays a particular department and the employee count.  Notice that an in-line view was needed to compute the employee count and the in-line view result set was joined to the Department table.  This is done because there are some departments without employees and a dense result set is desired.  The new Pivot operator also allows you to generate a dense result set, but in a different manner.

 

select department, nvl(cnt,0) cnt
from department
  left join (select fk_department, count(*) cnt
             from employee
             group by fk_department) on (department = fk_department)

DEPARTMENT     CNT                   
---------------------      ------
WEL                        6                     
INT                          7                     
POL                        8                     
CEN                        0                     
                     

5 rows selected

            
Traditionally data was pivoted using the Decode function, Case statements, or unioned Select statements.  The latter technique is illustrated in Listing 2.  The listing contains an in-line view that has a unioned select statement for each pivoted value (i.e distinct department value).  Each statement consists of an aggregation of the target value (i.e. employees).  The resulting aggregation is displayed in one of the statement's columns.  Each statement contains an expression for each of the pivoted columns, but only the column that pertains to the statement's Where clause value is populated.  The unioned result sets are then summarized by the outer statement creating the pivoted result set.  The main problem with this technique is the need to pre-define each column and select statement.   

select sum(int) int, sum(wel) wel, sum(cen) cen, sum(pol) pol
from
 (select count(*) INT, 0 WEL, 0 CEN, 0 POL
 from employee
 where fk_department = 'INT'
union
 select 0, count(*), 0, 0
 from employee
 where fk_department = 'WEL'
union
 select 0, 0, count(*), 0
 from employee
 where fk_department = 'CEN'
union
 select 0, 0, 0, count(*)
 from employee
 where fk_department = 'POL')

 

INT     WEL   CEN   POL                   
-------- -------- --------- --------
      7       6         0       8                    
  
 

1 rows selected

Oracle 11g has given us the Pivot and Unpivot operators.  These operators help in reducing the amount of code but do not solve the problem most developers would like solved, computing dynamic columns.  Developers have long wanted a statement that dynamically creates the needed columns rather than having to code for each value.  Unfortunately the new operator does not do this either.  However, the operators might help in reducing the amount of code that must be written.  For this reason, its good to look at them.

Below is a template of the Pivot operator along with a description of its parameters.  The Unpivot operator is shown later in this article.

XML                          - This parameter causes the result set to be returned in XML format
Aggregate function     - The aggregate function (i.e. sum, count, min, max)  used to compute the pivot matrix value.
Expression                - The aggregated column
Alias                         -  Aggregate column alias
Column_list               -  The column whose values determine the pivot location
Subquery                  -  The Pivot expression columns

PIVOT [XML] (<aggregate function> (expression) [AS<alias>]
FOR (<column_list>)
IN <subquery>)

Listing 3 depicts the Pivot operator.  The statement produces the same result set as the traditional statement shown in Listing 2.  The statement produces a row that displays a count of the INT, WEL, CEN, and POL department employees.  The statement is placed in an in-line view.  Notice that Fk_department values are placed in the Subquery parameter list.  The values in this list determine the columns in the Pivot table.  The select statement above the Pivot operator actually produces more Fk_department values than are listed in the Subquery parameter list.  The For clause matches the aggregated FK_department values produced by the upper query to the values in the Subquery list.  If a value does not exist in the Subquery list, it is not displayed as a column.
 

select *
from
  (select fk_department
   from employee)
   pivot
    (count(fk_department)
      for fk_department in ('INT', 'WEL', 'CEN', 'POL'));

 

'INT'         'WEL'       'CEN'     'POL'                                                                            
----------    ----------   ----------  ----------                                                                             
        7            6            0          8                                                                            

Finer aggregated matrix values can also be displayed by adding additional columns to the master Select list and not listing them in the Pivot clause.  The additional values that increase the aggregation grain are displayed on the Y-Axis.  This is shown in Listing 4.  The statement displays department wages by current position. The Fk_department values are displayed along the X-Axis as headings.  The current positions are displayed in the first column or Y-Axis.  The sum of wages values are displayed in the matrix area. Notice the statement is essentially the same as Listing 3 except for the inclusion of the Current_positions column.

 

select *
 from
  (select current_position, fk_department, wages
   from employee)
   pivot
   (sum(wages)
     for fk_department in ('INT', 'WEL', 'CEN', 'POL'));
 

CURRENT_POSITIO    'INT'      'WEL'     'CEN'      'POL'                                                            
-----------------------------    ----------   ----------   ----------   ----------                                                            
VICE PRESIDENT         9500       8500                                                                                  
LABORER 3                13000                                                                                  
SALESPERSON 1                                                7500                                                            
JANITOR                       9500                                                                                             
CLERK 2                     14000                                                                                             
TREASURER                                                      12500                                                             
...                                                                          
SYSTEM ANALYST                                                                                                         
TREASURER CLERK                                           12000                                                            
CLERK 1                                                             25400                                                            
MAINT. MAN 2             10000                                                                                  
ADMINISTRATOR                                                   9800                                                             

 

The example statement shown in Listing 5 illustrates that multiple matrix aggregations can be included in the statement.  The Listing 4 statement was modified with the inclusion of a count value.  The statement now displays department wages and employee counts by current position.  A numeric literal value was added to the select clause and a summary function added to the Pivot clause.  The additions are bolded.

Listing 5 - Department wages and employee count by current position
 

SQL> select *
  2  from
  3  (select current_position, fk_department, wages, 1 cnt
  4  from employee)
  5  pivot
  6  (sum(wages) as wages, sum(cnt) as count
  7    for fk_department in ('INT', 'WEL', 'CEN', 'POL'));

 

CURRENT_POSITIO  'INT'_WAGES  'INT'_COUNT  'WEL'_WAGES  'WEL'_COUNT  'CEN'_WAGES  'CEN'_COUNT  'POL'_WAGES  'POL'_COUNT        
-----------------------------   -------------------   ------------------  ----------------------   --------------------   ---------------------   --------------------  ----------------------  --------------------        
VICE PRESIDENT                 9500                    1                8500                     1                                                        
LABORER 3                                                                    13000                      1                                                        
SALESPERSON 1                                                                                                                                                           7500                     1        
JANITOR                               9500                    1                                                                                
CLERK 2                              14000                   1                                                                                 
...                                          
TREASURER CLERK                                                                                                                                                     12000                     1        
CLERK 1                                                                                                                                                                       25400                     2        
MAINT. MAN 2                                                                 10000                      1                                                        
ADMINISTRATOR                                                                                                                                                             9800                    1        

Let's now look at the Unpivot operator.  This operator does the opposite of the Pivot operator.  It takes values displayed on a single row and moves them into a series of rows.  A template of the operator follows as well as a description of the parameters.

Include|Exclude Nulls                 - Determines whether null column values are displayed as a row.  
Column list                                - The matix column
For column list                           - The heading for the X-Axis column values
In column list                             -  The X-Axis columns

UNPIVOT [<INCLUDE | EXCLUDE> NULLS] (<column_list>) FOR (<column_list>)
IN (<column_list>) [AS (<constant_list>)])

The first step is to generate a result set that can be unpivoted.  Listing 6 displays a statement that creates a view using one of the earlier example select statements (Listing 40.  The view creates a pivot table of department wages by current position.

Listing 6 - Department wages by current position view
 

create view pivotTable as
  select *
  from
  (select current_position, fk_department, wages
  from employee)
  pivot
  (sum(wages)
  for fk_department in ('INT' as int, 'WEL' as wel,
   'CEN' as cen, 'POL' as pol));

View created.

Now let's unpivot the result set.  Listing 7 shows a select statement with the unpivot operator.  The statement begins with a Select statement against the view created in Listing 6.  Notice the following:

1.  The "Include Nulls" option was used.  If a department current position did not have wages, the row is included anyway
2.  The parameter values in the column list were column names not literal values that we have seen in other examples.
3.  There are no summary functions used in the Unpivot clause.

Listing 7 - Using the Unpivot operator to create rows

 SQL> select *
  2  from pivotTable
  3  unpivot include nulls
  4  (wages for fk_department in (INT, WEL, CEN, POL));

 

CURRENT_POSITIO             FK_      WAGES                                                                                         
-----------------------------------------  ---------   ----------                                                                                         
VICE PRESIDENT                INT           9500                                                                                         
VICE PRESIDENT                WEL        8500                                                                                         
VICE PRESIDENT                CEN                                                                                                    
VICE PRESIDENT                POL                                                                                                    
LABORER 3                         INT                                                                                                    
LABORER 3                         WEL      13000                                                                                         
LABORER 3                         CEN                                                                                                     
...                                                                                         
TREASURER CLERK            INT                                                                                                    
TREASURER CLERK            WEL                                                                                                    
TREASURER CLERK            CEN                                                                                                    
TREASURER CLERK            POL      12000                                                                                         
CLERK 1                              INT                                                                                                    
CLERK 1                              WEL

Technorati Tags: Oracle

Router Hangs due to Azureus

Mostly every torrent downloader requires to connect to more peers and seeds so it makes changes to windows tcpip config file to more than the maximum number of connections allowed ... some of the possibilities would be ... and i mentioned how to make your Internet stable ..

You have limited your upload speed ?
Install your old version of Azureus and test again. If the problem goes away either update to Azureus CVS (simply replace azuerus2.jar) and retest.
A colleague guessed that maybe Azureus was causing my router to crash?
Quite likely. If the above doesn't fix the issue, try each step below, restart the client and test after each step.

1) Open the UpNP tab and see what the messages are
2) Disable uPnP at client
3) Ensure DHT UDP port is forwarded to the client, not just open the port but forward also.
4) Disable DHT at client (DHT overwhelms some routers with connections)
4) Set your network adaptor and IP in Advnaced Network settings and specify ports

Hiding whole web page & displaying image instead of it during Ajax processing ..

When a WebGrid triggers an AJAX request, there is a delay until the request completes and is displayed on the client. In some cases, the programmer will want to hide or otherwise disable input on the WebGrid until this processing finishes. This article shows how to display an image or other "Loading" message while these requests process.

Additional Information

You can use the WebGrid’s BeforeXmlHttpRequest and AfterXmlHttpResponse client-side events to hide the grid and display a loading message.
Note that these events are only raised when an AJAX request is created by the WebGrid itself, not when processing custom AJAX requests.
This approach only works in NetAdvantage 2006 Volume 1 and later. In previous versions, the WebGrid is non-responsive during the BeforeXmlHttpRequest event.

Step-By-Step Example

The following functions hide the WebGrid and display an image when an AJAX request from the WebGrid starts, and hides the image and displays the WebGrid when the AJAX response finishes processing. In this example, both "grd" and "loadinggif" are the IDs of DIV elements which respectively contain the WebGrid and the loading image.
In JavaScript:

function grid_BeforeXmlHttpRequest(gridName,type)
{
    document.getElementById("grd").style.visibility = 'hidden';
    document.getElementById("loadinggif").style.visibility = 'visible';
}
function UltraWebGrid1_AfterXmlHttpResponseProcessed(gridName)
{
    document.getElementById("grd").style.visibility = 'visible';
    document.getElementById("loadinggif").style.visibility = 'hidden';
}

When both these DIV elements are positioned identically, it appears as though the image replaces the grid for the period it takes to process the AJAX request and response .

Sample Downloads

kb_displayloadingimageduringxmlrequests_cs.zip

Demonstrates replacing a WebGrid with a loading image while processing an AJAX request

Fade in Animation in Asp.net

Animation Using Ajax Toolkit

While the AJAX Control Toolkit is focused primarily on providing great AJAX controls and extenders, it also includes a powerful animation framework which you can use to add awesome visual effects on your pages. This walkthrough describes the basics of using this framework to create declarative animations.

The animations are implemented in JavaScript as an ASP.NET AJAX class hierarchy and can be invoked via JavaScript by calling the Animation's play function. This is very useful when writing to the animation classes from code, but writing complex animations ends up being time consuming and error prone. To make it very easy to use the animation framework without writing any JavaScript, the Toolkit provides the ability to declare animations via XML markup.

Extenders with animation support, like the AnimationExtender, expose various events, like OnClick, that can be associated with a generic XML animation declaration. Within this declaration, you specify which types of animation effects you would like to occur. For example, to make a Panel with ID = "MyPanel" disappear from the page when clicked, you could add an AnimationExtender like this:

<ajaxToolkit:AnimationExtender id="MyExtender"
  runat="server" TargetControlID="MyPanel">
  <Animations>
    <OnClick>
      <FadeOut Duration=".5" Fps="20" />
    </OnClick>
  </Animations>
</ajaxToolkit:AnimationExtender>

Let's take a closer look at exactly what's going on in the XML above. The AnimationExtender is used to indicate that we want to make our animation target the control with ID = "MyPanel". The Animations section is where all generic animations are declared. OnClick indicates that when the target is clicked we will play the animation nested inside it. Finally, FadeOut defines the specific animation that will be played and sets the values of its Duration and Fps properties.

That's what this simple code does animation does ...

U can try implementing Ajax toolkit in ur Asp.net Application for much more pretty animation ...

Hack a Windows 2000 system through IPC$

I got this nice article from one the site to hack a win 2000 system through IPC$...

The initial step involve in this are ...

1 : Scanning for open Win2k systems
2: Connecting to the IPC$
3: Connecting and using Computer Management.
4. Disable NTLM
5: Starting the Telnet service
6: Creating user accounts and adding them to a group
7: Covering your tracks
8: How to protect your Win2k system from this attack

-----------------------------------------------------------------------------------------------------
You need to be running a Win2k system:

Superscan version 3.00 by Foundstone (246kb). Homepage
NetBrute Scanner 1.0.0.7 (247KB). Homepage
PQWak V1.0 (24KB)
----------------------------------------------------------------------------------------------------

1: Scanning for open Win2k systems

A. Open SuperScan 2.05 (Port scanner)
B. Select a IP range
C. Check "Only scan responsive pings" and "All selected ports in list"
D. Only scan ports 139 (NetBIOS), and 1025 (Network Blackjack)
E. When a system with both Netbios and BlackJack is found, open NetBrute, and scan that IP to see if there is an IPC$

 

2: Connecting to the IPC$

A. Open a DOS window
B. Type in " net use \\ipaddress\ipc$ "" /user:administrator "
C. If you connect to the system, it will say, " The command was completed successfully "
D. If it says, “bad username or password”, Try running PQWak.exe to crack the share name password. Then insert the password like so:
net use \\ipaddress\ipc$ "password" /user:administrator
E. Users usually have only one password for everything. So try the c$ share pass as the administrator password to connect to the IPC$

3: Connecting using Computer Management

A. Open Computer Management.
B. Click “Action”, then “Connect to Another Computer”
C. Type in the IP address.

4. Disable NTLM

A. Open “regedit”
B. Connect to the following registry key:
HKEY_LOCAL_MACHINE--Software--Microsoft--Telnet Server--1.0—->NTLM
C. Set the value data from (2) to (1)
D. That will enable login to the telnet server without being connected to the IPC$ or a trusted domain.

5. Starting the Telnet service

A. In Computer Management, click “Services and Applications”
B. Click Services
C. Right click on the Telnet Service and open Properties.
D. Set the service to Automatic, and start the service.

6: Creating user accounts and adding them to a group

A. Open a dos window, and type the following: telnet IPaddress
B. If prompted to type a username and password, type Administrator with no password.
C. To create a user account, type the following: Net user username password /add
D. Replace “Username” and “password” with whatever you like.
E. To add a user account to a domain, type the following: Net localgroup administrators username /add Or Net group administrators username /add

7: Covering your tracks

A. Open a dos window, and type the following: Net use \\ipaddress\ipc$ /delete
B. While logged on to Computer Management. Check if the Security Logs are being audited in Event Viewer. If they are, clear them. :-)

8: How to protect your Win2k system from this attack

A. Open Regedit
B. Connect to the following:
C. HKEY_LOCAL_MACHINE--System--CurrentControlSet--Control--Lsa-->restrictanonymous
D. Change the "Value Data" from 0 to 1. It should say 0x00000001(1)
E. That will disable remote logon to a null IPC$
F. Always have a complicated administrator password with Windows2000 or any other OS
G. Install a firewall. www.zonealarm.com

Parmalink