START WITH and CONNECT BY in Oracle SQL

One way to store hierarchical data is to create a self referencing table. It is called self referencing because the foreign key (column parent_id) references the same table as the table in which the foreign key is:

create table tblActivity (

  id         number primary key, 

 parent_id  references tblActivity, -- references itself

  descr      varchar2(20),

 

);

This table is now filled with some values:

-- 1st level:

insert into tblActivity values ( 1, null, 'Yahoo'         , 1);

-- 2nd level:

insert into tblActivity values ( 2,    1, 'Entertainment' , 1);

insert into tblActivity values ( 3,    1, 'Science'       , 1);

insert into tblActivity values ( 4,    1, 'Social Science', 1);

-- 3rd level (below Entertainment)

insert into tblActivity values ( 5,    2, 'Awards'        , 1);

insert into tblActivity values ( 6,    2, 'Comedy'        , 1);

insert into tblActivity values ( 7,    2, 'Humor'         , 0);

insert into tblActivity values ( 8,    2, 'Magic'         , 1);

-- 4th level (below Humor)

insert into tblActivity values (19,    7, 'Jokes'         , 1);

insert into tblActivity values (20,    7, 'Advice'        , 1);

insert into tblActivity values (21,    7, 'Parody'        , 1);

-- 4th level (below Magic)

insert into tblActivity values (16,    8, 'Card Tricks'   , 1);

insert into tblActivity values (17,    8, 'Magazines'     , 1);

insert into tblActivity values (18,    8, 'Organizations' , 1);

-- 3rd level (below Science)

insert into tblActivity values ( 9,    3, 'Astronomy'     , 1);

insert into tblActivity values (10,    3, 'Biology'       , 1);

insert into tblActivity values (11,    3, 'Geography'     , 1);

insert into tblActivity values (12,    3, 'Physics'       , 1);

insert into tblActivity values (13,    3, 'Research'      , 1);

-- 4th level (below Astronomy)

insert into tblActivity values (22,    9, 'Galaxies'     , 1);

insert into tblActivity values (23,    9, 'Pictures'     , 1);

insert into tblActivity values (24,    9, 'Stars'        , 1);

-- 4th level (below Physics)

insert into tblActivity values (25,   12, 'Electricity'  , 1);

insert into tblActivity values (26,   12, 'Mechanics'    , 1);

-- 3rd level (below Social Science)

insert into tblActivity values (14,    4, 'Psychology'   , 1);

insert into tblActivity values (15,    4, 'Sociology'    , 1);

-- 4th level (below Psychology)

insert into tblActivity values (27,   14, 'Conferences'  , 1);

insert into tblActivity values (28,   14, 'Intelligence' , 1);

--Now Select 

select * from   tblActivity 

start with id = 3
connect by prior id = parent_id; 

It return the below mention record

Science
    Astronomy
      Galaxies
      Pictures
      Stars
    Biology
    Geography
    Physics
      Electricity
      Mechanics
    Research

Asp.net Datalist item fetches using javascript‏

string scr = @"<script>
function Done()
{

var Hdnval;
var HdnControl;
var foo = document.getElementById('dtlImages');
var inps = foo.getElementsByTagName('input');
var inpsmage = foo.getElementsByTagName('img');
for(var i=0;i<inps.length;i++){
if(inps[i].type=='checkbox')
{
if (inps[i].checked== true)
{
HdnControl = inpsmage[i];
Hdnval = inpsmage[i].src;
}
}}
var fName=Hdnval;
var ret= new Array(fName,HdnControl);
//SetPreviewElements(HdnControl,HdnControl)
window.opener.update(ret);
window.close();
}
</script>;";
ClientScript.RegisterClientScriptBlock(this.GetType(), "done", scr);

Dynamic SQL Query with Sql Paging‏

Create PROC DBO.Article_SelectByCriteria_BySqlPaging

