RE: Another SQL Question

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

On the second look, my update can be simplified a bit:

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 myFile
     GROUP BY FISCAL_YEAR,CONTROL_GROUP,SOURCE_CODE) B
   WHERE A.FISCAL_YEAR   = B.FISCAL_YEAR   AND                     
         A.CONTROL_GROUP = B.CONTROL_GROUP AND                     
         A.SOURCE_CODE   = B.SOURCE_CODE)

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 Elvis Budimlic
Sent: Friday, May 02, 2008 12:17 PM
To: 'Midrange Systems Technical Discussion'
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