Me too. It seems that the second query doesn't use the field names obtained from the first one, but I think if it does the second select should throw an exception, show an error message.
Voglio dire: sei obbligato a farlo con un'unica query? Non PUOI scrivere codice B4X? Perché la soluzione, con 2 query e poche righe di codice, c'è.
Public Sub OnlyNullFieldNames(TableName As String) As List
Dim lstFieldNames As List
lstFieldNames.Initialize
Dim lstFieldNamesWithNull As List
lstFieldNamesWithNull.Initialize
Dim Query As String
Query = $"SELECT name FROM PRAGMA_TABLE_INFO('${TableName}')"$
'Get the table field names.
Dim RS As ResultSet
RS = mDB.ExecQuery(Query)
Do While RS.NextRow
lstFieldNames.Add(RS.GetString("name"))
Loop
RS.Close
'Count the number of records.
Query = $"SELECT COUNT(*) FROM '${TableName}'"$
Dim RecordsCount As Int
RecordsCount = mDB.ExecQuerySingleResult(Query)
'Search for "full null" fields.
For Each FieldName As String In lstFieldNames
Query = $"SELECT Count(*) FROM '${TableName}' WHERE '${FieldName}' IS NULL"$
Dim Count As Int = mDB.ExecQuerySingleResult(Query)
If Count = RecordsCount Then
lstFieldNamesWithNull.Add(FieldName)
End If
Next
Return lstFieldNamesWithNull
End SubI think you need to change this line by removing the single quotes from:
Query = $"SELECT Count(*) FROM '${TableName}' WHERE '${FieldName}' IS NULL"$Query = $"SELECT Count(*) FROM '${TableName}' WHERE ${FieldName} IS NULL"$Yes, I should have used square brackets (although it would be better for the names to have no spaces or worse)
I assumed so, having seen various posts (without looking "in depth").
The differences from the source in post #9 are that the one I posted doesn't iterate all fields that might have a Null value and that it works on any table, whose name is passed as a parameter.
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ART_ANA'
AND COLUMN_NAME NOT IN (
SELECT COLUMN_NAME
FROM dbo.ART_ANA
WHERE COLUMN_NAME IS NOT NULL
)Sub OnlyNullFieldNames(TableName As String) As List
'Count the number of records in db table
Dim Query As String
Query = $"SELECT COUNT(*) FROM '${TableName}'"$
Dim RecordsCount As Int
RecordsCount = SQL.ExecQuerySingleResult(Query)
Dim lstFieldNamesWithNull As List
lstFieldNamesWithNull.Initialize
Query = $"SELECT name FROM PRAGMA_TABLE_INFO('${TableName}')"$
'Get the table field names and iterate over them
Dim rs As ResultSet
rs = SQL.ExecQuery(Query)
Do While rs.NextRow
Query = $"SELECT Count(*) FROM '${TableName}' WHERE ${rs.GetString("name")} IS NULL OR ${rs.GetString("name")} ='' "$
Dim Count As Int = SQL.ExecQuerySingleResult(Query)
If Count = RecordsCount Then
lstFieldNamesWithNull.Add(rs.GetString("name"))
End If
Loop
rs.Close
Return lstFieldNamesWithNull
End SubYou don't need the single quote. If you use the code sub I have in post #52, it will work for you every time. To see which columns have all NULL or empty string, do this:
For Each f As String In OnlyNullFieldNames("AAA_TEST_NULL")
Log($"Cols with all NULL/empty rows: ${f}"$)
Next
This is an easy question.
How about this SQLite statement which involves 2 simple queries like this without lists and without variables. I just cannot see it done using one simple query the way you want. This is my final answer:
Dim rs1 As ResultSet =SQL.ExecQuery($"Select name
FROM PRAGMA_TABLE_INFO('AAA_TEST_NULL') "$)
Do While rs1.nextrow
Dim rs2 As ResultSet= SQL.Execquery($"SELECT DISTINCT ${rs1.getstring("name")} FROM AAA_TEST_NULL
WHERE ${rs1.getstring("name")} IS NULL
OR ${rs1.getstring("name")} = ''
"$)
Do While rs2.NextRow
Log(rs1.getstring("name"))
Loop
rs2.Close
Loop
rs1.Close
