LibreOffice logo
BASEDOCUMENTER
The software tool for documenting your LibreOffice Base applications
 
Database file/home/jean-pierre/Documents/BaseDocumenter/www/databases/LODoc/Example_Autotext_Searchmark_Spelling.odb
File actual save date2018-08-21 16:38:15
Scanning done on2018-08-21 16:57:13
Documentation generated on2018-08-21 16:57:24
Table of contents
Example_Autotext_Searchmark_Spelling
List of queries
Query name Used by Query type Direct SQL List of fields SQL
Query_Form_Like Search_mark (Form)
Spelling_afterwards (Form)		 SELECT   Details 
SELECT 
	"ID", 
	"memo" 
FROM 
	"table" 
WHERE 
	LOWER ("memo") LIKE '%' || LOWER (
		IFNULL(
			(
				SELECT 
					"searchtext" 
				FROM 
					"filter" 
				WHERE 
					"ID" = TRUE), 
			'')) || '%'
Query_Form_Locate   SELECT   Details 
SELECT 
	"ID", 
	"memo" 
FROM 
	"table" 
WHERE 
	LOCATE(
		LOWER (
			IFNULL(
				(
					SELECT 
						"searchtext" 
					FROM 
						"filter" 
					WHERE 
						"ID" = TRUE), 
				'')), 
		LOWER ("memo")) > 0
Query_Locate_0   SELECT   Details 
SELECT 
	"ID", 
	"memo", 
	LOCATE(
		LOWER (: Searchtext), 
		LOWER ("memo")) "position" 
FROM 
	"table"
Query_Locate_1   SELECT   Details 
SELECT 
	"ID", 
	"memo", 
	"position", 
	CASE WHEN "position" = 0 THEN '**not found**' WHEN "position" < 10 THEN SUBSTRING ("memo", 1, 25) ELSE SUBSTRING (
		"memo", 
		LOCATE(' ', "memo", "position" - 10) + 1, 
		25) END "hit" 
FROM 
	(
		SELECT 
			"ID", 
			"memo", 
			LOCATE(
				LOWER (: Searchtext), 
				LOWER ("memo")) "position" 
		FROM 
			"table")
Query_Locate_2   SELECT ✔ Details 
SELECT 
	"ID", 
	"memo", 
	"position01", 
	"hit01", 
	"position02", 
	CASE WHEN "position02" = 0 THEN '**not found**' WHEN "position02" < 10 THEN SUBSTRING (
		"memo", 
		1, 
		LOCATE(' ', "memo", 25)) ELSE SUBSTRING (
		"memo", 
		LOCATE(
			' ', "memo", "position02" - 10
		) + 1, 
		(
			LOCATE(
				' ', "memo", "position02" + 20
			) - (
				LOCATE(
					' ', "memo", "position02" - 10
				) + 1))) END AS "hit02", 
	CASE WHEN "position02" = 0 THEN 0 ELSE LOCATE(
		LOWER (: Searchtext), 
		LOWER ("memo"), 
		"position02" + 1) END AS "position03" 
FROM 
	(
		SELECT 
			"ID", 
			"memo", 
			"position01", 
			CASE WHEN "position01" = 0 THEN '**not found**' WHEN "position01" < 10 THEN SUBSTRING (
				"memo", 
				1, 
				LOCATE(' ', "memo", 25)) ELSE SUBSTRING (
				"memo", 
				LOCATE(
					' ', "memo", "position01" - 10
				) + 1, 
				(
					LOCATE(
						' ', "memo", "position01" + 20
					) - (
						LOCATE(
							' ', "memo", "position01" - 10
						) + 1))) END AS "hit01", 
			CASE WHEN "position01" = 0 THEN 0 ELSE LOCATE(
				LOWER (: Searchtext), 
				LOWER ("memo"), 
				"position01" + 1) END AS "position02" 
		FROM 
			(
				SELECT 
					"ID", 
					"memo", 
					LOCATE(
						LOWER (: Searchtext), 
						LOWER ("memo")) "position01" 
				FROM 
					"table"))
