B4J Question Query that returns only the column names that have never been evaluated

[amorosik]

I need a query that returns, given the name of a table in the db, all the names of the fields that have never been filled in any row of the data present
Basically the fields I could also delete because at the moment they don't contain any information
How to make a unique query that spits out the above?

 

[amorosik]

Perplexity ?

View attachment 141720

PRAGMA_TABLE_INFO('table_name');
SELECT name FROM PRAGMA_TABLE_INFO('table_name')
WHERE name NOT IN (SELECT name FROM table_name WHERE name IS NOT NULL);

[Note that this solution only works if the table has at least one row. If the table is empty, there is no data from which to determine which columns are filled or not]

In attach an example db
But the query not return PREZZO

 

[DonManfred]

In attach an example db

Upload a small project
- including the complete databasecreate-methods so we could see what you are building here.
- Only the db-file is not of much help.

 

[aeric]

Without know the field name, i can't test for each one
And i know only table name
What i need is to know if is possibile, with a single query and table name, have in return all the field never used (or like 'null') in every row of table

Why you don't know the fields name? Are you restricted for observing the database schema?
Please explain the scenario.
Is the database contains 1000 of tables and each table also contains 1000 of fields?

It is possible to create a single query but I don't think it is worth a try.
I don't see the objective, practical reason or advantages for doing so.
I have no idea what you are trying to achieve or trying to make things more complicated.

SELECT Field FROM (
SELECT Field, CountNull FROM
(SELECT 'descrizione' AS Field, COUNT(*) AS CountNull FROM AAA_TEST_NULL WHERE descrizione IS NULL
UNION
SELECT 'prezzo' AS Field, COUNT(*) AS CountNull FROM AAA_TEST_NULL WHERE prezzo IS NULL))
WHERE CountNull = (SELECT COUNT(*) FROM AAA_TEST_NULL)

 

[amorosik]

Upload a small project
- including the complete databasecreate-methods so we could see what you are building here.
- Only the db-file is not of much help.

The db and the query are the only things needed
Eventually the db manager

 

[DonManfred]

The db and the query are the only things needed

good to know. Good luck fixing the problem.

 

[amorosik]

Why you don't know the fields name? Are you restricted for observing the database schema?
Please explain the scenario.
Is the database contains 1000 of tables and each table also contains 1000 of fields?

It is possible to create a single query but I don't think it is worth a try.
I don't see the objective, practical reason or advantages for doing so.
I have no idea what you are trying to achieve or trying to make things more complicated.

SELECT Field FROM (
SELECT Field, CountNull FROM
(SELECT 'descrizione' AS Field, COUNT(*) AS CountNull FROM AAA_TEST_NULL WHERE descrizione IS NULL
UNION
SELECT 'prezzo' AS Field, COUNT(*) AS CountNull FROM AAA_TEST_NULL WHERE prezzo IS NULL))
WHERE CountNull = (SELECT COUNT(*) FROM AAA_TEST_NULL)

I have full access to the db
The practical reason is to obtain a compact and faster execution system than looping through all the fields of the table to compose the query
I don't understand why you write that you have no idea what I want to achieve, it is correctly described in the initial post
I would like to have a query that, given a table name, returns all the names of the fields that have never been used in the rows within that table

 

[amorosik]

good to know. Good luck fixing the problem.

Thank you very much for the wish

 

[aeric]

The practical reason is to obtain a compact and faster execution system than looping through all the fields of the table to compose the query

How much faster?
If you able save a few milliseconds, that is not significant to justify the time and effort.
Don't underestimate the power of modern CPU. ?

Edit: And don't forget SQLite is lightweight and lightning fast.

 

[amorosik]

How much faster?
If you able save a few milliseconds, that is not significant to justify the time and effort.
Don't underestimate the power of modern CPU. ?

Edit: And don't forget SQLite is lightweight and lightning fast.

Faster than coupled
- code cycle for reading field names
- query for null fields extraction

 

[Mahares]

I have full access to the db

Basically the fields I could also delete because at the moment they don't contain any information

