• Article Type: General
• Product: Aleph
• Product Version: 20

Description:
How can the SQL FIRST and LAST functions be used?

Resolution:
[From Mike Rogers, U. Tennessee - Knoxville, on the Global Aleph Users list;]

I thought I'd pass along this tidbit to those of you who use SQL in Aleph. It is the FIRST/LAST function and it can be used in a variety of ways. The use I found for it was in reporting, if you only want to display the first and last items of a data set.

For example, we started an inventory project in Aleph using the "Inventory Marking" tool in the Cataloging module. Forty-six books were inventoried on the first test of our Juvenile fiction collection. Our staff wanted a report listing the call number range of the books that had been inventoried for a particular day or range of dates, along with a total count of books inventoried for each day.

I knew that items scanned by Inventory Marking received an updated date in Z30_ON_SHELF_DATE, but couldn't figure out the rest of the SQL syntax. I started messing around with FIRST/LAST and then hit on this SQL query which gives us exactly what we need:


COLUMN TO_CHAR(TO_DATE(Z30_ON_SHELF_DATE,'YYYYMMDD'),'YYYY/MM/DD') HEA 'InvDate'
COLUMN MIN(SUBSTR(Z30_CALL_NO,4,25))KEEP(DENSE_RANKFIRSTORDERBY(Z30_CALL_NO_KEY)) FOR A23 HEA 'FromCallNo'
COLUMN MAX(SUBSTR(Z30_CALL_NO,4,25))KEEP(DENSE_RANKLASTORDERBY(Z30_CALL_NO_KEY)) FOR A23 HEA 'ToCallNo'
COLUMN COUNT(*) HEA 'ItemCount'

SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */
TO_CHAR(TO_DATE(Z30_ON_SHELF_DATE,'YYYYMMDD'),'YYYY/MM/DD'),
MIN(SUBSTR(Z30_CALL_NO,4,25)) KEEP (DENSE_RANK FIRST ORDER BY (Z30_CALL_NO_KEY)),
MAX(SUBSTR(Z30_CALL_NO,4,25)) KEEP (DENSE_RANK LAST ORDER BY (Z30_CALL_NO_KEY)),
COUNT(*)
FROM UTK50.Z30
WHERE Z30_COLLECTION = 'JUV'
AND Z30_ON_SHELF_DATE = '20120215'
GROUP BY TO_CHAR(TO_DATE(Z30_ON_SHELF_DATE,'YYYYMMDD'),'YYYY/MM/DD');


The output from the query looks like this:

InvDate FromCallNo ToCallNo ItemCount
---------- ----------------------- ----------------------- ----------
2012/02/15 FA129fi FA291fo 46

So on February 15th, the first call number scanned was FA129fi and the last call number scanned was FA291fo. ItemCount gives the total number of items scanned.

I can potentially see some other uses for this function, too.

• Article last edited: 10/8/2013