Databases
StackOverflow2013
Postsdbo
POT-SQL needing to select results using 3 parameters and if any one doesnt exist in data correct inform user which one
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL needing to select results using 3 parameters and if any one doesnt exist in data correct inform user which one
    text
    copied!<p>I am looking to select from three columns using three parameters, one for each column. If all the parameters are present for the columns then it will return a fourth column. If, however, any one of the three parameters is not found in the table, then it should return a message informing the user which of the parameters is incorrect.</p> <p>I have tried the following:</p> <pre><code> SELECT RESULTS, "[status]" = case when CostCenterNo &lt;&gt; '800' then 'CentreNotFound' when EmpNo &lt;&gt; '2' then 'EmpNotFound' when Surname &lt;&gt; 'sonny' then 'SurnameNotFound' else null end from CostCentres where CostCenterNo = case CostCenterno when '800' then '800' else '' end and EmpNo = case EmpNo when '2' then '2' else '' end and Surname = case Surname when 'sonny' then 'sonny' else '' end </code></pre> <p>this retrieves the correct information when all parameters are correct but then I need for it to say CentreNotFound, EmpNotFound, SurnameNotFound for when the respective parameter is not found in the table. I have tried looking up the links below but still no luck. <a href="https://stackoverflow.com/questions/16422044/if-statement-in-sql-server-where-clause">IF statement in SQL Server where clause</a></p> <p><a href="https://stackoverflow.com/questions/18787453/using-exists-as-a-column-in-tsql">Using EXISTS as a column in TSQL</a></p> <p><a href="https://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause">SQL: IF clause within WHERE clause</a></p> <p>I have also tried the following code:</p> <pre><code> select results = case when CostCenterNo = '800' then case when EmpNo = '2' then case when Surname = 'sonny' then (select results from bcse where CostCenterNo = 'BW800' and EmpNo like '2' and Surname = 'sonny') else 'surname not found' end else 'Emp not found' end else 'center no not found' end from CostCentres where (CostCenterno = 'bw800' or CostCenterNo = '%') and (EmpNo = '2' or EmpNo = '%' ) and (surname = 'sonny' or surname = '%') </code></pre> <p>The above works for the correct parameters but then again I need it to return the CentreNotFound, EmpNotFound, SurnameNotFound for when the respective parameter is not found in the table.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload