_______________________________________

>>In a stored procedure I have this line:

>> SELECT FIRST 1 CUSTOMER_ID FROM T_CUSTOMERS WHERE

>> (CC_HASH = :CC_HASH and :CC_HASH is not null and :CC_HASH != 0) OR

>> (CC_HASH_SHA256 = :CC_HASH_SHA256 and :CC_HASH_SHA256 is not null and not

>> :CC_HASH_SHA256 = '')

>> into :CUSTOMER_ID_OUT;



> That is no surprise. But it is "the additional OR" that does the damage, it is all that NOT logic in the WHERE clause.

Thanks a lot!



I changed it to this:

if (CC_HASH_SHA256 is not null and not CC_HASH_SHA256 = '') then

begin

SELECT FIRST 1 CUSTOMER_ID FROM T_CUSTOMERS WHERE (CC_HASH_SHA256 = :CC_HASH_SHA256) into :CUSTOMER_ID_OUT;

end

IF (:CUSTOMER_ID_OUT is null and CC_HASH is not null and CC_HASH <> 0) THEN

begin

SELECT FIRST 1 CUSTOMER_ID FROM T_CUSTOMERS WHERE (CC_HASH = :CC_HASH) into :CUSTOMER_ID_OUT;

End



And it works like a charm.



I will also look into your suggestion to remove the FIRST 1 altogether, I didn't know it was expensive (why is it, I don't have a sort or anything I just want 1).



I will also sweep through the whole database for similar situations and also look for ways to remove Ors the way Maxim suggested.





Thanks a lot again, both of you, you saved the day.



Regards



Tim



[Non-text portions of this message have been removed]