Querying Z00-DATA with SQL
- Article Type: General
- Product: Aleph
- Product Version: 20
Description:
I'd like to get a list of items which have in their parent bib record a specific field value .
Since the bib field is only in Z00_DATA (field 937$b), I cannot use LIKE %...%.
Is there a "LIKE" equivalent for LONG data type? Or another way to query parts of Z00_DATA?
SELECT Z30.Z30_REC_KEY FROM ABC50.Z30,ABC50.Z103,ABC01.Z00
WHERE Z30_ITEM_PROCESS_STATUS = 'MI'
-----> AND Z00_DATA LIKE %$$b0820% <-----
AND ABC01.Z00.Z00_DOC_NUMBER = substr (UTR50.Z103.Z103_REC_KEY_1,6,9)
AND substr (ABC50.Z103.Z103_REC_KEY_1,1,5) = 'ABC01'
AND substr (ABC50.Z103.Z103_REC_KEY,6,9) = substr (ABC50.Z30.Z30_REC_KEY,1,9);
Resolution:
There is no "LIKE" equivalent for LONG data type; "select ... where" is not valid. This is discussed in KB 8192-3802. There is no way to query parts of Z00_DATA.
If the field you were looking for were in the z13 record, that could be used, but since it is not and since I see that you have the z00r, you should use the z00r instead. This is exactly why the option of generating the z00r was added.
KB's 16384-14801 and 3930 have examples of SQL queries going against the z00r.
Additional Information
z00_data
- Article last edited: 10/8/2013