code snippets for sqlite

attach_multiple_databases_from_designer.md

This statement in a single query (e.g. in FastStats Designer) can attach other databases to the current one attach database C:\Apteco\Build\system\data\lookups.sqlite as lookup The ATTACH is not persistent in Designer

ATTACH DATABASE "C:\Apteco\Build\system\data\lookups.sqlite" AS lookup;
SELECT g.*, p.*
FROM geschaeftspartner g
INNER JOIN lookup.plz5 p ON g.plz = p.plz limit 10;

Regarding to https://www.connectionstrings.com/sqlite/ we can also make use of the :memory: databases (in RAM) in sqlite using the connection string

Data Source=:memory:;Version=3;New=True;

If you want to learn more about in memory databases in sqlite, please follow this link: https://www.sqlite.org/inmemorydb.html

With the connection string above and this statement for a data source you can connect multiple file based databases to the in-memory-database and join them:

attach database "C:\Apteco\Build\sytem\data\lookups.sqlite" as lookup;
attach database "C:\Apteco\Build\system\data\data.sqlite" as data;
select * from data.geschaeftspartner g inner join lookup.plz5 p on g.plz = p.plz limit 10;

create_x_random_numbers.sql

-- create adhoc x random numbers between 0 and n

CREATE VIEW random_nr_2 as 

WITH RECURSIVE
    cte(x) AS (
       SELECT abs(random())%1000
       UNION ALL
       SELECT abs(random())%1000
         FROM cte
        LIMIT 100000
  )
SELECT x FROM cte

regexp_and_list.sql

-- group_concat uses a result and combines it into one string
-- this result contains special characters for regex like + and will be espaced with \
-- and then this filter will be used via REGEXP regular expressions

SELECT
  *
FROM
  vortrag
WHERE
  address_phone_no REGEXP(
    SELECT
      replace(group_concat(Vorwahl_Int, "|"), '+', '\+')
    FROM
      Mobil_Preparation
  )