Need one result from two table query but getting two results

[Ocula]

The following SQL is for phone billing.

cdr.dst=number dialed.

Country table fields are code=area prefix, landloc=geographical location (Australia OR Australia Mobile for example) and charge=per minute value.

There is a huge variation of country prefix and in the WHERE clause you can see I need to compare the first 3 digits (e.g. mid(cdr.dst,1,3)) through to the first 9 to determine which country and weather it is to a mobile phone and so apply the correct charge to the call (AND country.landloc Not Like '%Mobile').

It's not picking out exactly that. E.G. Aussie is 0061xxxxxxxxx and Aussie Mobile is 00614xxxxxxxx so if the number dialed is a mobile then as per my WHERE clause the first 4 digits are a match for Aussie and Aussie Mobile numbers BUT it's not getting exactly WHERE country.landloc Not Like '%Mobile' so I get 2 results.

Appreciate your assistance, TIA


SELECT cdr.calldate, cdr.src, cdr.dst, round(0.5+((cdr.billsec)/60),0) AS duration, date(cdr.calldate) as date, time(cdr.calldate) as time,
EXTRACT(YEAR FROM cdr.calldate) AS Year, EXTRACT(MONTH FROM cdr.calldate) AS Month, EXTRACT(DAY FROM cdr.calldate) as Day, country.LANDLOC AS location,
CASE
WHEN $Month='01' THEN 'Jan'
WHEN $Month='02' THEN 'Feb'
WHEN $Month='03' THEN 'Mar'
WHEN $Month='04' THEN 'Apr'
WHEN $Month='05' THEN 'May'
WHEN $Month='06' THEN 'Jun'
WHEN $Month='07' THEN 'Jul'
WHEN $Month='08' THEN 'Aug'
WHEN $Month='09' THEN 'Sep'
WHEN $Month='10' THEN 'Oct'
WHEN $Month='11' THEN 'Nov'
WHEN $Month='12' THEN 'Dec'
ELSE ''
END AS MonthT,
CASE
WHEN cdr.channel LIKE 'Local/%' THEN 'DVRT'
ELSE 'International'
END AS calltype,
country.charge*(round(0.5+(cdr.billsec)/60)) AS callcost
FROM cdr, country
WHERE EXTRACT(YEAR FROM cdr.calldate)=$Year AND EXTRACT(Month FROM cdr.calldate)=$Month AND accountcode='$Password' AND Concat('0',Mid(cdr.src,3,10))=$Number AND (cdr.dst Not Like 'vm%') And (cdr.dcontext Not Like 'from-internal') And (cdr.dcontext Not Like 'from-did-direct') And Length(cdr.dst)>10 And ((Mid(cdr.dst,1,3)=country.code)Or (Mid(cdr.dst,1,4)=country.code) Or (Mid(cdr.dst,1,5)=country.code) Or (Mid(cdr.dst,1,6)=country.code) Or (Mid(cdr.dst,1,7)=country.code) Or (Mid(cdr.dst,1,9)=country.code)) And (cdr.billsec>0) And cdr.disposition='ANSWERED' and country.LANDLOC Not Like '%Mobile'

 

[IanWaterman]

YOu need to wrap your ORs in brackets

And (
Length(cdr.dst)>10 And ((Mid(cdr.dst,1,3)=country.code)
Or (Mid(cdr.dst,1,4)=country.code) Or (Mid(cdr.dst,1,5)=country.code)
Or (Mid(cdr.dst,1,6)=country.code) Or (Mid(cdr.dst,1,7)=country.code)
Or (Mid(cdr.dst,1,9)=country.code))
)
And (cdr.billsec>0)

Ian

 

[Ocula]

Thanks Ian but aren't they already? All you did was include Length(cdr.dst)>10 in the group or ORs, which didn't change the output.

Cheers