SQL to connect course doc records with items?

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

Description:
Is there any SQL that will connect course library doc records with the items that are linked to the courses? For version 19+?

I had a query that used to do this in version 18, but it doesn't seem to work anymore, and I can't figure out quite why. It used Z103 to link to the ADM, then further limited items based on the reserves collection and item status.

Resolution:
You say "SQL that will connect course library doc records with the items that are linked to the courses".

But, in fact, items are not linked to the courses directly. They are linked to doc records and doc records are linked to courses.

The link from the doc record to the course is the xxx30 z11 CNO and CNO-S index entries.

The link from the item to the doc record is via the z103 records:

Case 1. The xxx50 item is linked to the xxx30 doc via an xxx01 record: there's an "SID L $$axxx01 ..." field in the xxx30 doc record.

Case 2. The xxx50 item is linked directly to the xxx30: there's an "SID L $$axxx30 ..." field in the xxx30 doc record (linking it to the xxx30 "Connection record").


Case 1:

Reading doc : 000000206
...
CNO L $$a216^^^^^^^^^^^^^^^^^0001
SID L $$aUSM01-000029238-216


util f/3/12:

ENTER HOME LIBRARY : usm30
ENTER DOC-NUMBER : 000000206
USM50 000029238 ITM


Reading usm50 doc : 000029238
FMT L AD
...
LKR L $$aADM$$lUSM01$$b000029238


Case 2:

Reading usm30 doc : 000000201
...
PST L $$0Z30$$1000238856

Reading doc : 000000202
LDR L 00565nam--22001817a-4500
CAT L $$aMASTER$$b99$$c20080409$$lUSM30$$h1129
CNO L $$a212^^^^^^^^^^^^^^^^^0001
SID L $$aUSM30-000000201-212


util f/3/12:

ENTER HOME LIBRARY : usm30
ENTER DOC-NUMBER : 201
USM50 000238856 ADM <------

ENTER HOME LIBRARY : USM30
ENTER DOC-NUMBER : 202
USM50 000238856 ITM <------


Reading usm50 doc : 000238856
Load: /tmp/utf_files/exlibris/aleph/a19_4/usm50/tab/tab_expand
FMT L AD
...
LKR L $$aADM$$lUSM30$$b000000201