RE: Another SQL Question

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