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

amorosik · May 3, 2023

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?

DonManfred · May 3, 2023

This is the english part of the forum. Please write - at least - english here.
Alternatively you can use the italian forum.

amorosik · May 3, 2023

Yes of course, I'm sorry, I wasn't paying attention

aeric · May 3, 2023

Even though the first post has been edited to English, I am still don't understand what you want to do.

aeric · May 3, 2023

You mean you have a table
e.g tbl_population
that you want to query.
You also have another table or list
e.g tbl_selected
Inside tbl_population you have all the data or information while in tbl_selected contains the rows of countries you have added.
You want to query the data in tbl_population where the data contains the countries "not yet" added into tbl_selected.
Am I guess correctly?

amorosik · May 3, 2023

aeric said:
Even though the first post has been edited to English, I am still don't understand what you want to do.

I have a db with one table, named CUSTOMERS
This table contain tre field, Code, Name, Note
In the table there are 2 row, like these:
-----------------------------
Code, Name, Note
1, , Note for expert
2, ,Note for novice
-----------------------------
The question is: how to write a query that extract the name of field never used?
(in the example the query must return 'Name')

amorosik · May 3, 2023

aeric said:
You mean you have a table
e.g tbl_population
that you want to query.
You also have another table or list
e.g tbl_selected
Inside tbl_population you have all the data or information while in tbl_selected contains the rows of countries you have added.
You want to query the data in tbl_population where the data contains the countries "not yet" added into tbl_selected.
Am I guess correctly?

No, see the example above at #6

aeric · May 3, 2023

amorosik said:
I have a db with one table, named CUSTOMERS
This table contain tre field, Code, Name, Note
In the table there are 2 row, like these:
-----------------------------
Code, Name, Note
1, , Note for expert
2, ,Note for novice
-----------------------------
The question is: how to write a query that extract the name of field never used?
(in the example the query must return 'Name')

You mean the value return from the query is empty string?

Mahares · May 3, 2023

amorosik said:
The question is: how to write a query that extract the name of field never used?
(in the example the query must return 'Name')

This code iterates over the TEXT type columns and displays in a list the column names where their data is empty string. Assumes if the given column first row is empty, then rest of the rows for that same col have also empty strings, hence LIMIT 1.

B4X:

Dim l As List
    l.Initialize
    Dim rs As ResultSet =SQL.ExecQuery2($"SELECT * FROM CUSTOMERS WHERE
    Name =?
    or Note = ? LIMIT 1"$, Array As String("",""))    'assumes if the column first row is empty, then rest of the rows have also empty strings
    If rs.NextRow Then
        If rs.GetString2(1) =""  Then
'            Log(rs.GetColumnName(1))
            l.Add(rs.GetColumnName(1))
        End If
        If rs.GetString2(2) ="" Then
'            Log(rs.GetColumnName(2))
            l.Add(rs.GetColumnName(2))
        End If
    Else
        Log("All table columns have data")
    End If
    rs.Close
    For Each s As String In l
        Log(s)  'displays all the column names where the data is empty strings
    Next

Perhaps there are more elegant ways.

amorosik · May 3, 2023

aeric said:
You mean the value return from the query is empty string?

If field has never been used, probably contains a 'null' value

amorosik · May 3, 2023

Mahares said:
This code iterates over the TEXT type columns and displays in a list the column names where their data is empty string. Assumes if the given column first row is empty, then rest of the rows for that same col have also empty strings, hence LIMIT 1.

B4X:

Dim l As List l.Initialize Dim rs As ResultSet =SQL.ExecQuery2($"SELECT * FROM CUSTOMERS WHERE Name =? or Note = ? LIMIT 1"$, Array As String("","")) 'assumes if the column first row is empty, then rest of the rows have also empty strings If rs.NextRow Then If rs.GetString2(1) ="" Then ' Log(rs.GetColumnName(1)) l.Add(rs.GetColumnName(1)) End If If rs.GetString2(2) ="" Then ' Log(rs.GetColumnName(2)) l.Add(rs.GetColumnName(2)) End If Else Log("All table columns have data") End If rs.Close For Each s As String In l Log(s) 'displays all the column names where the data is empty strings Next

Perhaps there are more elegant ways.

Ok, but is not a single query

Mahares · May 3, 2023

amorosik said:
Ok, but is not a single query

How do you like to see the query look like?

stevel05 · May 4, 2023

Sorry, deleted my response, just re-read the question.

aeric · May 4, 2023

Try:

SQL:

SELECT Field FROM(
SELECT Field, CountNullName, COUNT(*) AS CountAll FROM CUSTOMERS, (SELECT 'Name' AS Field, COUNT(*) AS CountNullName FROM CUSTOMERS WHERE "Name" IS NULL) AS B)
WHERE CountNullName = CountAll

aeric · May 4, 2023

If you want to check for both column "Name" and "Note", try:

SQL:

SELECT Field FROM(
SELECT Field, CountNull FROM
(SELECT 'Name' AS Field, COUNT(*) AS CountNull FROM CUSTOMERS WHERE "Name" IS NULL
UNION
SELECT 'Note' AS Field, COUNT(*) AS CountNull FROM CUSTOMERS WHERE "Note" IS NULL))
WHERE CountNull = (SELECT COUNT(*) FROM CUSTOMERS)

DarkoT · May 4, 2023

amorosik said:
I have a db with one table, named CUSTOMERS
This table contain tre field, Code, Name, Note
In the table there are 2 row, like these:
-----------------------------
Code, Name, Note
1, , Note for expert
2, ,Note for novice
-----------------------------
The question is: how to write a query that extract the name of field never used?
(in the example the query must return 'Name')

Maybe I don't understand the question but I suggest to use IFNULL function in SqlLite - like this:

SQL:

select *
from Customers
where Ifnull(Name,'') = ''

amorosik · May 4, 2023

aeric said:

If you want to check for both column "Name" and "Note", try:

SQL:

SELECT Field FROM(
SELECT Field, CountNull FROM
(SELECT 'Name' AS Field, COUNT(*) AS CountNull FROM CUSTOMERS WHERE "Name" IS NULL
UNION
SELECT 'Note' AS Field, COUNT(*) AS CountNull FROM CUSTOMERS WHERE "Note" IS NULL))
WHERE CountNull = (SELECT COUNT(*) FROM CUSTOMERS)

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

Andrew (Digitwell) · May 4, 2023

Assuming you are using SQLLite there is no single query which will do this.

It is possible to get the columns of a table only knowing the table name.

B4X:

SELECT 1 FROM PRAGMA_TABLE_INFO('your_table')

See this stackoverflow. https://stackoverflow.com/questions/947215/how-to-get-a-list-of-column-names-on-sqlite3-database

However, you then have to use this information using one of the queries above to check if the fields are null.

LucaMs · May 4, 2023

Perplexity ?

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]

amorosik · May 4, 2023

May be, but actually not function to me

B4X:

SELECT name
FROM pragma_table_info('AAA_TEST_NULL')
WHERE name not IN
  (SELECT DISTINCT name
   FROM AAA_TEST_NULL
   WHERE name is not null)

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

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Active Member

Expert

Well-Known Member

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency