People have asked me 'how' I wrote
Seeing the Psalter. There is a brief 4-pager
here. I could write a book on it. But one part I have never shared is that for almost every panel in the screens in the brief 4-pager, I wrote a lot of code like the stuff below. Hundreds of bits of it actually over a period of 7 years - or this is what it looks like when one of these snippets is fully expanded by the
GX-LEAF software that I used to help me.
GX-LEAF helped in so many ways as I felt my way into the Hebrew of the Psalms and decided what direction I should take with this very complex bit of poetic history. It held my data one character at a time. It let me draw and explore diagrams of the psalms as I learned them. It allowed queries and graphs to experiment with the data I had entered. And it provided the development environment for the panels that would let me control the translation process with queries and updates such as the snippet below.
This is an example of the code that creates a cross-tab from the database given a number of user-defined search parameters. I designed it to allow up to 40 columns. Of course there may not be that many columns so each column header is conditioned by a separate query to determine whether it will let itself be visible or not. Altogether, one does not want to read this unless necessary. But there you have a bit of the background to the tables that you may have seen in other posts like the recent ones on
Psalm 90 and
Psalm 109.
SELECT ROOT,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,
P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,
AF,AG,AH,AI,AJ,AK,AL,AM,AN,VSS,REALROOT
FROM
(SELECT kwd_cd root, (decode(vs,1,bl,NULL)) a, (decode(vs,2,bl,NULL)) b, (decode(vs,3,bl,NULL)) c, (decode(vs,4,bl,NULL)) d, (decode(vs,5,bl,NULL)) e, (decode(vs,6,bl,NULL)) f, (decode(vs,7,bl,NULL)) g, (decode(vs,8,bl,NULL)) h, (decode(vs,9,bl,NULL)) i, (decode(vs,10,bl,NULL)) j, (decode(vs,11,bl,NULL)) k, (decode(vs,12,bl,NULL)) l, (decode(vs,13,bl,NULL)) m, (decode(vs,14,bl,NULL)) n, (decode(vs,15,bl,NULL)) o, (decode(vs,16,bl,NULL)) p, (decode(vs,17,bl,NULL)) q, (decode(vs,18,bl,NULL)) r, (decode(vs,19,bl,NULL)) s, (decode(vs,20,bl,NULL)) t, (decode(vs,21,bl,NULL)) u, (decode(vs,22,bl,NULL)) v, (decode(vs,23,bl,NULL)) w, (decode(vs,24,bl,NULL)) x, (decode(vs,25,bl,NULL)) y, (decode(vs,26,bl,NULL)) z, (decode(vs,27,bl,NULL)) aa, (decode(vs,28,bl,NULL)) ab, (decode(vs,29,bl,NULL)) ac, (decode(vs,30,bl,NULL)) ad, (decode(vs,31,bl,NULL)) ae, (decode(vs,32,bl,NULL)) af, (decode(vs,33,bl,NULL)) ag, (decode(vs,34,bl,NULL)) ah, (decode(vs,35,bl,NULL)) ai, (decode(vs,36,bl,NULL)) aj, (decode(vs,37,bl,NULL)) ak, (decode(vs,38,bl,NULL)) al, (decode(vs,39,bl,NULL)) am, (decode(vs,40,bl,NULL)) an,
grp_cd realroot,
vss
FROM
(SELECT dgm_grp_cd, kwd_seq, kwd_id, kwd_cd,
(SELECT max(vs)
FROM
(SELECT DGM_GRP_CD,
VS
FROM
(SELECT dgm_grp_cd,
rownum vs
FROM
(SELECT dgm_grp_cd,
min(kwd_seq),
min(kwd_id)
FROM
(SELECT count(kwd_loc) over (partition BY dgm_grp_cd) keyword_count,
min(kwd_id) over (partition BY dgm_grp_cd
ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))),kwd_id) kwd_id,
dgm_grp_cd,
min(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))) over (partition BY dgm_grp_cd
ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))),kwd_id) kwd_seq, replace(kwd_loc,'-','') kwd_loc,
(SELECT
(SELECT nvl(max(comm_fld),upper('2')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_first')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
FROM dual) fst_only,
kwd_gen_key1
FROM cs_dgmkwd d
WHERE instr(
(SELECT nvl(max(comm_fld),upper('`L,YHVH,)LVH,KY,)L,)T')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('excl_root')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL),dgm_grp_cd)=0
AND kwd_tp =
(SELECT nvl(max(comm_fld),upper('PSALMS')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('b_books')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND (kwd_loc LIKE substr(
(SELECT nvl(max(comm_fld),upper('--1')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_loc_st')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL),1,3)||'%')
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) BETWEEN
(SELECT nvl(max(comm_fld),upper('001')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_st')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('176')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_end')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) NOT BETWEEN
(SELECT nvl(max(comm_fld),upper('998')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_xst')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('999')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_xend')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))), kwd_id)
WHERE keyword_count >
(SELECT nvl(max(comm_fld),upper('1')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_count1')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND keyword_count <=
(SELECT nvl(max(comm_fld),upper('99')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_count2')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND (fst_only= '2'
OR fst_only=decode(kwd_gen_key1,NULL,'0','1'))
GROUP BY dgm_grp_cd
ORDER BY 2,
3)))) maxvs,
(SELECT vs
FROM
(SELECT DGM_GRP_CD,
VS
FROM
(SELECT dgm_grp_cd,
rownum vs
FROM
(SELECT dgm_grp_cd,
min(kwd_seq),
min(kwd_id)
FROM
(SELECT count(kwd_loc) over (partition BY dgm_grp_cd) keyword_count,
min(kwd_id) over (partition BY dgm_grp_cd
ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))),kwd_id) kwd_id,
dgm_grp_cd,
min(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))) over (partition BY dgm_grp_cd
ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))),kwd_id) kwd_seq, replace(kwd_loc,'-','') kwd_loc,
(SELECT
(SELECT nvl(max(comm_fld),upper('2')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_first')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
FROM dual) fst_only,
kwd_gen_key1
FROM cs_dgmkwd d
WHERE instr(
(SELECT nvl(max(comm_fld),upper('`L,YHVH,)LVH,KY,)L,)T')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('excl_root')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL),dgm_grp_cd)=0
AND kwd_tp =
(SELECT nvl(max(comm_fld),upper('PSALMS')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('b_books')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND (kwd_loc LIKE substr(
(SELECT nvl(max(comm_fld),upper('--1')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_loc_st')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL),1,3)||'%')
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) BETWEEN
(SELECT nvl(max(comm_fld),upper('001')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_st')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('176')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_end')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) NOT BETWEEN
(SELECT nvl(max(comm_fld),upper('998')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_xst')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('999')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_xend')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
ORDER BY (decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'))), kwd_id)
WHERE keyword_count >
(SELECT nvl(max(comm_fld),upper('1')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_count1')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND keyword_count <=
(SELECT nvl(max(comm_fld),upper('99')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_count2')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND (fst_only= '2'
OR fst_only=decode(kwd_gen_key1,NULL,'0','1'))
GROUP BY dgm_grp_cd
ORDER BY 2,
3)))
WHERE dgm_grp_cd = e.dgm_grp_cd) vs,
vss,
kwd_gen_key1,
grp_cd,
bl
FROM
(SELECT count(kwd_loc) over (partition BY dgm_grp_cd) keyword_count,substr(kwd_loc,5) vss, replace(replace(substr(kwd_cd,1,instr(kwd_cd,'&')-1)||'░'||'','color','background-color'),'* ','') bl, kwd_id,kwd_cd, dgm_grp_cd,
(SELECT
(SELECT nvl(max(comm_fld),upper('2')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_first')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
FROM dual) fst_only,
decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'), kwd_loc) kwd_seq,
replace(kwd_loc,'-','') kwd_loc,
kwd_gen_key1,
grp_cd
FROM cs_dgmkwd d
WHERE instr(
(SELECT nvl(max(comm_fld),upper('`L,YHVH,)LVH,KY,)L,)T')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('excl_root')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL),dgm_grp_cd)=0
AND kwd_tp =
(SELECT nvl(max(comm_fld),upper('PSALMS')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('b_books')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND (kwd_loc LIKE substr(
(SELECT nvl(max(comm_fld),upper('--1')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_loc_st')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL),1,3)||'%')
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) BETWEEN
(SELECT nvl(max(comm_fld),upper('001')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_st')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('176')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_end')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND substr(decode(length(substr(kwd_loc,instr(kwd_loc,'.')+1)),2,replace(kwd_loc,'.','.0'),1,replace(kwd_loc,'.','.00'),kwd_loc),5) NOT BETWEEN
(SELECT nvl(max(comm_fld),upper('998')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_xst')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL) AND
(SELECT nvl(max(comm_fld),upper('999')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_seq_xend')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
ORDER BY kwd_seq,
kwd_id) e
WHERE keyword_count >
(SELECT nvl(max(comm_fld),upper('1')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_count1')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND keyword_count <=
(SELECT nvl(max(comm_fld),upper('99')) comm_fld
FROM cs_udpv
WHERE udg_cd = 'SVARS'
AND udp_cd = 'KEYVALPR'
AND udpv_gen_key1 = USER
AND udpv_gen_key2 = upper('kwd_count2')
AND udpv_gen_key3 IS NULL
AND udpv_gen_key4 IS NULL)
AND (fst_only= '2'
OR fst_only=decode(kwd_gen_key1,NULL,'0','1'))
ORDER BY 2,
3))
