determining which rows have a long description? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

determining which rows have a long description?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-04-12 : 10:55:31

I have to look at a table that has a product description.
This description at times are too long and the user would like me to provide a list of these.

I would like to say something like select *all
where Description field is greater than 15

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-12 : 11:05:49

Asuming that the description column is a VARCHAR type (or NVARCHAR) then maybe just:



SELECT * FROM <theTable> WHERE LEN(<descriptionColumn>) > 15

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-04-12 : 11:23:51

Thank you very much Charlie!

Just one thing if I wanted to display only the first of multi's how can I do this? IOW, there are many duplicated descriptions in this list.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-12 : 11:26:14

If you just want the distinct descriptions....


SELECT DISTINCT <descriptionColumn> FROM <theTable> WHERE LEN(<descriptionColumn>) > 15

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-04-13 : 09:57:47

Thank you Charlie. I have one thing to add here tho.

I have to do a join to another table.
They want to select the description only for an active item.

so the other table has a field that it needs to have a '2' there.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-13 : 10:11:45

Can you explain how the tables are linked and the columns that are important. It sounds like your requirement will be a simple JOIN.

Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-04-13 : 10:16:25

It would be joined by itemnmbr

then the itemtype would be looked at for a value of '2'

Kristen
Test

22859 Posts

Posted - 2010-04-13 : 10:21:11

If [DescriptionColumn] is [the deprecated] datatype of TEXT then may need to use DATALENGTH() instead of LEN() (which, if datatype is Ntext) will return Bytes (2 bytes per character) rather than a count of Characters

Is that right? or have I misremembered?

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-04-13 : 10:25:43

it's working this way tho.
SELECT DISTINCT SOP10200.ITEMNMBR, SOP10200.ITEMDESC FROM dbo.sop10200 WHERE LEN(SOP10200.ITEMDESC) > 39

I am not sure how to include the join in here.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-13 : 10:29:41

You haven't told us the name of the tables but I guess it would be something like


SELECT DISTINCT
	sp102.ITEMNMBR
	, sp102.ITEMDESC
FROM
	dbo.sop10200 AS sp102
	JOIN dbo.<The Other table> AS spOTHER ON spOTHER.ITEMNMBR = sp102.ITEMNMBR
WHERE
	LEN(SOP10200.ITEMDESC) > 39
	AND spOTHER.ITEMTYPE = '2'

To be honest, You've managed one of the worst descriptions for your request that I've ever seen that still contains just enough info to guess what you want. So well done on that one.

Your tables have meaningless names also (unless you've changed them for the forum). It makes it hard to help when we don't know the column names, or table names, or relationships.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-04-13 : 10:51:35

I am getting an error message of "Msg 208, Level 16, State 1, Line 1
Invalid object name 'inv00101'."

SELECT
t2.[itemnmbr], t1.[itemdesc]
FROM
sop10200 AS t1

JOIN inv00101 AS t2 ON t2.[itemnmbr] = t1.[itemnmbr]
WHERE
t2.[itemType] = '2'

Kristen
Test

22859 Posts

Posted - 2010-04-13 : 10:58:04

Possibilities are:

inv00101 is misspelled, you are in the wrong database, or inv00101 is owned by a different schema (i.e. not the schema you are connected to and/or not "dbo")

Kristen
Test

22859 Posts

Posted - 2010-04-13 : 10:58:59

I suppose it might also be a permissions issue on inv00101, but I think that would give you a different error messages.

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-04-13 : 11:02:13

nope doesn't like the dbo there either

Kristen
Test

22859 Posts

Posted - 2010-04-13 : 11:05:02

That suggests that the table is in a different schema? or in a different database?

If the database is case-sensitive you will have to get the case of the table name correct too.

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-04-13 : 11:19:43

it is same database. they are all part of Great Plains. Don't know, I will just do it manually.

Kristen
Test

22859 Posts

Posted - 2010-04-13 : 11:36:38

Great Plains is (IIRC - that might be "was" !!) case sensitive. Open up the object explorer and cut & paste the table name so you are sure it is exactly right?

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-04-13 : 12:09:32

Sorry about that, Typo. But how do I include the Descriptions only being more than 39 chars

SELECT
t2.[itemnmbr], t1.[itemdesc]
FROM
SOP10200 AS t1

JOIN IV00101 AS t2 ON t2.[ITEMNMBR] = t1.[ITEMNMBR]
WHERE
t2.[ITEMTYPE] = '2'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-13 : 13:20:13

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2010-04-13 : 14:06:36

"how do I include the Descriptions only being more than 39 chars"


SELECT t2.[itemnmbr], t1.[itemdesc]
FROM   SOP10200 AS t1
       JOIN IV00101 AS t2
           ON t2.[ITEMNMBR] = t1.[ITEMNMBR]
WHERE t2.[ITEMTYPE] = '2'
      AND LEN([DescriptionColumn]) > 39

Kristen
Test

22859 Posts

Posted - 2010-04-13 : 14:13:13

P.S. T.C. said earlier:


SELECT ...
FROM ...
WHERE
	LEN(SOP10200.ITEMDESC) > 39
	AND ...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources