SQLite Forum

Timeline
Login

50 most recent forum posts by user jicman

2021-11-20
20:35 Post: Language pairs strings how to (artifact: 004c25a59b user: jicman)

Greetings.

I am planning a website to have both English and Spanish strings. These strings would be in SQLite. The idea is for a person to click on the language and be able to choose between the two languages and the website would populate on the chosen language. The question is what is the best way to do this? For example:

CREATE TABLE IF NOT EXIST Headers (H1, H2, H3);

This will take care of the English. I am stuck on whether I create a Headers_es (H1, H2, H3) also to address the Spanish language. I know there are probably many ways to do this, but, I would like some opinion from the experts. Any guidance would be greatly appreciated. Thanks.

josé

2021-11-16
20:12 Reply: How to address a variable within a WITH statement (artifact: 2a54545fe9 user: jicman)

Thanks. This would be too complicated as there are other pieces and tables to be added to the WITH piece of the SQL code. I guess I will have to use the value instead. Thanks again.

josé

18:58 Post: How to address a variable within a WITH statement (artifact: 7560bfdc41 user: jicman)

Greetings.

I have this WITH Statement,

WITH LastEntries (ProjID, ml_insert)
AS
(
   SELECT 'PR0000019191' AS PID,
   (
        SELECT max(insertdate) FROM Project_Keytask_and_Milestones where projid = 'PR0000019191'
   )
) 
SELECT ProjID, ml_insert from LastEntries;

Which works, but I would like to use the first value like this,

WITH LastEntries (ProjID, ml_insert)
AS
(
   SELECT 'PR0000019191' AS PID,
   (
        SELECT max(insertdate) FROM Project_Keytask_and_Milestones where projid = PID
   )
) 
SELECT ProjID, ml_insert from LastEntries;

But, it does not work. Any way to do this, or do I have to use the value? Thanks.

josé

2021-11-07
02:16 Reply: Best way to keep song lyrics in SQLite (artifact: 1db1b25379 user: jicman)

Thanks, Simon. This is great. I think the songs are small enough that I will leave FTS5 for later. I appreciate your input.

josé

2021-11-06
20:29 Post: Best way to keep song lyrics in SQLite (artifact: 17a070f292 user: jicman)

Greetings!

I need of creating a SQLite database of songs to be able to search on words having songs with those words popup. What is the best way to do this on SQLite? I am thinking of having a songID, songTitle, songKeywords, and songLyrics. Any thought would be greatly appreciated. Thanks.

josé

2021-10-12
14:58 Reply: Compiling sqlite system on Ubuntu (artifact: 15c311a560 user: jicman)

If you want to build that yourself, you are probably best off with Roger Binns' APSW (Another Python SQLite Wrapper)

Thanks, Kees.

13:18 Reply: Compiling sqlite system on Ubuntu (artifact: 3ec0d6990e user: jicman)

Thank you both, Keith and Larry. That's fixed. Now, what about the rest of these files:

jcabrera@selva:~/b/sqlite/sqlite-autoconf-3360000$ ls -l sqlite3*
-rwxrwxr-x 1 jcabrera jcabrera  8880672 Oct 11 22:30 sqlite3
-rw-rw-r-- 1 jcabrera jcabrera     8928 Jun 18 14:52 sqlite3.1
-rw-rw-r-- 1 jcabrera jcabrera  8312766 Jun 18 14:52 sqlite3.c
-rw-rw-r-- 1 jcabrera jcabrera    35437 Jun 18 14:52 sqlite3ext.h
-rw-rw-r-- 1 jcabrera jcabrera   588809 Jun 18 14:52 sqlite3.h
-rw-rw-r-- 1 jcabrera jcabrera      288 Oct 11 22:28 sqlite3.lo
-rw-rw-r-- 1 jcabrera jcabrera 17826344 Oct 11 22:28 sqlite3.o
-rw-r--r-- 1 root     root          286 Oct 11 22:42 sqlite3.pc
-rw-rw-r-- 1 jcabrera jcabrera      267 Jun 18 14:52 sqlite3.pc.in
-rw-rw-r-- 1 jcabrera jcabrera     1992 Jun 18 14:52 sqlite3.rc
-rw-rw-r-- 1 jcabrera jcabrera       78 Jun 18 14:52 sqlite3rc.h
-rw-rw-r-- 1 jcabrera jcabrera  2644408 Oct 11 22:28 sqlite3-shell.o
-rw-rw-r-- 1 jcabrera jcabrera 18056664 Oct 11 22:30 sqlite3-sqlite3.o
jcabrera@selva:~/b/sqlite/sqlite-autoconf-3360000$

Where do I place them? I am going to use python for some scripting and I want to make sure that the latest built libraries are the one it uses. Thanks.

josé

03:04 Post: Compiling sqlite system on Ubuntu (artifact: 70c1ced3b5 user: jicman)

Greetings.

I installed SQLite using APT, but the version is last year's. I am trying to build sqlite 3.36.0, and I tried a few options, including this command,

sudo gcc shell.c sqlite3.c -lpthread -ldl -lm

but all I get is the a.out file. I am not getting what I want. I know I am missing some options to get the .so libraries installed, as well as the sqlite3 tool, so, if anyone can help, I would greatly appreciated.

The last thing I did was was run ./configure and make, after downloading the sqlite-autoconf-3360000.tar.gz. And it appears as if everything goes well, but when I do 'which sqlite3', there is no output. Any help would be greatly appreciated. Thanks.

josé

2021-08-13
13:30 Reply: .expert Error: ON clause references tables to its right (artifact: 260c590be4 user: jicman)

Thanks, Keith. So, what you are saying is that the .expert response is correct in providing the feedback with an error. Then the sqlite3 tool should not execute the SQLite bad code, and should also provide an error response. However, it is providing an answer. They should both provide the same error.

josé

2021-08-12
15:39 Reply: LEFT JOIN Misunderstanding (artifact: 4c6b88364d user: jicman)

Thank you very much.

josé

15:20 Post: .expert Error: ON clause references tables to its right (artifact: b1e3137f9b user: jicman)

Greetings.

This SQLite code works as it should on v3.36.0 on the sqlite3 tool (or, at least, it does what I want :-)):

WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid)
    FROM project_list e
    GROUP BY projid
 )
SELECT c.CreatedDate AS Aug, a.Project_Type, a.ProjID
	FROM Project_List AS a 
	LEFT JOIN Project_Extras AS b ON b.ProjID == a.ProjID
		AND a.Project_Delivered != 'Yes'
		AND a.PMO_Board_Report != 'No' 
		AND a.Status == 'Acknowledged'
		AND a.InsertDate = f.pl_insert
		AND b.FinCarryOver != 'y'
		AND b.MonthlyRpt = 'y'
		AND b.BudgetYear = '2021'
	LEFT JOIN Project_Highlights AS c ON c.ProjID = b.ProjID
		AND c.CreatedDate LIKE '2021-%' 
	LEFT JOIN LastEntries AS f
WHERE
    a.ProjID = f.projid
 	AND a.InsertDate = f.pl_insert
	AND b.InsertDate = f.pe_insert  
ORDER BY a.Manager, a.ProjID
;

But, .expert errors out with this:

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> .expert
sqlite> WITH LastEntries (ProjID, pl_insert, pe_insert)
   ...> AS
   ...> (
   ...>   SELECT projid,
   ...>          max(InsertDate),
   ...>          (SELECT max(insertdate) FROM project_extras where projid = e.projid)
   ...>     FROM project_list e
   ...>     GROUP BY projid
   ...>  )
   ...> SELECT c.CreatedDate AS Aug, a.Project_Type, a.ProjID
   ...> FROM Project_List AS a
   ...> LEFT JOIN Project_Extras AS b ON b.ProjID == a.ProjID
   ...> AND a.Project_Delivered != 'Yes'
   ...> AND a.PMO_Board_Report != 'No'
   ...> AND a.Status == 'Acknowledged'
   ...> AND a.InsertDate = f.pl_insert
   ...> AND b.FinCarryOver != 'y'
   ...> AND b.MonthlyRpt = 'y'
   ...> AND b.BudgetYear = '2021'
   ...> LEFT JOIN Project_Highlights AS c ON c.ProjID = b.ProjID
   ...> AND c.CreatedDate LIKE '2021-%'
   ...> LEFT JOIN LastEntries AS f
   ...> WHERE
   ...>     a.ProjID = f.projid
   ...>  AND a.InsertDate = f.pl_insert
   ...> AND b.InsertDate = f.pe_insert
   ...> ORDER BY a.Manager, a.ProjID
   ...> ;
