www.katabiblon.com
SELECT orig.orig_forum_tag , orig.iverse , orig.subverse , pericope.pericope_nm , orig.original_html # ***RP/NAUBS (OR OTHER) APPARATUS*** , apparatus.ketib_qere_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, 0 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-hmt", 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-hmt", orig.book_cd, orig.chapter, orig.iverse, orig.subverse) ) AS last_edit_dtm FROM ( SELECT concat(v1.book_cd, v1.chapter, ":", v1.subverse) AS orig_forum_tag, v1.document_cd, v1.book_cd, v1.chapter, v1.iverse, v1.subverse, #group_concat(v1.start_time, if(v1.translation is null, if(char_length(v1.word_utf8) = 2, v1.word_utf8, concat(v1.word_utf8, "&nbsp;<sub><bdi dir=\"ltr\">", kata_system.func_heb_translit(v1.uncantillated_word), "</bdi></sub>")), concat( group_concat(v1.start_time, if(v1.translation is null, v1.word_utf8, concat( "<span class=\"interlinear\"><span onclick=\"element = this.nextSibling; element.style.display = 'block'; return false;\">", v1.word_utf8, "</span><span class=\"shadow\"><span class=\"popup\"> <span lang=\"en\">", "[<a href=\"https://lexicon.katabiblon.com/?search=", v1.word_utf8, "\">lexicon</a>][<a href=\"https://lexicon.katabiblon.com/inflect.php?lemma=", v1.word_utf8, "\">inflect</a>][<a href=\"#\" onclick=\"element = this.parentNode.parentNode.parentNode; element.style.display = 'none'; return false;\">close</a>]<br />", "<strong>", substring_index(v1.pos_ext, " ", 1), "</strong>", substring(v1.pos_ext, locate(" ", v1.pos_ext)), "</span><br />", replace(replace(v1.lexical_long_name_ext, "<lemma>", ""), "</lemma>", ""), "<br />", v1.uncontracted_word, "<br /><span lang=\"en\">", v1.parse, "<br />", replace(v1.translation, "~(", "~~("), "<br />", v1.definition, "</span></span></span>", if(v1.strongs_numbers is null, "</span>", concat("<sup> ", v1.strongs_numbers, "</sup></span>")) )) order by v1.position separator " ") AS original_html FROM ( SELECT orig.document_cd, orig.book_cd, orig.chapter, orig.iverse, orig.subverse, # ***GREEK WORDS*** words.position, normalized.word_utf8 AS word_utf8, #normalized.uncantillated_word, # ***INTERLINEAR DEFINITIONS*** cast(group_concat(concat( vwi.lexical_long_name_lex, case when vwi.lexical_name_ext is null then "" when left(vwi.lexical_name_ext, 1) = "," then vwi.lexical_name_ext else concat(" (", vwi.lexical_name_ext, ")") end, ifnull(dpp.principal_parts_list, "") ) order by vwi.pos_sort_num separator "; ") as char) AS lexical_long_name_ext, group_concat(binary case vwi.pos_sort_num when 8 then "Verb, Participle" when 9 then "Participle" else dpp.pos_ext end order by vwi.pos_sort_num separator "; ") AS pos_ext, #"binary" appears to be necessary for dpp.pos_ext, but not vwi.pos_ext; vwi.pos_ext differentiates verbs and participles; dpp.pos_ext can be updated dynamically group_concat(vwi.uncontracted_word_list order by vwi.pos_sort_num separator "; ") AS uncontracted_word, group_concat(vwi.parse_list order by vwi.pos_sort_num separator "; ") AS parse, /*group_concat( binary #group_concat order-by goes kablooey with "dynamic" text if group_concat_max_len > 21844 and charset = utf8, therefore convert to binary (or latin1) trim(concat(vwi.safe_formatted_user_definition, " ", ifnull(vwi.safe_definition_long, ""))) order by vwi.pos_sort_num separator "; ") AS definition, */ group_concat( binary #group_concat order-by goes kablooey with "dynamic" text if group_concat_max_len > 21844 and charset = utf8, therefore convert to binary (or latin1) trim(ifnull(vwi.safe_definition_long, "")) order by vwi.pos_sort_num separator "; ") AS definition, group_concat( binary #group_concat order-by goes kablooey with "dynamic" text if group_concat_max_len > 21844 and charset = utf8, therefore convert to binary (or latin1) vwi.safe_interlinear_translation order by vwi.pos_sort_num separator "; ") AS translation, # ***STRONGS NUMBERS*** group_concat( concat("<a rel=\"nofollow\" href=\"https://www.katabiblon.com/global/vocabulary.php?text=HB&amp;book=Prv&amp;ch=14&amp;lemma=0.000000E+01D%CF%CE9EFF", strongs.super_num, "\">", strongs.super_num, "</a>") order by binary(strongs.super_num) + 0, strongs.super_num separator " | ") AS strongs_numbers # ***AUDIO*** , ifnull(concat("<span class=\"audio\" title=\"", a.start_time, "\"></span>"), "") as start_time FROM ( 0 AS orig # ***GREEK WORDS*** , kata_text.heb_westminster_words AS words LEFT JOIN kata_text.heb_westminster_normalized_words AS normalized ON normalized.word_utf8 = words.word_utf8 LEFT JOIN kata_lexicon.grc_look_alike_words AS law ON law.look_alike_word = binary normalized.uncantillated_word #AND law.strict_id < 0 #no filtering (e.g., do not differentiate ὁ and ὅ) AND law.strict_id = 0 #least strict (e.g., differentiate ὁ and ὅ; but do not differentiate φίλων[n.] and φιλῶν[v.]) #AND law.strict_id < 2 #more strict (e.g., differentiate φίλων[n.] and φιλῶν[v.]) , kata_system.documents AS d ) # ***INTERLINEAR DEFINITIONS*** LEFT JOIN kata_lexicon.view_wiki_interlinear AS vwi ON (vwi.inflected_word, binary vwi.inflected_word) = (normalized.base_word collate utf8_general_ci, binary normalized.base_word) #collate necessary in order to not blow up when joining to heb_westminster_normalized_words AND (vwi.unsubscripted_fl = "N" OR vwi.unsubscripted_fl = d.grc_unsubscripted_fl) #controls whether θύραι matches both θύραι and θύρᾳ AND vwi.super_lemma = ifnull(law.super_lemma, vwi.super_lemma) LEFT JOIN kata_lexicon.grc_dictionary_posx_pparts AS dpp ON dpp.super_lemma = vwi.super_lemma # ***STRONGS NUMBERS*** LEFT JOIN kata_lexicon.grc_lexicon_occurrences AS occur ON occur.super_lemma = vwi.super_lemma AND occur.gnt_count <= ? LEFT JOIN kata_lexicon.grc_dictionary_strongs strongs ON strongs.super_lemma = occur.super_lemma AND strongs.sub_lemma = strongs.super_lemma # ***AUDIO*** LEFT JOIN kata_wiki.audio AS a ON (a.book_cd, a.chapter, a.verse, a.position) = (orig.book_cd, orig.chapter, orig.iverse, words.position) AND "on" = "" WHERE (orig.book_cd, orig.chapter) = (?, ?) and elt(1,1, ?, ?) AND d.document_cd = orig.document_cd AND (words.book_cd, words.chapter, words.iverse, words.subverse) = (orig.book_cd, orig.chapter, orig.iverse, orig.subverse) GROUP BY orig.document_cd, orig.book_cd, orig.chapter, orig.iverse, orig.subverse, words.position, words.word_utf8 ) AS v1 GROUP BY v1.document_cd, v1.book_cd, v1.chapter, v1.iverse, v1.subverse ) AS orig # ***RP/NAUBS (OR OTHER) APPARATUS*** LEFT JOIN kata_text.heb_westminster_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-hmt", 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)) ) LEFT JOIN kata_system.document_books AS db ON (db.document_cd, db.book_cd) = (base.document_cd, base.book_cd)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 '0 AS orig # ***GREEK WORDS*** , kata_text.heb_westminster_words AS ' at line 117