SQL: Multiple "WHERE" Clauses - can I dynamically extract where one or many have not been-CodePudding

This is my first post here, not to say I don't come here for most of my questions! Sadly I couldn't find an existing answer to this!

SQL is not my strong point, but I can generally get by. I am running a query as a sort of validation against known data held in a tactical database vs data entered on a form.

The expected results are

validAccount = TRUE or FALSE errorMsg = NULL or Defined error based on which WHERE clause(s) have not been met

This is my existing statement and I have been able to define an error message for each single WHERE clause if it isn't met, but I'd like to be able to loop over the whole statement and determine if there's more than one "fail". Is it possible? I'd love a pointer to some guidance on how to achieve this! And also would be keen to learn if there's a better way of achieving what I already have below - it kind of works, but doesn't feel very elegant!

SELECT CASE WHEN EXISTS (
    SELECT *
    FROM customer_database_dummy
    WHERE 
    
    account_reference =  {accNumber}
    and replace(replace(name,' ',''),'.','') = replace(replace({Rent_Full_Name},' ',''),'.','')
    and period_start_date = {DateOfBirth_f}
    and replace(postcode,' ','') =  replace({PostcodeUpper},' ','') 
    and property_code not in ('Shed','Garage','Parking Space')
    and (tenancy_end_date > getdate() or tenancy_end_date is null)

)
--VALID RESULT
THEN  'TRUE'

--INVALID RESULT HANDLING
ELSE   'FALSE'  END as validAccount,

--ACC_NOT_FOUND
CASE WHEN NOT EXISTS (
    SELECT *
    FROM rent_account_customer_summary
    WHERE 
    
    account_reference = {accNumber})
THEN 'ACC_NOT_FOUND'

--NAME_MISMATCH
WHEN EXISTS (
    SELECT *
    FROM rent_account_customer_summary
    WHERE 
    
    account_reference =  {accNumber}
    and replace(replace(name,' ',''),'.','') != replace(replace({Rent_Full_Name},' ',''),'.',''))
THEN  'NAME_MISMATCH' 

--DOB_MISMATCH
WHEN EXISTS (
    SELECT *
    FROM rent_account_customer_summary
    WHERE 
    
    account_reference =  {accNumber}
    and period_start_date != {DateOfBirth_f})
THEN 'DOB_MIMSMATCH'

--POSTCODE_MISMATCH#
WHEN EXISTS (
    SELECT *
    FROM rent_account_customer_summary
    WHERE 
    
    account_reference =  {accNumber}
    and replace(postcode,' ','') !=  replace({PostcodeUpper},' ',''))
THEN 'POSTCODE_MISMATCH'

--PROPERTY_TYPE_INVALID
WHEN EXISTS (
    SELECT *
    FROM rent_account_customer_summary
    WHERE 
    
    account_reference =  {accNumber}
    and property_code in ('Shed','Garage','Parking Space'))
THEN 'PROPERTY_TYPE_INVALID'
    
--TENANCY_ISSUE
WHEN EXISTS (
    SELECT *
    FROM rent_account_customer_summary
    WHERE 
    
    account_reference =  {accNumber}
    and (tenancy_end_date < getdate() or tenancy_end_date != null))
THEN 'TENANCY_ISSUE'


ELSE   ''  END as errorMsg

CodePudding user response：

Assuming you use MS SQL Server, here is a possible solution, which I hope you'll find cleaner :-)

SELECT IIF(errorMsg = '', 'TRUE', 'FALSE') AS validAccount, errorMsg
FROM (SELECT {accNumber} AS accNumber) accNumberTable
LEFT JOIN rent_account_customer_summary ON account_reference = accNumber
CROSS APPLY (
  SELECT COALESCE(
    IIF(account_reference IS NOT NULL, NULL, 'ACC_NOT_FOUND'),
    IIF(replace(replace(name,' ',''),'.','') = replace(replace({Rent_Full_Name},' ',''),'.',''), NULL, 'NAME_MISMATCH'),
    IIF(period_start_date = {DateOfBirth_f}, NULL, 'DOB_MISMATCH'),
    IIF(replace(postcode,' ','') =  replace({PostcodeUpper},' ',''), NULL, 'POSTCODE_MISMATCH'),
    IIF(property_code not in ('Shed','Garage','Parking Space'), NULL, 'PROPERTY_TYPE_INVALID'),
    IIF(tenancy_end_date >= getdate() or tenancy_end_date = null, NULL, 'TENANCY_ISSUE'),
    ''
  ) AS errorMsg
) t

The idea is to check if there is an error message for the account number being searched for. We save this error in a "lateral table" (terminology from PostgreSQL), and use this error message to determine if the account is valid, then return the error message itself.

CodePudding user response：

You just need to include all the conditions you are using into the select fields with an IIF statement.

You will have a result with all your fields in your select plus a flag indicating which conditions has passed or failed like:

'all other fields names'  | valid_account | valid_name | ...etc
'all other record values' |      1        |      0     | ...etc

Example code

SELECT *,
    IIF (account_reference =  {accNumber}, 1, 0) AS valid_account,
    IIF (replace(replace(name,' ',''),'.','') = replace(replace({Rent_Full_Name},' ',''),'.',''), 1, 0) AS valid_name,
    IIF (period_start_date = {DateOfBirth_f}, 1, 0) AS valid_start_date,
    IIF (replace(postcode,' ','') =  replace({PostcodeUpper},' ',''), 1, 0) AS valid_post_code,
    IIF (property_code not in ('Shed','Garage','Parking Space'), 1, 0) AS valid_property_code,
    IIF ((tenancy_end_date > getdate() or tenancy_end_date is null), 1, 0) AS valid_tenancy_date
    FROM customer_database_dummy
    WHERE 
    account_reference =  {accNumber}
    and replace(replace(name,' ',''),'.','') = replace(replace({Rent_Full_Name},' ',''),'.','')
    and period_start_date = {DateOfBirth_f}
    and replace(postcode,' ','') =  replace({PostcodeUpper},' ','') 
    and property_code not in ('Shed','Garage','Parking Space')
    and (tenancy_end_date > getdate() or tenancy_end_date is null)