Error: ON clause references tables to its right

One of them is right. Thoughts? Thanks.

josé

2021-08-11
15:29 Reply: LEFT JOIN Misunderstanding (artifact: 10e275dcb3 user: jicman)
	LEFT JOIN LastEntries AS f
		ON a.ProjID = f.ProjID
		AND a.InsertDate = f.pl_insert AND b.InsertDate = f.pe_insert

These changes produce a totally different set of records. Hmmmm...Thanks.

15:02 Reply: LEFT JOIN Misunderstanding (artifact: 42642ce94b user: jicman)

Thanks. You're right, but every person's idea of indenting and/or breaking code is different. But, I appreciate and thank you for your input.

josé

2021-08-10
20:52 Reply: LEFT JOIN Misunderstanding (artifact: 8053c8b046 user: jicman)

One last question on this... I have been trying to figure this out for a while, but I can't seem to come up with the correct SQLite code. This code works correctly:

WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid)
    FROM project_list e
    GROUP BY projid
 )
SELECT count(c.CreatedDate) AS Aug, a.Project_Type , a.ProjID
FROM Project_List AS a 
LEFT JOIN Project_Extras AS b
ON a.ProjID == b.ProjID
AND
    b.FinCarryOver != 'y'
AND
    b.MonthlyRpt = 'y'
AND
    b.BudgetYear = '2021'
LEFT JOIN Project_Highlights AS c
ON b.ProjID = c.ProjID
AND 
    c.CreatedDate LIKE '2021-08%' 
LEFT JOIN LastEntries AS f
WHERE
    a.Project_Delivered != 'Yes'
AND
    a.PMO_Board_Report != 'No' 
AND
    a.Status == 'Acknowledged'
AND
    a.InsertDate = f.pl_insert
AND
    b.InsertDate = f.pe_insert
AND
    a.ProjID = f.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;

which results to:
2|Activity|PR0000019131
3|Project|PR0000019132
3|Project|PR0000019155
3|Project|PR0000019175
3|Project|PR0000019586
0|Project|PR0000018388
2|Project|PR0000018390
2|Project|PR0000018391
0|Activity|PR0000019764

This is correct.

But this other code gives the wrong result,

WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid)
    FROM project_list e
    GROUP BY projid
 )
SELECT count(d.CreatedDate) AS Jul, count(c.CreatedDate) AS Aug, a.Project_Type , a.ProjID
FROM Project_List AS a 
LEFT JOIN Project_Extras AS b
ON a.ProjID == b.ProjID
AND
    b.FinCarryOver != 'y'
AND
    b.MonthlyRpt = 'y'
AND
    b.BudgetYear = '2021'
LEFT JOIN Project_Highlights AS c
ON b.ProjID = c.ProjID
AND 
    c.CreatedDate LIKE '2021-08%' 
LEFT JOIN Project_Highlights AS d
ON b.ProjID = d.ProjID
AND 
    d.CreatedDate LIKE '2021-07%' 
LEFT JOIN LastEntries AS f
WHERE
    a.Project_Delivered != 'Yes'
AND
    a.PMO_Board_Report != 'No' 
AND
    a.Status == 'Acknowledged'
AND
    a.InsertDate = f.pl_insert
AND
    b.InsertDate = f.pe_insert
AND
    a.ProjID = f.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;

which results to:
4|4|Activity|PR0000019131
6|6|Project|PR0000019132
6|6|Project|PR0000019155
6|6|Project|PR0000019175
6|6|Project|PR0000019586
2|0|Project|PR0000018388
4|4|Project|PR0000018390
4|4|Project|PR0000018391
0|0|Activity|PR0000019764

