Re: Another SQL Question

[Jeff Young <cooljeff913@xxxxxxxxx>]

Elvis,
The solution provided by Brigitta works fine for my problem.
For my particular problem, I can order the data by the 1st 3 rows without causing any problems with the application.
The end result of the SQL statement is going to be used in a Create Tabel Using to create a temporary table to be used later in the program.

Thanks,

 
Jeff Young 
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3 


  
 





----- Original Message ----
From: Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Friday, May 2, 2008 1:17:08 PM
Subject: RE: Another SQL Question

Jeff, 

Joe and Birgitta got really close, but I don't think either one of their
suggestions does quite what you're asking.
Both proposals assume ordering is to be done over first 3 columns, but in
your desired result set example you obviously want default RRN ordering
(i.e. AP then JE then CS -- no order by whatsoever).

With this assumption, I came up with something like this:

UPDATE myFile A SET A.SEQ_# =                                    
  (SELECT ORDINAL_NUMBER                                          
  FROM                                                            
    (SELECT FISCAL_YEAR,CONTROL_GROUP,SOURCE_CODE,                
          ROW_NUMBER() OVER(ORDER BY FISCAL_YEAR) AS ORDINAL_NUMBER
    FROM (SELECT FISCAL_YEAR,CONTROL_GROUP,SOURCE_CODE            
          FROM myFile A                                        
          GROUP BY FISCAL_YEAR,CONTROL_GROUP,SOURCE_CODE) B) C    
  WHERE A.FISCAL_YEAR  = C.FISCAL_YEAR  AND                    
        A.CONTROL_GROUP = C.CONTROL_GROUP AND                    
        A.SOURCE_CODE  = C.SOURCE_CODE)

Test this first though, as I am not really using any ordering in this
proposal. 
Now, if you added additional requirements where ordering applies... solution
would be different of course.

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Friday, May 02, 2008 11:00 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Another SQL Question

Given the following data:
FISCAL      CONTROL  SOURCE  SEQ
YEAR          GROUP      CODE          #
  2,007                1                AP            0
  2,007                1                AP            0
  2,007                1                AP            0
  2,007                1                JE            0
  2,007                1                JE            0
  2,007                1                JE            0
  2,007                1                CS            0
  2,007                1                CS            0
  2,007                1                CS            0 
  2,007                1                CS            0 
  2,007                1                CS            0 
  2,007                2                JE            0 
  2,007                2                JE            0 
  2,007                2                JE            0 
  2,007                2                AP            0 
  2,007                2                AP            0 
  2,007                2                AP            0 
  2,007                2                AP            0 
  2,007                2              AP            0
          
Is there any method using dynamic SQL (no user defined functions or any
other type of sql program), to set a sequence number so that when Year,
Control Group or Source Code change, the value will be incremented by 1
starting at 1?
I have tried the Row_Number() Over function, but have not been able to get
it to do what I want.

The desired result would be:
FISCAL      CONTROL  SOURCE  SEQ
YEAR          GROUP      CODE          #
  2,007                1                AP            1
  2,007                1                AP            1
  2,007                1                AP            1
  2,007                1                JE            2
  2,007                1                JE            2
  2,007                1                JE            2
  2,007                1                CS            3
  2,007                1                CS            3 
  2,007                1                CS            3 
  2,007                1                CS            3 
  2,007                1                CS            3 
  2,007                2                JE            4 
  2,007                2                JE            4 
  2,007                2                JE            4 
  2,007                2                AP            5 
  2,007                2                AP            5 
  2,007                2                AP            5 
  2,007                2                AP            5 
  2,007                2              AP            5
......

Thanks,

Jeff Young