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?

 

[DonManfred]

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

 

[amorosik]

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

 

[aeric]

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

 

[aeric]

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]

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]

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]

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]

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.

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]

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

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

 

[amorosik]

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.

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]

Ok, but is not a single query

How do you like to see the query look like?

 

[stevel05]

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

 

[aeric]

Try:

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]

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

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]

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:

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

 

[amorosik]

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

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)]

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.

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]

Perplexity

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 be, but actually not function to me

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)