I am expecting:
2|2|Activity|PR0000019131
2|3|Project|PR0000019132
2|3|Project|PR0000019155
2|3|Project|PR0000019175
2|3|Project|PR0000019586
2|0|Project|PR0000018388
2|2|Project|PR0000018390
2|2|Project|PR0000018391
0|0|Activity|PR0000019764

I know it's something simple, again, but I can't seem to put my finger on the problem. Any thoughts? Thanks.

josé

20:26 Reply: LEFT JOIN Misunderstanding (artifact: 212c4c3b48 user: jicman)

I agree and also thank you for the examples.

jos♪

2021-08-04
15:32 Reply: LEFT JOIN Misunderstanding (artifact: 6e559baf66 user: jicman)

Hi Rico.

The extra constraints in the WHERE will affect rows returned by this query. That may be ok for you but it makes the joins not purely left.

I don't understand. Where can those constraints go? That is the only spot where they can be place, correct?

2021-07-30
16:13 Reply: LEFT JOIN Misunderstanding (artifact: 8ae4d29967 user: jicman)

This is for my own search in the future, this works:

WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid)
    FROM project_list e
    GROUP BY projid
 )
SELECT count(c.CreatedDate), a.Status, a.ProjID, a.Project_Name,  a.Manager
FROM Project_List AS a 
LEFT JOIN Project_Extras AS b
ON a.ProjID == b.ProjID
AND
    b.MonthlyRpt = 'y'
AND
    b.BudgetYear = '2021'
LEFT JOIN Project_Highlights AS c
ON b.ProjID = c.ProjID
AND 
	c.CreatedDate LIKE '2021-07-%' 
LEFT JOIN LastEntries AS d
WHERE
	a.Status == 'Acknowledged'
AND
    a.InsertDate = d.pl_insert
AND
    b.InsertDate = d.pe_insert
AND
	a.ProjID = d.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;

thanks for all the help.

josé

13:42 Reply: LEFT JOIN Misunderstanding (artifact: 73a99b46ed user: jicman)

The one you refer still gives me the same result as the original. The second one gives me more, but I can see that I still need to filter a few more things.

13:35 Reply: LEFT JOIN Misunderstanding (artifact: 29a0dc20f8 user: jicman)

This one works. Thanks so much. So, group the conditions with the ON. Learned something new. Again. :-) Thanks.

13:33 Reply: LEFT JOIN Misunderstanding (artifact: e5680a6502 user: jicman)

This one still gives me the same result...

12:04 Reply: LEFT JOIN Misunderstanding (artifact: fc2d3e8487 user: jicman)

Thanks, Rico, but I don't think this is the problem. SQLite v3.35.2 does the same thing. Thanks.

02:35 Post: LEFT JOIN Misunderstanding (artifact: 3b587713f8 user: jicman)

Greetings.

The following code is working partially:

WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
  SELECT projid,
         max(InsertDate),
         (SELECT max(insertdate) FROM project_extras where projid = e.projid)
    FROM project_list e
    GROUP BY projid
 )
SELECT a.ProjID, a.Project_Name, count(c.CreatedDate), a.Manager
FROM Project_List AS a 
LEFT JOIN Project_Extras AS b
LEFT JOIN Project_Highlights AS c
LEFT JOIN LastEntries AS d
WHERE
    c.CreatedDate LIKE '2021-07-%' 
AND
    b.MonthlyRpt = 'y'
AND
    b.BudgetYear = '2021'
AND
    a.InsertDate = d.pl_insert
AND
    b.InsertDate = d.pe_insert
AND a.ProjID = b.ProjID 
AND a.ProjID IS c.ProjID
AND a.ProjID = d.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;

But I am expecting 108 records, and I am only getting 13. The reason is that the Project_Highlights table does not have all the ProjIDs that Project_List has. However, I am trying to show those ProjIDs in Project_List also with a count of 0. I know what I am missing is simple, but, I have searched a bunch of places trying to understand the reasoning behind it. I know it's because c.ProjID is NULL, but I am missing the way to have it worked. Any input would be greatly appreciated. Thanks.

2021-07-23
15:08 Reply: Random crash doing SELECT (artifact: bca162b9fa user: jicman)

Hi. You haven't provided much information, so, from what I can quickly come up to debug your problem would be to try to print to a text file or the screen output what is the content of:

"SELECT "tile_blob" FROM "tiles_table"n WHERE "tile_meta" = @tile_meta AND "tile_id" = @tile_id;"

Once you get that, use the sqlite3 tool and connect to the DB and try pasting that result on the tool and see if you get a result, or if it also crashes. Other than this, I don't know how else to help you.

josé

13:46 Reply: Parse failure doing UPDATE FROM (artifact: 69c49867fc user: jicman)

I am no expert, but I have figured out that if I am going to do anything with a tool, I would use the latest version of the tool, and then go to previous version if the need is there. :-) Just thinking out loud. :-)

josé

2021-07-17
23:42 Reply: ORDER BY not ordering with the correct result (artifact: 558b42baa6 user: jicman)

And it's also a great teaching tool for me. :-) Now I know that I can do this with a bunch of queries that are written with lots of weaknesses. I also was able to understand your syntax and setup of the various pieces of the puzzle. Thanks again for your wonderful work on this forum.

josé

23:30 Reply: ORDER BY not ordering with the correct result (artifact: e0c8c946ae user: jicman)

Wow! How do you come up with these SQL combinations? Someday I will be like you. :-)

I did made these changes to the final select to make it work,

     )                                       -- took the comma out
   select a.*,                               -- replace Proj_List with a
          sum(b.AnnualDossier) as Dossier    -- added a b. in the sum function
     from Proj_List as a
left join Proj_Budget as b
       on a.ProjID == b.ProjID
 group by a.ProjID
 order by a.Project_Manager
;

and it worked. But it looks like they both have the same speed. This is the expert result for the last one you provided:


(no new indexes)

MATERIALIZE Proj_Budget
SEARCH o USING COVERING INDEX sqlite_autoindex_ABT_Budget_1 (ProjID=?)
LIST SUBQUERY 29
MATERIALIZE ProjIDs
SCAN 25 CONSTANT ROWS
SCAN ProjIDs
CORRELATED SCALAR SUBQUERY 30
SEARCH ABT_Budget USING COVERING INDEX ABT_Budget_idx_9bd20c76 (ProjID=?)
SEARCH o USING INDEX PL_ProjID (ProjID=?)
LIST SUBQUERY 26
SCAN ProjIDs
CORRELATED SCALAR SUBQUERY 27
SEARCH Project_List USING COVERING INDEX Project_List_idx_292415b1 (ProjID=?)
SEARCH b USING AUTOMATIC COVERING INDEX (ProjID=?)
USE TEMP B-TREE FOR ORDER BY

and this is for the previous one with your suggested fix:

(no new indexes)

SEARCH a USING INDEX PL_ProjID (ProjID=?)
LIST SUBQUERY 27
CO-ROUTINE ProjIDs
SCAN 25 CONSTANT ROWS
SCAN ProjIDs
CORRELATED SCALAR SUBQUERY 28
SEARCH Project_List USING COVERING INDEX Project_List_idx_292415b1 (ProjID=?)
SEARCH b USING COVERING INDEX PAB_ProjID_AnnualDossier_InsertDate (ProjID=?)
CORRELATED SCALAR SUBQUERY 26
SEARCH ABT_Budget USING COVERING INDEX ABT_Budget_idx_9bd20c76 (ProjID=?)
USE TEMP B-TREE FOR ORDER BY

Don't really understand this well, but which one is better? :-)

22:43 Reply: ORDER BY not ordering with the correct result (artifact: 4a99bba335 user: jicman)

So this is your query (reformatted for eye-bleeding prevention):

Thanks for the rearranging. Much cleaner and easier to the eyes. :-)

[clip]

b.InsertDate = (b.InsertDate is NULL)

What is the "intention" of this code as it seems somewhat confusing?

This is because if there are no result from total(b.AnnualDossier), in other words, the ProjID does not exists in the table b (ABT_Budget), then that record won't show in the result. So, with this assignment, the result has all the records listed in the WITH part of the code.

Of course, if what you mean is that you want b.InsertDate to be the max(InsertDate) for that project (as in correlated) then it should be b.InsertDate = (select max(insertdate) from abt_budget where ProjId = b.ProjID).

