08-26-2015 01:57 AM - edited 03-19-2019 10:00 AM
Hi all
When I run This AXL query , I get a result such as DID, user, firstname,lastname
run sql SELECT distinct np1.dnorpattern, np1.calledpartytransformationmask, eu.userid, eu.firstname, eu.middlename, eu.lastname FROM Device d join DeviceNumPlanMap dnpm on d.pkid=dnpm.fkDevice and d.name like 'SEP%' join NumPlan np2 on dnpm.fkNumPlan = np2.pkid join NumPlan np1 on (np2.dnorpattern=np1.calledpartytransformationmask and np1.dnorpattern is not NULL and np1.dnorpattern like '\\+%') left join EndUserDeviceMap eudm on d.pkid=eudm.fkdevice left join EndUser eu on eu.pkid=eudm.fkenduser
Sample of output
dnorpattern calledpartytransformationmask userid firstname middlename lastname
============== ============================= ============================= =============== ==========
\+902122028414 81177101 xxx@yyy.com Sibel asdfasdasd hjkhkjh
\+902122028430 81177196 NULL NULL NULL NULL
\+9033311111 81177137 abc@dsds.com Alper AtaÄŸ
\+902222809002 80421147 1111@xxx.akb John Smith
The query result is, giving only "the dnorpattern is not NULL" records
I try to modify the query, such as even dnorpattern is null or not
How can I do this?
PS: When I remove "np1.dnorpattern is not null" statement, nothing changes.
08-26-2015 03:38 AM
You're trying to join using NULL values. Tweak the JOIN to an OUTER JOIN and you should be good to go. See technet.microsoft.com/en-us/library/ms190409%28v=sql.105%29.aspx for details.
GTG
08-27-2015 03:34 AM
Hi Gordon The below query is working properly thanks for your assist about the "outer join" recommendation
run sql SELECT distinct np1.dnorpattern, np2.dnorpattern, eu.userid, eu.firstname, eu.middlename, eu.lastname FROM Device d join DeviceNumPlanMap dnpm on d.pkid=dnpm.fkDevice and d.name like 'SEP%' join NumPlan np1 on (dnpm.fkNumPlan = np1.pkid and np1.dnorpattern like '8%') left outer join NumPlan np2 on (np2.calledpartytransformationmask=np1.dnorpattern and np2.dnorpattern like '\\%') left join EndUserDeviceMap eudm on d.pkid=eudm.fkdevice left join EndUser eu on eu.pkid=eudm.fkenduser
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide