www.katabiblon.com
Kata BiblonSELECT
concat(orig.book_cd, orig.chapter, ":", orig.subverse) AS orig_forum_tag
, orig.iverse
, orig.subverse
#, pericope.pericope_nm, ifnull(null, replace(orig.modified_html, "class=\"annotate\"", "class=\"annotate\" lang=\"en\"")) AS original_html, apparatus.variants_list_html AS apparatus_html, if(base_dn.maps_to_chapter<0, null, if(locate("-", base_dn.maps_to_subverse) or locate(",", base_dn.maps_to_subverse), concat(base_dn.maps_to_book_cd, " ", base_dn.maps_to_chapter, ":", base_dn.maps_to_subverse), concat(base.book_cd, " ", base.chapter, ":", base.subverse))) AS base_forum_tag, replace(lower(coalesce(db.site_book_short_nm_eng, db.site_book_nm_eng, db.site_book_long_nm_eng, db.document_book_nm_i18n)), " ", "_") AS bible_hub_title, ifnull(vwt.user_html, ifnull(base.normalized_xml, # ***GROUP BASE VERSES THAT MAP MANY-TO-ONE (E.G., GK EXODUS 38:16 = HEB EXODUS 37:20-22)***
(SELECT group_concat(concat("(", base.iverse, ") ", base.normalized_xml) separator " ")
FROM kata_text.eng_webhtm_verses AS base
WHERE (base.book_cd, base.chapter) = (base_dn.maps_to_book_cd, base_dn.maps_to_chapter)
AND (
(locate("-", base_dn.maps_to_subverse) AND base.iverse BETWEEN floor(left(base_dn.maps_to_subverse, locate("-", base_dn.maps_to_subverse) -1)) AND floor(mid(base_dn.maps_to_subverse, locate("-", base_dn.maps_to_subverse) + 1)))
OR (locate(",", base_dn.maps_to_subverse) AND find_in_set(base.iverse, base_dn.maps_to_subverse))
)
))
) AS user_html
, if(vwt.tid is null, null,
(SELECT group_concat(concat("<sup>", y.reference_mark, "</sup> ", cast(y.user_html as binary)) order by y.reference_mark separator "\t")
FROM kata_wiki.wiki_translations_commentary AS y
WHERE y.tid = vwt.tid
GROUP BY y.tid)
) AS commentary_html, null AS brenton_forum_tag, null AS brenton_html, (SELECT count(*)
FROM kata_forums.forum f
STRAIGHT_JOIN kata_forums.message m
WHERE f.tag = concat(orig.book_cd, orig.chapter, ":", orig.subverse)
and m.fid = f.fid
GROUP BY f.tag) AS message_cnt #currently forums are set up only for the GNT
, (
SELECT count(distinct uid)
FROM kata_wiki.wiki_translations_history AS wth
WHERE (wth.wiki_translation_cd, wth.book_cd, wth.chapter, wth.iverse, wth.subverse) = ("wiki-english-gnt", orig.book_cd, orig.chapter, orig.iverse, orig.subverse)
) AS editors_cnt
, (
SELECT date(max(insert_dtm))
FROM kata_wiki.wiki_translations_history AS wth
WHERE (wth.wiki_translation_cd, wth.book_cd, wth.chapter, wth.iverse, wth.subverse) = ("wiki-english-gnt", orig.book_cd, orig.chapter, orig.iverse, orig.subverse)
) AS last_edit_dtm
FROM kata_text.grc_robinson_verses AS orig
LEFT JOIN kata_text.grc_robinson_verses AS apparatus ON (apparatus.book_cd, apparatus.chapter, apparatus.iverse, apparatus.subverse) = (orig.book_cd, orig.chapter, orig.iverse, orig.subverse)
# ***PERICOPES***
LEFT JOIN kata_system.document_pericopes AS pericope
ON (pericope.orig_document_cd, pericope.book_cd, pericope.chapter, pericope.iverse, pericope.subverse) = (orig.document_cd, orig.book_cd, orig.chapter, orig.iverse, orig.subverse)
# ***USER WIKI TRANSLATION***
LEFT JOIN kata_wiki.view_wiki_translations_verse AS vwt
ON (vwt.wiki_translation_cd, vwt.book_cd, vwt.chapter, vwt.iverse, vwt.subverse) = ("wiki-english-gnt", orig.book_cd, orig.chapter, orig.iverse, orig.subverse)
# ***BASE TRANSLATION***
LEFT JOIN kata_system.document_normalization AS base_dn use index (i_maps_to) ON base_dn.id =
(
SELECT ifnull(max(if(dn.iverse is null, null, dn.id)), ifnull(max(if(dn.chapter is null, null, dn.id)), max(dn.id)))
FROM kata_system.document_normalization dn
WHERE (dn.orig_document_cd, dn.base_document_cd, dn.book_cd, ifnull(dn.chapter, orig.chapter))
= (orig.document_cd, "bible-web", orig.book_cd, orig.chapter)
AND (
(ifnull(dn.iverse, orig.iverse), ifnull(dn.subverse, orig.subverse))
= (orig.iverse, orig.subverse)
OR (dn.iverse<0 AND locate("-", dn.subverse) AND orig.iverse BETWEEN floor(left(dn.subverse, locate("-", dn.subverse) -1)) AND floor(mid(dn.subverse, locate("-", dn.subverse) + 1)))
OR (dn.iverse<0 AND locate(",", dn.subverse) AND find_in_set(orig.iverse, dn.subverse))
)
)
LEFT JOIN kata_text.eng_webhtm_verses AS base
ON (base.book_cd, base.chapter, base.iverse, base.subverse)
= (ifnull(base_dn.maps_to_book_cd, orig.book_cd)
, ifnull(base_dn.maps_to_chapter, orig.chapter)
, ifnull(base_dn.maps_to_iverse, orig.iverse + ifnull(base_dn.adjustment, 0))
#, if(base_dn.adjustment is null or base_dn.adjustment = 0, ifnull(base_dn.maps_to_subverse, orig.subverse), base.subverse)
, ifnull(base_dn.maps_to_subverse, if(orig.subverse = concat(orig.iverse), orig.subverse + ifnull(base_dn.adjustment, 0), orig.subverse))
)
AND (
ifnull(base_dn.iverse, 0) >= 0
OR (base_dn.iverse<0 AND locate("-", base_dn.subverse) AND orig.iverse = floor(left(base_dn.subverse, locate("-", base_dn.subverse) -1)))
OR (base_dn.iverse<0 AND locate(",", base_dn.subverse) AND orig.iverse = floor(left(base_dn.subverse, locate(",", base_dn.subverse) -1)))
)
LEFT JOIN kata_system.document_books AS db
ON (db.document_cd, db.book_cd) = (base.document_cd, base.book_cd)WHERE elt(1, 1, ?) and (orig.book_cd, orig.chapter) = (?, ?) and elt(1,1, ?, ?)ORDER BY orig.iverse, orig.subverse
ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT group_concat(concat("(", base.iverse, ") ", base.normalized_xml) separato' at line 6
ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT group_concat(concat("(", base.iverse, ") ", base.normalized_xml) separato' at line 6