Re: Another SQL Question

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