cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements
Walkthrough Wednesdays
99
Views
5
Helpful
2
Replies
cevliyaoglu
Beginner

Question about AXL query need help modifying the query

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.

 

2 REPLIES 2
Gordon Ross
Engager

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.

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

Content for Community-Ad

Spotlight Awards 2021