0

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'); 

1 Answer 1

1

The number of colums in the result cannot be altered within the query depending on the results of the query. What you can do is adding a third column (eg. Info) which will be empty when status='Passing' and populated when status='Failing'.

Content of the Info column can be a concatenation of values from the subquery.

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,
CASE 
    WHEN EXISTS(SELECT 1 FROM disk_encryption WHERE user_uuid IS NOT "" AND vault_status = 'on' LIMIT 1)
  THEN ''
  ELSE (SELECT name ||'-'|| type ||'-'|| vault_status FROM disk_encryption)
END AS Info;


Status |Policy            |Info
-----------------------------------------------------------
Failing|Encryption Enabled|/dev/disk1s2-APFS Encryption-off
1
  • This is great! I was playing around with CTEs, but this is much simpler. Nov 2, 2022 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.