Query problem

[dfwcharles]

I'm trying to query a table to find certain Glaccount codes. The datalength of the code varies, the maximum length of the code is 15, but I only want to find the codes that have a datalength of 10. Here's the catch; Some of the codes have only 10 char, but there will be a code that matches the frist 10 with an additional 5 chars at the end. I only want the codes that have 10 with no matching 15. Here's an small example of the data.

glaccount
---------------------
1106195001
110619500181155
1106400000
110640000082167
1106400001
110640000100000
1106400002
1106400003
110640000381155
110640000381156
1106400004
110640000482141
110640000482142
1106400005
1106400006
110640000682723

In this example I would only what to see 1106400002 and 1106400005. I was thinking about joining the table to itself, with some kind of where clause, but I'm stumped.

Thanks in advance
Charles

 

[checkai]

select a.code
from tbl a
where len(a.code) = 10 and a.code not in (select left(code,10) from tbl where len(code) > 10)

what's this give yOU?

dlc

 

[donutman]

I think this is what you are looking for:

Select Max(Glaccount) from MyTable Group By Left(Glaccount,10) Having Count(*)=1

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[checkai]

I would agree with DonutMan

however, do you need the MAX?

Select Glaccount from MyTable Group By Left(Glaccount,10) Having Count(Glaccount )=1

 

[dfwcharles]

Checkai, I get codes with a length of 10, but they still have matching codes with 15.

Donutman, you query gets me close enough. Your code also returns glaccounts that are 15 in length but have no corresponding code of 10. Does make sense? I.e, returns 110640400000000 which does not have a match like this 1106404000. Close enough for what i need to do

Thanks for y'alls help
Charles

 

[donutman]

Good point. Missed that part.

Select Glaccount from MyTable Group By Left(Glaccount,10) Having Count(Glaccount )=1 and Len(Glaccount)=10

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Can the 10-letter code ever be repeated?

1106400004
1106400004

Donutman's query is great, otherwise. If there can be duplicate 10-letter codes, try:

SELECT G1.Glaccount
   FROM TheTable G1
      LEFT JOIN TheTable G2 ON Len(G2.Glaccount)=15 AND G1.GlAccount = Left(G2.Glaccount,10)
   WHERE G2.Glaccount IS NULL

 

[dfwcharles]

The glaccounts will always be distinct. I think your query returned everthing but what i needed.

 

[ESquared]

Really? hmmmmmmmmmmm