You said that you have full control of the database and that you will be removing all the columns that have all null or empty strings from the table. Therefore , the easiest thing would be to create a new table with the columns that have data from the exisiting table, delete the original table and rename the new table back to the original name. You will have your final table. This probably will not satisfy you, but why do you need a query that shows which columns were never filled.

CREATE TABLE IF NOT EXISTS NewTable AS SELECT rowid AS codice ,Descrizione FROM AAA_TEST_NULL

 

[amorosik]

You said that you have full control of the database and that you will be removing all the columns that have all null or empty strings from the table. Therefore , the easiest thing would be to create a new table with the columns that have data from the exisiting table, delete the original table and rename the new table back to the original name. You will have your final table. This probably will not satisfy you, but why do you need a query that shows which columns were never filled.

CREATE TABLE IF NOT EXISTS NewTable AS SELECT rowid AS codice ,Descrizione FROM AAA_TEST_NULL

Ok and how do you know which fields to use to recreate the new table?

 

[Mahares]

Ok and how do you know which fields to use to recreate the new table?

Since you mentioned that you have full control of the database and you mentioned the use of DB manager, you can open it in DB Manager and easily see which columns contain no data.

 

[LucaMs]

Perplexity ?

View attachment 141720

PRAGMA_TABLE_INFO('table_name');
SELECT name FROM PRAGMA_TABLE_INFO('table_name')
WHERE name NOT IN (SELECT name FROM table_name WHERE name IS NOT NULL);

[Note that this solution only works if the table has at least one row. If the table is empty, there is no data from which to determine which columns are filled or not]

Those are 2 queries; the first one is not need. Also, there is no need of two "IS NOT". The correct query should be:

SELECT name FROM PRAGMA_TABLE_INFO('AAA_TEST_NULL') WHERE name IN (SELECT name FROM AAA_TEST_NULL WHERE name IS NULL)

which, bizarrely, run in B4J produces an error, run in a tool... produces zero results, no field names.

 

[Mahares]

Since the OP is getting so many good ideas and opinions from several knowledgeable members, but has not found his satisfactory solution, I wish Erel can chime in due to his extreme mastery of SQLite and give us his opinion.

 

[amorosik]

Since you mentioned that you have full control of the database and you mentioned the use of DB manager, you can open it in DB Manager and easily see which columns contain no data.

Ok thanks a lot for the help, I hadn't thought of that

 

[amorosik]

Since the OP is getting so many good ideas and opinions from several knowledgeable members, but has not found his satisfactory solution, I wish Erel can chime in due to his extreme mastery of SQLite and give us his opinion.

There is no reason of urgency that can allow the father of B4X systems to be disturbed
Indeed, I hope he occasionally goes to see the wish-list and inserts a feature that is as useful as it is simple to add, which is the configurability of the shortcut keys in the development environments (do a ctrl-shift-B to insert a single breakpoint not you can just bear it)

 

[amorosik]

Those are 2 queries; the first one is not need. Also, there is no need of two "IS NOT". The correct query should be:

SELECT name FROM PRAGMA_TABLE_INFO('AAA_TEST_NULL') WHERE name IN (SELECT name FROM AAA_TEST_NULL WHERE name IS NULL)

which, bizarrely, run in B4J produces an error, run in a tool... produces zero results, no field names.

Wich tools are you using?

 

[LucaMs]

Wich tools are you using?

SQLite Expert Personal.

Query = $"SELECT name FROM PRAGMA_TABLE_INFO('table_name')"$

That query works with the tool, crashes in a B4J project. I will try to update the driver (if i will find it )


P.S. That tool uses:

 

[LucaMs]

I will try to update the driver

Solved with sqlite-jdbc-3.7.2

 

[amorosik]

I don't understand, I've been facing your query for half an hour and I can't figure out where the problem is

SELECT name FROM PRAGMA_TABLE_INFO('AAA_TEST_NULL') WHERE name IN (SELECT name FROM AAA_TEST_NULL WHERE name IS NULL)

I tried the subquery and it returns seven rows correctly
How come it doesn't work?
(I'm using SqlLite-gui latest version)