0

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.

I have tried the following:

    SELECT RESULTS, "[status]" =
case
    when CostCenterNo <> '800' then 'CentreNotFound'
    when EmpNo <> '2' then 'EmpNotFound'
    when Surname <> '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

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. IF statement in SQL Server where clause

Using EXISTS as a column in TSQL

SQL: IF clause within WHERE clause

I have also tried the following 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 = '%')

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.

Community
  • 1
  • 1
Mad. 3iGroup
  • 1
  • 1
  • Can you use a stored procedure ?? – M.Ali Nov 06 '13 at 14:42
  • Why are you using a where clause if you are returning all rows? I feel like i'm missing something... – Daniel E. Nov 06 '13 at 15:03
  • Muhammed, using a stored procedure is another option. How would I best structure it though? Daniel, the intention is not to return all rows. We need to limit the result to only one row and it should either be 1) the correct response from the table when all parameters are correct or 2) the error message informing the user which parameter is not found in the table. Thanks – Mad. 3iGroup Nov 06 '13 at 15:18
  • What if multiple params are missing? – Daniel E. Nov 06 '13 at 16:10
  • Hi Daniel, if multiple params are missing it would be great to inform about all of them, but alas, the interface only accommodates one result (is it possible concatenate the error messages to show all the incorrect params?), but I think we can work on a hierarchy system where the params are evaluated successively with one after the other and returning the first error that it comes across. – Mad. 3iGroup Nov 06 '13 at 16:21

1 Answers1

0

The WHERE condition should be

where (CostCenterno = 'bw800' or CostCenterNo = '%')
and (EmpNo = '2' or EmpNo like '%' )
and (surname = 'sonny' or surname like '%')

Also if you use parameters, it should be

where (CostCenterno = @CostCenterno or @CostCenterNo = '')
and (EmpNo = @EmpNo  or @EmpNo ='' )
and (surname = @surname or @surname ='')

EDIT: You may need to use something like below

declare @error varchar(100)
set @error=
case 
when not exists(select * from CostCentres where CostCenterno = @CostCenterno) then
    'CenterNotFound'
when not exists(select * from CostCentres where EmpNo = @EmpNo) then
    'EmpnoNotFound'
when not exists(select * from CostCentres where surname = @surname) then
    'surnameNotFound'
end
select CostCenterno, EmpNo,surname , @error as status
from CostCentres
where
    CostCenterno = @CostCenterno 
and EmpNo = @EmpNo  
and surname = @surname
Madhivanan
  • 13,470
  • 1
  • 24
  • 29
  • Thanks for the quick response. I tried the first statement and it works for when the parameters are correct. However, it returns all results for all rows with CostCenterNo = 'bw800' including the correct row and the error messages for all the other rows where EmpNo is incorrect. I tried with LIKE in all three and still no luck. So, is the SELECT part of the statement correct, we just need to figure out the WHERE clause? – Mad. 3iGroup Nov 06 '13 at 14:38
  • Madhivanan, the second where clause you proposed does not return anything when any of the parameters are incorrect and hence does not inform the user which parameter is not found in the table. Otherwise, it selects perfectly when the parameters ARE correct. I have the feel that it is just so close. . . nearly there. . . – Mad. 3iGroup Nov 06 '13 at 16:12
  • Thanks Madhivanan, the edited query works a charm. Now, I am finding that the case clause and its when conditions are not evaluating properly. I find that no matter which wrong parameter is sent it always returns the first error message. ie: in the above, if the empno is wrong, it gets stuck on the first when statement and doesnt go onto the 2nd when statement. I think it might be because all t2 columns are returning null so whichever when is evaluated first will be the one returned. – Mad. 3iGroup Nov 07 '13 at 10:12
  • Thanks Madhivanan, I had looked at using several select statements in a stored procedure but had thought that it might take too much processing time to evaluate the select statements in succession. I had hoped to execute a quicker using one select with the case statements embedded. I guess this is the only option left. Thank you so much for your assistance. If, however you come up with a quicker execution solution do not hesitate to post it. I shall use the stored procedure route until we find the quicker solution. – Mad. 3iGroup Nov 07 '13 at 11:17