Yes, you're right. Thanks. I found out what it was not sorting (spaces in front the data). But, this is also much faster by using your suggested changes.

20:39 Post: ORDER BY not ordering with the correct result (artifact: 70de1e3781 user: jicman)

Greetings.

I have this SQL code,

WITH ProjIDs AS (VALUES 
   ('PR0000016675'),
   ('PR0000017285'),
   ('PR0000017416'),
   ('PR0000019109'),
   ('PR0000019129'),
   ('PR0000019130'),
   ('PR0000019131'),
   ('PR0000019133'),
   ('PR0000019135'),
   ('PR0000019136'),
   ('PR0000019139'),
   ('PR0000019140'),
   ('PR0000019141'),
   ('PR0000019142'),
   ('PR0000019143'),
   ('PR0000019147'),
   ('PR0000019148'),
   ('PR0000019150'),
   ('PR0000019152'),
   ('PR0000019164'),
   ('PR0000019176'),
   ('PR0000019180'),
   ('PR0000019186'),
   ('PR0000019191'),
   ('PR0000019764')
)
SELECT a.*,total(b.AnnualDossier) as Dossier FROM Project_List AS a 
LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID 
AND b.InsertDate = 
  ( 
     SELECT MAX(InsertDate) FROM ABT_Budget WHERE b.ProjID IN ProjIDs 
     OR 
     b.InsertDate IS NULL 
  ) 
WHERE a.ProjID IN ProjIDs 
AND a.InsertDate = 
( 
   SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID  
) 

  GROUP BY a.ProjID
  ORDER BY a.Project_Manager;

But I am not getting a sorted by Project_Manager. What am I doing wrong? Thanks for the support.

josé

2021-07-12
20:12 Reply: Awesome people (artifact: ed7e88cee1 user: jicman)

I second that. Igor was instrumental in my early SQL days. Thanks all.

josé

2021-07-02
13:41 Edit: Very Newbie Question (artifact: 65071df38e user: jicman)

I have this SQL that is not working:

sqlite> select 00 AS A1, 0 AS A2, A1 + A2 AS TA;
Error: no such column: A1

This one works,

sqlite> select 00 AS A1, 0 AS A2;
A1|A2
0|0

I know it's easy, but I want to be able to add A1 and A2, but I can't seem to find a way. Thanks.

josé

13:30 Reply: Very Newbie Question (artifact: c8ac3ad76a user: jicman)

... Could you please edit your post accordingly?...

Call me genie. I have granted your wish. :-)

josé

13:27 Edit: Very Newbie Question (artifact: bf6d54be06 user: jicman)

I have this SQL that is not working:

sqlite> select 00 AS A1, 0 AS A2, A1 + A2 AS TA;
Error: no such column: A1

This one works,

sqlite> select 00 AS A1, 0 AS A2;
A1|A2
0|0

I know it's easy, but I want to be able to add A1 and A2, but I can't seem to find a way. Thanks.

josé

13:23 Reply: Very Newbie Question (artifact: 978ce4724b user: jicman)

It would be very nice if you stated what your question is about instead of classifying it as a "newbie question".

You're right. My apologies for the bad title. I wonder if I can change the title. And, furthermore, how long am I going to keep assigning myself the title of newbie? :-)

2021-06-29
19:34 Reply: Very Newbie Question (artifact: e1910d2b0b user: jicman)

Darn it! I knew is was something so simple. Thanks, Larry.

josé

19:06 Post: Very Newbie Question (artifact: 70b79f180b user: jicman)

I have this SQL that is not working:

sqlite> select 00 AS A1, 0 AS A2, A1 + A2 AS TA;
Error: no such column: A1

This one works,

sqlite> select 00 AS A1, 0 AS A2;
A1|A2
0|0

I know it's easy, but I want to be able to add A1 and A2, but I can't seem to find a way. Thanks.

josé

2021-06-28
20:19 Reply: Will SQLite support GQL or SQL/PGQ in the future? (artifact: d752edabe4 user: jicman)

I look forward to seeing your implementation.

Love that answer. :-)