-- Parameters of Article_SelectByCriteria-------------- 
  @P_GUID                     VARCHAR(40)='' , -- OPTIONAL  
  @P_parentArticleId          INT = 0 ,  --OPTIONAL  
  @P_postingURL               nVARCHAR(200) = '' , --OPTIONAL 
  @P_authorId                 INT  = 0, --OPTIONAL 
  @P_elaphObjectId            INT =0,  --OPTIONAL 
  @P_assignedBy     BIGINT=0, --OPTIONAL 
  @P_assignedTo               BIGINT= 0 , --OPTIONAL 
  @P_assignedDate             VARCHAR(50) = '12:00:00 AM' , --OPTIONAL      
  @P_publishedBy              BIGINT = 0, -- OPTIONAL  
  @P_publishedDate            VARCHAR(50) = '12:00:00 AM' ,-- OPTIONAL  
  @P_lastModifiedBY           BIGINT = 0 , -- OPTIONAL 
  @P_lastModifiedDate        VARCHAR(50) = '12:00:00 AM' , --OPTIONAL 
  @P_archivedBy              BIGINT = 0, --OPTIONAL 
  @P_archivedDate            VARCHAR(50) = '12:00:00 AM' , --OPTIONAL 
  @P_expirationDate          VARCHAR(50) = '12:00:00 AM' , --OPTIONAL 
  @P_CountryId               TINYINT  = 0 , --OPTIONAL 
  @P_IsSectionPageNewsTicker BIT = 0 , --OPTIONAL 
  @P_isHomePageNewsTicker    BIT = 0 , --OPTIONAL 
  @P_isPrinterFriendly       BIT = 0 , --OPTIONAL 
  @P_isSendToFriend          BIT = 0 , --OPTIONAL 
  @P_isAddToFavorite         BIT = 0, --OPTIONAL 
  @P_isWriteToAuthor         BIT = 0 , --OPTIONAL 
  @P_isSubscribedArticle     BIT = 0 , --OPTIONAL 
  @P_isDisablePrintCutPaste  BIT = 0, --OPTIONAL 
  @P_IsCopyRight             BIT = 0 , --OPTIONAL  
  @P_IsArticleRating         BIT = 0 , --OPTIONAL 
  @P_IsComments              BIT = 0 , --OPTIONAL 
  @P_articleStatusId         INT = 0, --OPTIONAL 
  @P_articleTypeId           TINYINT = 0 ,--OPTIONAL 
  @P_createdBy               BIGINT  = 0 , --OPTIONAL 


@PageSize int, @PageNumber int

WITH RECOMPILE 

AS
SET NOCOUNT ON
  DECLARE
@STMT nvarchar(max),    -- SQL statement to execute
@ubound int

Declare @RowStart int
Declare @RowEnd int


if @PageNumber > 0
Begin

SET @PageNumber = @PageNumber -1

SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;
 --IF @start < 1 SET @start = 1
  --IF @maxct < 1 SET @maxct = 1
 -- SET @ubound = @start + @maxct


