SQL Server Performance Forum – Threads Archive

Order by Clause

SELECT CALLEDNUMBER FROM [CURRENTI] D
INNER JOIN
(select CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes
order by len(replacedcode) desc)c
ON
D.CALLEDNUMBER LIKE ‘0’+LTRIM(RTRIM(C.REPLACEDCODE))+’%’
AND
D.CODEB IS NULL
could i use order by in subquery if not then wats the solution
i dont want to use cursor for Updation

---

ORDER BY in the JOIN clause is unnecessary. What about
SELECT CALLEDNUMBER FROM [CURRENTI] D
INNER JOIN
(select CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes)c
ON D.CALLEDNUMBER LIKE ‘0’+LTRIM(RTRIM(C.REPLACEDCODE))+’%’
AND D.CODEB IS NULL
ORDER BY… May I say, that using LIKE in JOINs is not the best choice to get good performance. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

SELECT CALLEDNUMBER FROM [CURRENTI] DINNER JOIN(select CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes)cON D.CALLEDNUMBER LIKE ‘0’+LTRIM(RTRIM(C.REPLACEDCODE))+’%’AND D.CODEB IS NULLORDER BY… if i want to update the codes to its maximum to minimum
Like if the callednubmer is 02158265
and the replacementcode table have 2 codes 215 and 21
the the Updation should b like this codeb=0215
else id destinationnumber is 216 then 21

---

Post some sample data and the result you want Madhivanan Failing to plan is Planning to fail

---

Coupe of things on this expression:
D.CALLEDNUMBER LIKE ‘0’+LTRIM(RTRIM(C.REPLACEDCODE))+’%’ If you add the leading zero on the REPLACEDCODE column, and remove any leading zeroes – before trying to run the update – then performance should already improve. (This looks like a one-time update query, so performance is probably not a big issue, unless you have little time to run it.) If REPLACEDCODE is a numeric column, then you might try changing it to a type that matches the CALLEDNUMBER column – which would appear to be varchar or similar. This takes out the implicit conversion.

---

Let say we have a table called replacement code
cityformatch code
Turkey Mobile 00901
Turkey 0090 if i wil select the table like this
Select * from replacemetncode order by len(code) desc
IT wil give the code from maximum to its minimum length So the others table have the callednumber
like
Callednumber
00901
0090
00905 All the callednumber must match to the maximum lenth of the
replacementcode table
like
callednumber 00901 must match with Turkey Mobile 00901
while
callednumber 0090 must match with Turkey 0090 of the replacementcode table

---

what is the datatype of code? If it is varchar then no need of appending 0 Madhivanan Failing to plan is Planning to fail

---

the question is how wil i update the CURRENTI table because in subquery
v can’bt specify the order by clause to select the maximum order to the minimum order length of code Update D
set b.codeb=c.codeb
FROM [CURRENTI] D
INNER JOIN
(select CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes
order by len(replacedcode) desc)c
ON
D.CALLEDNUMBER LIKE ‘0’+LTRIM(RTRIM(C.REPLACEDCODE))+’%’
AND
D.CODEB IS NULL

---

You can use Top 100 percent in subquery Update D
set b.codeb=c.codeb
FROM [CURRENTI] D
INNER JOIN
(select Top 100 percent CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes
order by len(replacedcode) desc)c
ON
D.CALLEDNUMBER LIKE ‘0’+LTRIM(RTRIM(C.REPLACEDCODE))+’%’
AND
D.CODEB IS NULL
Madhivanan Failing to plan is Planning to fail

---

You need to have a WHERE NOT EXISTS subquery to verify that there is no longer matching replacementcode entry: Update D
set b.codeb=c.codeb
FROM [CURRENTI] D
INNER JOIN
(select CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes)c
ON D.CALLEDNUMBER LIKE ‘0’+LTRIM(RTRIM(C.REPLACEDCODE))+’%’
WHERE D.CODEB IS NULL
AND NOT EXISTS
(SELECT * FROM [MACHINE-09].POPS.dbo.replacementcodes X
WHERE LEN(X.ReplacedCode) > LEN(C.ReplacedCode)
AND X.ReplacedCode LIKE C.ReplacedCode + ‘%’)

---

Just spotted this error in your code: Update D
set b.codeb=c.codeb
FROM [CURRENTI] D The second line should of course be this: set D.codeb=c.codeb

---

i have table name updatedcitya follwing the data 22
22
2202
2203
2204
2205
2206
2207
221
221
221
221
221
221
222
2221
2221
223
2231
2232
2232
2232
2233
2237
2237
2238
224
226
227
228
229
229
2292 select top 100 percent * from standard.dbo.updatedcitya where code like ‘22%’ order by len(code) desc
it wil produce the result from maximum to its minimum length
but it wil not Update the other table called Test
having the following callednumber column
021545
0223
02238
02202 but the resut is
testcodeb
22
22
22
please see this Update statement tel me wat is wrong with that Update T
set testcodeb=c.code
—select *
from Test T inner join
(
select top 100 percent * from standard.dbo.updatedcitya where code like ‘22%’ order by len(code) desc )C
on
t.callednumber like ‘0’+rtrim(c.code)+’%’
and
t.testcodeb is null

---

I am not sure of all the patterns since you did not post sample data from all tables (i.e. what should be the correct testcodeb for the above situation so we can set the select statement. If you could post the DDL with DML to generate sample data, then maybe we can post this accurately. Check this out though. UPDATE T
SET testcodeb = c.code
FROM
TEST T,
(SELECT
code,
MAX(codelen) maxcode
FROM (
SELECT code, len(code) codelen
FROM standard.dbo.updatedcitya u
WHERE
left(code,2) = ’22’
) MU
GROUP BY code
) MAXT
WHERE
T.testcodeb is null
AND T.callednumber like ‘0’+rtrim(MAXT.code)+’%’
) the inner should return the code with only the maximum length. I cannot determine if the query should use the CHARINDEX instead of like since I don’t have all the data. Anyway, you may get an idea from this. May the Almighty God bless us all!
www.empoweredinformation.com

---

If my bring this to your attention again:

---

]]>