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

Desired Outcome Goal:
What SQL query can pull transactions mirroring cash-09 -- including 04 id type from z308 (Patron's ID)?

Procedure:
Step 1: Running this SQL query should give the same results as running cash-09:
COLUMN TransfDt FORMAT 99999999
COLUMN SystemID FORMAT A12
COLUMN 04ID FORMAT A12
COLUMN Type FORMAT A6
COLUMN Amount FORMAT $9999D99
COLUMN Status FORMAT A6
SELECT
z31.Z31_TRANSFER_DATE "TransfDt",
substr( z31.Z31_REC_KEY, 1, 12) "SystemID",
decode(substr( z308.z308_rec_key, 1, 2), '04', substr( z308.z308_rec_key, 3, 12), '') "04ID",
decode( z31.Z31_CREDIT_DEBIT, 'C', 'Credit', 'Debit') "Type",
(z31.Z31_SUM/100) "Amount",
Z31_STATUS "Status"
FROM zzz50.z31 Z31 left outer join zzz50.z308 Z308
on substr( z31.Z31_REC_KEY, 1, 12) = z308.Z308_ID
WHERE substr( z31.Z31_REC_KEY, 1, 12) = z308.Z308_ID
AND z31.Z31_TRANSFER_DATE >= '0'
AND z31.Z31_TRANSFER_DATE <= '0'
AND z31.Z31_SUM > '00000000000000'
GROUP BY
z31.Z31_TRANSFER_DATE,
substr( z31.Z31_REC_KEY, 1, 12),
substr(z308.z308_rec_key, 1, 2),
z31.Z31_CREDIT_DEBIT,
z31.Z31_SUM,
substr( z308.z308_rec_key, 3, 12),
Z31_STATUS;