RE: SQL

Note that this requires V5R2 because of the identity column but you should be 
able to put your distinctified version into a temp table and use the RRN 
function to get the relative record number which should be the same thing in 
this case. This would change the final SQL to:

SELECT ' ', RRN(QTEMP.TEMPFILE), FIELD1 FROM QTEMP.TEMPFILE

Matt

-----Original Message-----
From: chris beck [mailto:cbeck@xxxxxxxxxxxx]
Sent: Friday, March 19, 2004 3:05 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL


Rob, 

I think this will do what you want, I was unable to figure out how to do it in 
one statement.

CREATE TABLE QTEMP.TEMPFILE (ID  INTEGER GENERATED ALWAYS AS IDENTITY , 
                              FIELD1 CHAR(10)  NOT NULL WITH DEFAULT)
                                                 
INSERT INTO QTEMP.TEMPFILE (FIELD1) SELECT DISTINCT(FIELD1) FROM 
SOMELIB.SOMEFILE 

SELECT ' ', ID, FIELD1 FROM QTEMP.TEMPFILE
UNION
SELECT 'Total', COUNT(ID) AS ID, ' ' FROM QTEMP.TEMPFILE
ORDER BY ID

Chris Beck                                                                      
                



>>> SimaFrog@xxxxxxxxxx 03/19/04 07:00AM >>>
Matt, The select count gives you a total. What I need is the details and the 
totals on the same output.

1. ABC
2. DEF

Total 2

My main Select is a Select Distinct so I can remove the dups.
Thank you,
Rob400
> -----Original Message-----
> From: Haas  Matt [mailto:Matt.Haas@xxxxxxxxxxx] 
> Sent: Thursday, March 18, 2004, 1:33 PM
> To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
> Subject: RE: SQL
> 
> Rob,
> 
> Are you trying to get a count of the number of rows? If so you use a query
> such as:
> 
> select count(*) from myfile where somecondition=true
> 
> If you're trying to add the values from a particular column (to get a total),
> you'd do something like:
> 
> select sum(number_field) from myfile where somecondition=true
> 
> If you want these for the entire file, just leave off the where clause.
> 
> I don't know that I'd recommend any of the books in the Info Center for
> learning SQL but there are several good books on the subject and you should be
> able to find them at a book store that carries computer books (or Amazon.com).
> 
> Matt
> 
> -----Original Message-----
> From: simafrog [mailto:SimaFrog@xxxxxxxxxx] 
> Sent: Thursday, March 18, 2004 4:15 PM
> To: RPG programming on the AS400 / iSeries
> Subject: SQL
> 
> 
> HI I am not sure if this is the right list for an SQL question, as I don't see
> any specific SQL List. 
> I would like to know how to add a column for each record simply as a
> sequential number and then getting a total at the end (count). I do not see
> this anywhere on my STRSQL on the AS/400. If this is the wrong list, please
> re-direct me to the proper one.
> Thanks,
> Rob
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
> or email: RPG400-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
> 
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
> or email: RPG400-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l 
or email: RPG400-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.