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,REALROOTFROM (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))
No comments:
Post a Comment