sql - MS Access "Invalid Procedure Call" matching first half of email address with email addresses from another table -
i'm running "invalid procedure call" when running below code on tables in access 2010 db working in access 2000 db.
i didn't write code, know compairing text before '@' symbol love hear explanation of specific syntax looking @ here does: "left(nz(t1.username,''),instr(nz(t1.username,''),'@')-1) = left(nz(t2.email,''),instr(nz(t2.email,''),'@')-1) ))<>false));"
select t1.id, t1.username email users t1 (((exists (select * license t2 left(nz(t1.username,''),instr(nz(t1.username,''),'@')-1) = left(nz(t2.email,''),instr(nz(t2.email,''),'@')-1) ))<>false));
nz(t1.username,'') returns blank string ('') if t1.username null.
now, suppose t1.username aa.gmail.com
then instr(nz(t1.username,''),'@')-1 3 - 1 = 2 so, left(nz(t1.username,''),instr(nz(t1.username,''),'@')-1) left('aa.gmail.com', 2) = aa
so, expression extracts part of email address before @.
left(nz(t2.email,''),instr(nz(t2.email,''),'@')-1) doing same t2.email
so clause comparing part of t1.username before @ part of t2.email before @.
possible cause of error: if either t1.username or t2.email not of form something@something, instr return 0. so, left(some string, 0) may throwing error function left expecting value >=1 second parameter.
Comments
Post a Comment