Query_Locate_5   SELECT   Details 
SELECT 
	"ID", 
	"memo", 
	"position01", 
	"hit01", 
	"position02", 
	"hit02", 
	"position03", 
	"hit03", 
	"position04", 
	"hit04", 
	"position05", 
	CASE WHEN "position05" = 0 THEN '**not found**' WHEN "position05" < 10 THEN SUBSTRING (
		"memo", 
		1, 
		LOCATE(' ', "memo", 25)) ELSE SUBSTRING (
		"memo", 
		LOCATE(
			' ', "memo", "position05" - 10
		) + 1, 
		(
			LOCATE(
				' ', "memo", "position05" + 20
			) - (
				LOCATE(
					' ', "memo", "position05" - 10
				) + 1))) END "hit05", 
	CASE WHEN "position05" = 0 THEN 0 ELSE LOCATE(
		LOWER (: Searchtext), 
		LOWER ("memo"), 
		"position05" + 1) END "position06" 
FROM 
	(
		SELECT 
			"ID", 
			"memo", 
			"position01", 
			"hit01", 
			"position02", 
			"hit02", 
			"position03", 
			"hit03", 
			"position04", 
			CASE WHEN "position04" = 0 THEN '**not found**' WHEN "position04" < 10 THEN SUBSTRING (
				"memo", 
				1, 
				LOCATE(' ', "memo", 25)) ELSE SUBSTRING (
				"memo", 
				LOCATE(
					' ', "memo", "position04" - 10
				) + 1, 
				(
					LOCATE(
						' ', "memo", "position04" + 20
					) - (
						LOCATE(
							' ', "memo", "position04" - 10
						) + 1))) END "hit04", 
			CASE WHEN "position04" = 0 THEN 0 ELSE LOCATE(
				LOWER (: Searchtext), 
				LOWER ("memo"), 
				"position04" + 1) END "position05" 
		FROM 
			(
				SELECT 
					"ID", 
					"memo", 
					"position01", 
					"hit01", 
					"position02", 
					"hit02", 
					"position03", 
					CASE WHEN "position03" = 0 THEN '**not found**' WHEN "position03" < 10 THEN SUBSTRING (
						"memo", 
						1, 
						LOCATE(' ', "memo", 25)) ELSE SUBSTRING (
						"memo", 
						LOCATE(
							' ', "memo", "position03" - 10
						) + 1, 
						(
							LOCATE(
								' ', "memo", "position03" + 20
							) - (
								LOCATE(
									' ', "memo", "position03" - 10
								) + 1))) END "hit03", 
					CASE WHEN "position03" = 0 THEN 0 ELSE LOCATE(
						LOWER (: Searchtext), 
						LOWER ("memo"), 
						"position03" + 1) END "position04" 
				FROM 
					(
						SELECT 
							"ID", 
							"memo", 
							"position01", 
							"hit01", 
							"position02", 
							CASE WHEN "position02" = 0 THEN '**not found**' WHEN "position02" < 10 THEN SUBSTRING (
								"memo", 
								1, 
								LOCATE(' ', "memo", 25)) ELSE SUBSTRING (
								"memo", 
								LOCATE(
									' ', "memo", "position02" - 10
								) + 1, 
								(
									LOCATE(
										' ', "memo", "position02" + 20
									) - (
										LOCATE(
											' ', "memo", "position02" - 10
										) + 1))) END "hit02", 
							CASE WHEN "position02" = 0 THEN 0 ELSE LOCATE(
								LOWER (: Searchtext), 
								LOWER ("memo"), 
								"position02" + 1) END "position03" 
						FROM 
							(
								SELECT 
									"ID", 
									"memo", 
									"position01", 
									CASE WHEN "position01" = 0 THEN '**not found**' WHEN "position01" < 10 THEN SUBSTRING (
										"memo", 
										1, 
										LOCATE(' ', "memo", 25)) ELSE SUBSTRING (
										"memo", 
										LOCATE(
											' ', "memo", "position01" - 10
										) + 1, 
										(
											LOCATE(
												' ', "memo", "position01" + 20
											) - (
												LOCATE(
													' ', "memo", "position01" - 10
												) + 1))) END "hit01", 
									CASE WHEN "position01" = 0 THEN 0 ELSE LOCATE(
										LOWER (: Searchtext), 
										LOWER ("memo"), 
										"position01" + 1) END "position02" 
								FROM 
									(
										SELECT 
											"ID", 
											"memo", 
											LOCATE(
												LOWER (: Searchtext), 
												LOWER ("memo")) "position01" 
										FROM 
											"table")))))