2021-06-03
22:41 Post: Typo on the draft release notes? (artifact: 07d3668d82 user: jicman)

Greetings.

The last bullet of the draft release notes says,

The REGEXP extension is no included in CLI builds.

Perhaps it should say,

The REGEXP extension is now included in CLI builds.

Just thought it did not look right. Thanks.

josé

2021-05-06
15:55 Reply: User Defined Functions (artifact: d4deebf85a user: jicman)

Thanks, Keith. I am laughing so hard here. This is great! Keep it going. :-)

2021-04-23
15:46 Reply: sqlite.org/draft shows the same data as sqlite.org (artifact: ec2563780e user: jicman)

Thanks.

12:35 Post: sqlite.org/draft shows the same data as sqlite.org (artifact: 6a6b942f58 user: jicman)

Greetings.

I have always used https://sqlite.org/draft to see what's coming. Lately, I have been doing it, and I am seeing the same data that I see on https://sqlite.org. Is this the new behavior? Thanks.

josé

2021-04-12
14:07 Reply: Only Column Names read from table (artifact: 9add3c3898 user: jicman)

Have you tried,

PRAGMA table_info(tablename);

that is how I grab all of my columns names from a table.

sqlite> PRAGMA table_info(Business_OBS_List);
0|Bus_OBS||0||1
1|Bus_Area||0||0
2|Bus_Org||0||0

Maybe I am misunderstanding your question.

2021-03-29
13:09 Reply: Novice question about running UTF-8 on CLI from file. (artifact: 8f5d5298e6 user: jicman)

(..., but I love Notepad, it's light and minimalist, one step removed from a terminal, but also lacking in features. It's so very unlike the rest of Windows.)

I don't care what anybody says, but that statement above is funny. :-)

2021-03-25
13:39 Reply: Error: no such column: x (artifact: 0dc5949bcd user: jicman)

Thanks, Keith. I know you probably don't remember this one, but I was able to get this one to work with DISTINCT and it is the fastest yet: 2.34 secs loading. Thanks, Obrigado, gracias, Danke, etc.

josé

2021-03-24
20:16 Reply: Thousands of SQLite Databases for each user (artifact: 3e1cdcba27 user: jicman)

Has anyone had experience with something like this? I don't see why it would be hard to scale as it is just singular files that can be easily replicated.

So, I am thinking that you would have some SharedDB for all users to keep track of sharable data, etc. If it's only reads, it should be ok, if you are talking about reading and writing, that would be tough. Back in 2006 I started a project tracking program with 11 PMs, each with their own DB, plus one shared DB that would update after every PM save. After multiple broken SharedDB, and lots of code changes checking for DB locks, it worked. But, that was only 11 users. And I should say that, sometimes, they would try to write at the same time and the write would time out, so it will try again. PRAGMA busy_timeout will be your friend. Again, that was only with 11 PMs. 1000+ users? I don't think it will work. Imagine if 100 of them write to the DB at the same time. Anyway, I may have all of this wrong, but that is the input I can give. IHTH.

josé

14:28 Reply: Help rewriting a query to match the speed of a 2nd query (artifact: 7cb60a51af user: jicman)

If you mean just without having to type/bind the values twice, then how about

Thanks. The WITH is so elegant.

josé

13:39 Post: Help rewriting a query to match the speed of a 2nd query (artifact: a5711a3943 user: jicman)

Greetings!

This query is very slow:

SELECT a.ProjID, total(b.AnnualDossier) AS Dossier FROM Project_List AS a 
LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
AND b.InsertDate = 
(
    SELECT MAX(InsertDate) FROM ABT_Budget WHERE b.ProjID = ProjID
    OR
    b.InsertDate IS Null
)
WHERE a.ProjID IN 
(
           'PR0000014888',
           'PR0000015204',
           'PR0000016639',
           'PR0000016678',
           'PR0000016724'
)
AND a.InsertDate = 
(
    SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID
)
GROUP BY a.ProjID;
PR0000014888|0.0
PR0000015204|0.0
PR0000016639|0.0
PR0000016678|24939.539891
PR0000016724|0.0
Run Time: real 18.511 user 5.859375 sys 12.593750
sqlite>

