Question about AXL query need help modifying the query

cevliyaoglu · ‎08-26-2015

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.

Gordon Ross · ‎08-26-2015

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

Please rate all helpful posts.

cevliyaoglu · ‎08-27-2015

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