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

LucaMs said:

Perplexity ?

View attachment 141720

B4X:

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]

Click to expand...

amorosik said:

In attach an example db

Click to expand...

amorosik said:

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

Click to expand...

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)

DonManfred said:

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.

Click to expand...

amorosik said:

The db and the query are the only things needed

Click to expand...

aeric said:

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.

SQL:

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)

Click to expand...

DonManfred said:

good to know. Good luck fixing the problem.

Click to expand...

amorosik said:

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

Click to expand...

aeric said:

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.

Click to expand...

amorosik said:

I have full access to the db

Click to expand...

amorosik said:

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

Click to expand...

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

Mahares said:

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.

B4X:

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

Click to expand...

amorosik said:

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

Click to expand...

LucaMs said:

Perplexity ?

View attachment 141720

B4X:

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]

Click to expand...

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

Mahares said:

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.

Click to expand...

Mahares said:

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.

Click to expand...

LucaMs said:

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

B4X:

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.

Click to expand...

amorosik said:

Wich tools are you using?

Click to expand...

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

LucaMs said:

I will try to update the driver

Click to expand...