while this one is super fast:

SELECT a.ProjID, total(b.AnnualDossier) AS Dossier FROM Project_List AS a 
LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
AND b.InsertDate = 
(
    SELECT MAX(InsertDate) FROM ABT_Budget  
    WHERE b.ProjID IN 
    (
               'PR0000014888',
               'PR0000015204',
               'PR0000016639',
               'PR0000016678',
               'PR0000016724'
    )
    OR
    b.InsertDate IS Null
)
WHERE a.ProjID IN 
(
           'PR0000014888',
           'PR0000015204',
           'PR0000016639',
           'PR0000016678',
           'PR0000016724'
)
AND a.InsertDate = 
(
    SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID
)
GROUP BY a.ProjID;
PR0000014888|0.0
PR0000015204|0.0
PR0000016639|0.0
PR0000016678|0.0
PR0000016724|0.0
Run Time: real 0.032 user 0.015625 sys 0.000000

Programmatically, I can do this easily, but is there a way to rewrite the second without the the duplication of IN groups? Thanks.

josé

2021-03-17
14:21 Reply: Missing records from the IN list (artifact: 611ec72a6b user: jicman)

I was able to rewrite the query and this one works fine:

SELECT a.ProjID, total(b.AnnualDossier) AS Dossier FROM Project_List AS a 
LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
AND b.InsertDate = 
(
    SELECT MAX(InsertDate) FROM ABT_Budget WHERE b.ProjID = ProjID
    OR
    b.InsertDate IS Null
)
WHERE a.ProjID IN 
(
  'PR0000019149','PR0000018256','PR0000018262','PR0000019185','PR0000019187'
)
AND a.InsertDate = 
(
    SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID
)
GROUP BY a.ProjID;
PR0000018256|0.0
PR0000018262|0.0
PR0000019149|0.0
PR0000019185|0.0
PR0000019187|0.0

The .expert command suggests an INDEX, which I am going to add:

sqlite> .expert
CREATE INDEX ABT_Budget_idx_2cd87472 ON ABT_Budget(InsertDate DESC);

Which I am planning to add. Thanks, Keith.

josé

2021-03-16
20:13 Reply: Missing records from the IN list (artifact: 602ad503f8 user: jicman)

Also note that you can use TOTAL(...) rather than COALESE(SUM(...),0).

TOTAL is the same as sum except that it always returns a double with a default value of 0.0.

Thanks. Didn't know that. (So much to read and learn, and yet do my work. :-))

19:25 Reply: Missing records from the IN list (artifact: b8c9133b2a user: jicman)

Are you sure these should be correlated and not direct references to the parent:

That is probably my problem. I have tried to rewrite it, but I still end up missing the 3. Let me try to rewrite it again. Back to the drawing board. :-)

Thanks, Keith.

18:30 Post: Missing records from the IN list (artifact: d1d89ca85f user: jicman)

Greetings.

The following query is missing 3 records. I know that it's probably because the LEFT JOIN does not have those records, but I have added a COALESCE to arrange for those missing records. Any ideas how to get this to work?

SELECT a.ProjID, COALESCE(sum(b.AnnualDossier), 0.00) AS Dossier FROM Project_List AS a 
LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
AND
(
   b.InsertDate = 
   (
       SELECT MAX(InsertDate) FROM ABT_Budget WHERE b.ProjID = ProjID
   )
   OR
       b.InsertDate IS Null
)
WHERE a.ProjID IN 
(
   SELECT ProjID FROM Project_List WHERE 1=1 
  AND
  (
     a.ProjID IN ('PR0000019149','PR0000018256','PR0000018262','PR0000019185','PR0000019187')
  )  
  AND InsertDate = 
  ( 
     SELECT MAX(InsertDate) FROM Project_List WHERE 1=1 
     AND 
     (
        a.ProjID IN ('PR0000019149','PR0000018256','PR0000018262','PR0000019185','PR0000019187')
     )   
  ) 
)
AND a.InsertDate = 
      (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID) 
  GROUP BY a.ProjID;

PR0000018256|0.0
PR0000018262|0.0
sqlite>

Thanks for your support.

josé

More ↓