DECLARE @L_WhereClause VARCHAR(8000) 
DECLARE @L_SQL Varchar(8000) 
SET @L_WhereClause = '' 
SET @L_SQL = '' 


 
 If @P_parentArticleId <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ParentArticleId =' +  CAST(@P_parentArticleId AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ParentArticleId =' +   CAST(@P_parentArticleId AS VARCHAR(20))   
            END 
    
    IF @P_GUID  <> '' 
             BEGIN  
    IF  @L_WhereClause <> ''  
                     SET  @L_WhereClause = @L_WhereClause + ' AND Article.GUID =' + ''''+  @P_GUID + '''' 
                  ELSE 
                     SET  @L_WhereClause = @L_WhereClause + ' Article.GUID =' + ''''+  @P_GUID + '''' 
      END 
    
    
    IF @P_postingURL  <> '' 
             BEGIN  
    IF  @L_WhereClause <> ''  
                     SET  @L_WhereClause = @L_WhereClause + ' AND Article.PostingURL =' + ''''+  @P_postingURL + '''' 
                  ELSE 
                     SET  @L_WhereClause = @L_WhereClause + ' Article.PostingURL =' + ''''+  @P_postingURL + '''' 
      END 
    
    If @P_authorId <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.AuthorId =' +  CAST(@P_authorId AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.AuthorId =' +   CAST(@P_authorId AS VARCHAR(20))   
            END 
      If @P_elaphObjectId <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ElaphObjectId =' +  CAST(@P_elaphObjectId AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ElaphObjectId =' +   CAST(@P_elaphObjectId AS VARCHAR(20))   
            END 
  If @P_assignedBy <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.AssignedBy =' +  CAST(@P_assignedBy AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.AssignedBy =' +   CAST(@P_assignedBy AS VARCHAR(20))   
            END 
  If @P_assignedTo <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.AssignedTo =' +  CAST(@P_assignedTo AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.AssignedTo =' +   CAST(@P_assignedTo AS VARCHAR(20))   
            END 
 
       If @P_assignedDate <> '12:00:00 AM'   
              
      BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.AssignedDate =' + '''' + @P_assignedDate  + '''' 
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.AssignedDate =' +   '''' + @P_assignedDate + '''' 
            END 
 
        If @P_publishedBy <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.PublishedBy =' +  CAST(@P_publishedBy AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.PublishedBy =' +   CAST(@P_publishedBy AS VARCHAR(20))   
            END 
     -- If @P_publishedDate <> '12:00:00 AM'   
              
  --    BEGIN  
           --      IF  @L_WhereClause <> ''  
 
  --      SET  @L_WhereClause = @L_WhereClause + ' AND Article.PublishedDate =' +  '''' + @P_publishedDate + '''' 
               --  ELSE 
        --       SET  @L_WhereClause = @L_WhereClause + ' Article.PublishedDate =' +   '''' + @P_publishedDate + '''' 
          --  END 
              
  If @P_lastModifiedBY <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.LastModifiedBY =' +  CAST(@P_lastModifiedBY AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.LastModifiedBY =' +   CAST(@P_lastModifiedBY AS VARCHAR(20))   
            END   
         If @P_lastModifiedDate <> '12:00:00 AM'   
              
      BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.LastModifiedDate =' + '''' + @P_lastModifiedDate + '''' 
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.LastModifiedDate =' +   '''' + @P_lastModifiedDate + '''' 
            END      
      If @P_archivedBy <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ArchivedBy =' +  CAST(@P_archivedBy AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ArchivedBy =' +   CAST(@P_archivedBy AS VARCHAR(20))   
            END      
  
          If @P_archivedDate <> '12:00:00 AM'   
              
      BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ArchivedDate =' +  '''' + @P_archivedDate + '''' 
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ArchivedDate =' +   '''' + @P_archivedDate + '''' 
            END  
  
         If @P_expirationDate <> '12:00:00 AM'   
          
      BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ExpirationDate =' +  '''' + @P_expirationDate + '''' 
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ExpirationDate =' +   '''' + @P_expirationDate + '''' 
            END  
  
        If @P_CountryId <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.CountryId =' +  CAST(@P_CountryId AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.CountryId =' +   CAST(@P_CountryId AS VARCHAR(20))   
            END  
   
        If @P_IsSectionPageNewsTicker <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsSectionPageNewsTicker =' +  CAST(@P_IsSectionPageNewsTicker AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsSectionPageNewsTicker =' +   CAST(@P_IsSectionPageNewsTicker AS VARCHAR(20))   
            END  
 
        If @P_isHomePageNewsTicker <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsHomePageNewsTicker =' +  CAST(@P_isHomePageNewsTicker AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsHomePageNewsTicker =' +   CAST(@P_isHomePageNewsTicker AS VARCHAR(20))   
            END 
 
      IF @P_isPrinterFriendly <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.isPrinterFriendly =' +  CAST(@P_isPrinterFriendly AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.isPrinterFriendly =' +   CAST(@P_isPrinterFriendly AS VARCHAR(20))   
            END 
   
  IF @P_isSendToFriend <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.isSendToFriend =' +  CAST(@P_isSendToFriend AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.isSendToFriend =' +   CAST(@P_isSendToFriend AS VARCHAR(20))   
            END 
   
         IF @P_isAddToFavorite <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsAddToFavorite =' +  CAST(@P_isAddToFavorite AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsAddToFavorite =' +   CAST(@P_isAddToFavorite AS VARCHAR(20))   
            END 
   IF @P_isWriteToAuthor <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsWriteToAuthor =' +  CAST(@P_isWriteToAuthor AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsWriteToAuthor =' +   CAST(@P_isWriteToAuthor AS VARCHAR(20))   
            END 
   IF @P_isSubscribedArticle <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsSubscribedArticle =' +  CAST(@P_isSubscribedArticle AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsSubscribedArticle =' +   CAST(@P_isSubscribedArticle AS VARCHAR(20))   
            END 
   
  IF @P_isDisablePrintCutPaste <> 0 
              
     BEGIN   
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsDisablePrintCutPaste =' +  CAST(@P_isDisablePrintCutPaste AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsDisablePrintCutPaste =' +   CAST(@P_isDisablePrintCutPaste AS VARCHAR(20))   
            END 
  IF @P_IsCopyRight <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsCopyRight =' +  CAST(@P_IsCopyRight AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsCopyRight =' +   CAST(@P_IsCopyRight AS VARCHAR(20))   
            END 
  IF @P_IsArticleRating <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsArticleRating =' +  CAST(@P_IsArticleRating AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsArticleRating =' +   CAST(@P_IsArticleRating AS VARCHAR(20))   
            END 
  IF @P_IsComments <> 0 
              
     BEGIN  
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.IsComments =' +  CAST(@P_IsComments AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.IsComments =' +   CAST(@P_IsComments AS VARCHAR(20))   
            END 
       
  
        
 
          IF @P_articleStatusId <> 0 
              
     BEGIN  
                 IF @P_articleStatusId = 1 
                    BEGIN 
               IF  @L_WhereClause <> ''  
    
          SET  @L_WhereClause = @L_WhereClause + ' AND Article.ArticleStatusId =' +  CAST(@P_articleStatusId AS VARCHAR(20)) + '  AND Article.PublishedDate <= ' +  '''' + CAST(getdate() AS VARCHAR(50)) + '''' +  '  AND Article.expirationDate > ' + '''' +
CAST(getdate() AS VARCHAR(50)) + ''''  
                    ELSE 
                 SET  @L_WhereClause = @L_WhereClause + ' Article.ArticleStatusId =' +   CAST(@P_articleStatusId AS VARCHAR(20))  + '  AND Article.PublishedDate <= ' +  '''' + CAST(getdate() AS VARCHAR(50)) + '''' +  '  AND Article.expirationDate > ' + '''' + CAST(getdate() AS VARCHAR(50)) + ''''
               
                    END 
     ELSE 
     BEGIN 
    IF  @L_WhereClause <> ''  
  
         SET  @L_WhereClause = @L_WhereClause + ' AND Article.ArticleStatusId =' +  CAST(@P_articleStatusId AS VARCHAR(20))  
                  ELSE 
                SET  @L_WhereClause = @L_WhereClause + ' Article.ArticleStatusId =' +   CAST(@P_articleStatusId AS VARCHAR(20))   
             END  
    END 
                 
  
 
 IF @P_articleTypeId <> 0 
              
     BEGIN   
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.ArticleTypeId =' +  CAST(@P_articleTypeId AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.ArticleTypeId =' +   CAST(@P_articleTypeId AS VARCHAR(20))   
            END  
 
      IF @P_createdBy <> 0 
              
     BEGIN   
                 IF  @L_WhereClause <> ''  
 
        SET  @L_WhereClause = @L_WhereClause + ' AND Article.CreatedBy =' +  CAST(@P_createdBy AS VARCHAR(20))  
                 ELSE 
               SET  @L_WhereClause = @L_WhereClause + ' Article.CreatedBy =' +   CAST(@P_createdBy AS VARCHAR(20))   
            END  
   
      IF  @L_WhereClause = ''  
 
        SET  @L_WhereClause = 'where  row >= ' + CONVERT(varchar(9), @RowStart ) + '  AND
                row <=  ' + CONVERT(varchar(9),@RowEnd)
  
 




SET @STMT = ' SELECT TOP 500 *
                FROM (
                      SELECT  ROW_NUMBER() OVER(ORDER BY PublishedDate Desc) AS row, ArticleId, PublishedDate
                      FROM    Article
                     ) AS tbl
                '+@L_WhereClause+' '
               
  EXEC (@STMT)
 
 
 end

 
GO

//// Now You can execute the SP
Article_SelectByCriteria_BySqlPaging
 @PageSize = 15,
 @PageNumber = 11