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

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

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

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 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:

quote:Originally posted by Adriaan

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 + '%')