Sample Header Ad - 728x90

How to conditionally return multiple different columns in sqlite?

0 votes
1 answer
1225 views
I am trying to return multiple columns only if a condition matches, else, return something else. Using Sqlite3, my understanding is that the way to do this is using CASE, so something like this:
SELECT
CASE 
    WHEN EXISTS(SELECT 1 FROM disk_encryption WHERE user_uuid IS NOT "" AND vault_status = 'on' LIMIT 1) then "Passing" else "Failing" 
END AS Status,
'Encryption Enabled' AS Policy;
This all works great, it returns the following:
Status   | Policy
------------------------------
Passing  | Encryption Enabled
Now, I am trying to return contextual data if Status = 'Failing'. For example, if Status = 'Failing', I would like to return the Policy and Status columns, as well as the output of:
SELECT name, type, vault_status FROM disk_encryption;
How would I do this? Here is simplified test data I am working with:
CREATE TABLE disk_encryption(name TEXT, type TEXT, user_uuid TEXT, vault_status TEXT, PRIMARY KEY (name)) WITHOUT ROWID;

INSERT INTO disk_encryption
VALUES ('/dev/disk1s1', 'APFS Encryption', '504', 'on');
Asked by Josh Brower (113 rep)
Oct 21, 2022, 04:18 PM
Last activity: Oct 24, 2022, 11:19 AM