Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: SQL: need that closing ")"

    Hi, please consider this SQL:
    Code:
    select	varchar
                (   case rownumber() over()
                       when 1 
                          then 'create view CMOD_tablecount as ('
                          else 'union all '
                    end         
                 || 'select	char('
                 || chr(39) 	
    	     || lcase(rtrim(a.table_name))
                 || chr(39)
    	     || ') as tabnam'					 	
    	     || ', count (*) as tabcnt from '	
    	     || lcase(rtrim(a.table_schema))
    	     || '.'					
    	     || lcase(rtrim(a.table_name))
                 ,128 ) as composed_line
    from sysibm.tables   a
    where                a.table_type       = ucase('base table') 
      and  length(rtrim( a.table_name  ))   =  4
      and  substr      ( a.table_name,3,1 ) = ucase('a') 
      and  substr      ( a.table_name,4,1 ) between '1' 
                                                and '9'
    order by             a.table_name
    It is almost finished. I only have to add 1 closing ")" at the end and the generated statement can be executed.

    I know when I am processing the first row (rownumber() over() = 1) but how do I know I am processing the last row of my set? Does anyone know how I can code that?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CASE ROW_NUMBER() OVER(ORDER BY a.table_name ASC )
    WHEN 1
    THEN processing the first row
    WHEN COUNT(*) OVER()
    THEN processing the last row

    or

    CASE
    WHEN ROW_NUMBER() OVER(ORDER BY a.table_name ASC ) = 1
    THEN processing the first row
    WHEN ROW_NUMBER() OVER(ORDER BY a.table_name DESC) = 1
    THEN processing the last row
    Last edited by tonkuma; 04-13-10 at 06:12. Reason: Add "ORDER BY a.table_name ASC" to the first example.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dr_te_z View Post

    I know when I am processing the first row (rownumber() over() = 1)
    That's not true. You are processing one random row of the group. For it to become first (or last) in relation to some ordering criteria, the group must be, well, ordered.

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    GREAT, thanks!
    This works
    Code:
    select	varchar (
                        case rownumber() over(order by a.table_name asc )                    
                           when 1  then 'create view cmod_tablecount as ('
                                   else 'union all '
                        end  
                     || 'select	char('
                     || chr(39) 	
    	         || lcase(rtrim(a.table_name))
                     || chr(39)
    	         || ') as tabnam'					 	
    	         || ', count (*) as tabcnt from '	
    	         || lcase(rtrim(a.table_schema))
    	         || '.'					
    	         || lcase(rtrim(a.table_name))
    	         || case rownumber() over(order by a.table_name desc)
                           when 1  then ');'
                                   else space(1)
                        end
                    ,196
    		    	      )   as view_definition
    from sysibm.tables   a
    where                a.table_type       = ucase('base table') 
      and  length(rtrim( a.table_name  ))   =  4
      and  substr      ( a.table_name,3,1 ) = ucase('a') 
      and  substr      ( a.table_name,4,1 ) between '1' and '9'
    order by             a.table_name asc 
      ;
    Last edited by dr_te_z; 04-13-10 at 09:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •