Nested Select

[bbawkon]

Hi All, Hoping someone can help me with this.

SELECT TOP 10 header."uid"
FROM sp50devel."dbo"."header" WHERE header."plx_name" LIKE '(SELECT TOP 1 header."plx_name" FROM sp50devel."dbo"."header" ORDER BY header."date_time" DESC)' ORDER BY header."date_time" DESC





header."plx_name" is text
header."uix" is int

If I run the nested SELECT, I get a return of IT-E03184A

If I replace the nested query portion with 'IT-E03184A' then I get the top 10 corresponding header."uid"s

If I run the query as posted, I get a return of NOTHING, NULL...

Can someone help me with this one. I've never worked with nested query's and i'm about to pull my last hair out..

Thanks!
Ben

 

[swampBoogie]

SELECT TOP 10 header."uid"
FROM sp50devel."dbo"."header" WHERE header."plx_name" = (SELECT TOP 1 header."plx_name" FROM sp50devel."dbo"."header" ORDER BY header."date_time" DESC) ORDER BY header."date_time" DESC

 

[bbawkon]

The text, ntext, and image data types are invalid in this subquery or aggregate expression.

State: 42000, Native: 279, Source: Microsoft OLE DB Provider for SQL Server

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

That's what I get if I use the = expression. plx_name is a text column.

 

[swampBoogie]

What type of data do you store in plx_name? Do you really need a text data type?

 

[bbawkon]

Unfortunately, Yes. It is the actual name of a circuit board. It is unreasonable to request our engineers to name all of their boards using only 0-9 charachters.

Ben

 

[SQLSister]

but how many characters does it need? a varchar datatype can accept up to 8000 characters. The text datatype is for data longer than that like entire documents. I would not think a circuit name would be more than 20-25 characters.

THerefore you should probably not be using the text datatype.

 

[swampBoogie]

using only 0-9 charachters.

There seems to be some misunderstanding here.

A varchar column can hold a maximum of 8000 characters.

Using varchar(30) allows you to store names with a maximum length of 30 characters.

 

[bbawkon]

I could switch to VarChar(50) in that case. Would that help make the Query function properly?

Correct, circuit names are less than 50 charachters.

Thanks for you help,
Ben

 

[bbawkon]

I switched header."plx_file" from text to varchar(50), and am getting the exact same results. The query as written returns an empty set, but if I replace the ( SUBQUERY ) with 'IT-E03184A' I get the 10 records that I'm looking for.

Thanks for all your help so far, hoping you can get me through this...

Ben

 

[swampBoogie]

Could it be that you have leading or trailing blanks in the name?

SELECT TOP 10 header."uid"
FROM sp50devel."dbo"."header" WHERE header."plx_name" = (SELECT TOP 1 ltrim(rtrim(header."plx_name")) FROM sp50devel."dbo"."header" ORDER BY header."date_time" DESC) ORDER BY header."date_time" DESC

 

[bbawkon]

Hmm... That actually seems to have worked, however, I'm confused. Even if there are leading or trailing blanks, i'm comparing plx_name with plx_name, so shouldn't it match WITH the blanks, and NOT Match without? " ABC " Doesn't EQUAL "ABC", but " ABC " DOES match " ABC "...

Can someone help explain the logic to me?

Thanks again!
Ben

 

[tlbroadbent]

I recommend returning the plx_name column and its length in the result set. Examine the result to understand why swampboogie's query returned results. It may be returning unexpected or even undesirable results.

SELECT TOP 10 uid, plx_name, datlength(plx_name) As NameLen
FROM sp50devel.dbo.header
WHERE plx_name =
(SELECT TOP 1 plx_name
FROM sp50devel.dbo.header
ORDER BY date_time DESC)
ORDER BY date_time DESC


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm