GROUP BY and text field - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

GROUP BY and text field

Author

Topic

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-23 : 10:16:46

SELECT OCRD.CardCode AS 'Customer No.', INV1.DocEntry as 'Doc En.', INV1.Text as 'Txt'
FROM INV1, OCRD
GROUP BY OCRD.CardCode, INV1.DocEntry, INV1.Text

This is the code that I expected to execute, but I am getting an error

When i put Text in the Group By its gives me an error
When i take Text out of the Group By it gives me an error

CardCode has numbers in it..DocEntry has numbers in it..Text has NULLs in it or blank values

Is there a way I can keep the group by and making this code work?

Any help is appreciated, thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 10:19:33

And what might the error message be, I wonder...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 10:20:19

[code]SELECT OCRD.CardCode AS [Customer No.],
INV1.DocEntry as [Doc En.],
INV1.Text as [Txt]
FROM INV1
CROSS JOIN OCRD
GROUP BY OCRD.CardCode,
INV1.DocEntry,
INV1.Text[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 10:21:18

quote:
Originally posted by DMarmolejos

SELECT OCRD.CardCode AS 'Customer No.', INV1.DocEntry as 'Doc En.', INV1.Text as 'Txt'
FROM INV1, OCRD
GROUP BY OCRD.CardCode, INV1.DocEntry, INV1.Text

This is the code that I expected to execute, but I am getting an error

When i put Text in the Group By its gives me an error
When i take Text out of the Group By it gives me an error

CardCode has numbers in it..DocEntry has numbers in it..Text has NULLs in it or blank values

Is there a way I can keep the group by and making this code work?

Any help is appreciated, thank you

Whats the error you're getting?

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-23 : 11:09:58

quote:
Originally posted by visakh16

quote:
Originally posted by DMarmolejos

SELECT OCRD.CardCode AS 'Customer No.', INV1.DocEntry as 'Doc En.', INV1.Text as 'Txt'
FROM INV1, OCRD
GROUP BY OCRD.CardCode, INV1.DocEntry, INV1.Text

This is the code that I expected to execute, but I am getting an error

When i put Text in the Group By its gives me an error
When i take Text out of the Group By it gives me an error

CardCode has numbers in it..DocEntry has numbers in it..Text has NULLs in it or blank values

Is there a way I can keep the group by and making this code work?

Any help is appreciated, thank you

Whats the error you're getting?

ERROR - "The text, ntext, image data types cannot be compared or sorted, ecept when using IS NULL or LIKE Operator"

It is not a matter of the Text column having null values because I tryed it with another column.

Select OCRD.CardCode AS 'Customer No.', POR1.ItemCode AS 'PO No.', OPOR.DocNum AS 'Style No.',
max(case when ITM1.PriceList=1 then ITM1.Price end) as 'First Cost',
max(case when ITM1.PriceList=2 then ITM1.Price end) as 'Wholesale',
POR1.Quantity, OPOR.DocDate, OPOR.DocDueDate, INV10.LineText
FROM OPOR, ITM1, POR1, OCRD, INV10
GROUP BY OCRD.CardCode, POR1.ItemCode, OPOR.DocNum, POR1.Quantity, OPOR.DocDate, OPOR.DocDate, OPOR.DocDueDate, INV10.LineText

This my whole code..It works fine when I leave out whats highlighted in bold..but if i add it i get that error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 11:12:57

quote:
Originally posted by DMarmolejos

quote:
Originally posted by visakh16

quote:
Originally posted by DMarmolejos

SELECT OCRD.CardCode AS 'Customer No.', INV1.DocEntry as 'Doc En.', INV1.Text as 'Txt'
FROM INV1, OCRD
GROUP BY OCRD.CardCode, INV1.DocEntry, INV1.Text

This is the code that I expected to execute, but I am getting an error

When i put Text in the Group By its gives me an error
When i take Text out of the Group By it gives me an error

CardCode has numbers in it..DocEntry has numbers in it..Text has NULLs in it or blank values

Is there a way I can keep the group by and making this code work?

Any help is appreciated, thank you

Whats the error you're getting?

ERROR "The text, ntext, image data types cannot be compared or sorted, ecept when using IS NULL or LIKE Operator"

It is not a matter of the Text column having null values because I tryed it with another column.

Select OCRD.CardCode AS 'Customer No.', POR1.ItemCode AS 'PO No.', OPOR.DocNum AS 'Style No.',
max(case when ITM1.PriceList=1 then ITM1.Price end) as 'First Cost',
max(case when ITM1.PriceList=2 then ITM1.Price end) as 'Wholesale',
POR1.Quantity, OPOR.DocDate, OPOR.DocDueDate, INV10.LineText
FROM OPOR, ITM1, POR1, OCRD, INV10
GROUP BY OCRD.CardCode, POR1.ItemCode, OPOR.DocNum, POR1.Quantity, OPOR.DocDate, OPOR.DocDate, OPOR.DocDueDate, INV10.LineText

This my whole code..It works fine when I leave out whats highlighted in bold..but if i add it i get that error

You cant use GROUP BY over text fields. try casting it to varchar and then using if you're sure if data falls within certain length.

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-23 : 12:04:21

quote:
Originally posted by visakh16

quote:
Originally posted by DMarmolejos

quote:
Originally posted by visakh16

quote:
Originally posted by DMarmolejos

SELECT OCRD.CardCode AS 'Customer No.', INV1.DocEntry as 'Doc En.', INV1.Text as 'Txt'
FROM INV1, OCRD
GROUP BY OCRD.CardCode, INV1.DocEntry, INV1.Text

This is the code that I expected to execute, but I am getting an error

When i put Text in the Group By its gives me an error
When i take Text out of the Group By it gives me an error

CardCode has numbers in it..DocEntry has numbers in it..Text has NULLs in it or blank values

Is there a way I can keep the group by and making this code work?

Any help is appreciated, thank you

Whats the error you're getting?

ERROR "The text, ntext, image data types cannot be compared or sorted, ecept when using IS NULL or LIKE Operator"

It is not a matter of the Text column having null values because I tryed it with another column.

Select OCRD.CardCode AS 'Customer No.', POR1.ItemCode AS 'PO No.', OPOR.DocNum AS 'Style No.',
max(case when ITM1.PriceList=1 then ITM1.Price end) as 'First Cost',
max(case when ITM1.PriceList=2 then ITM1.Price end) as 'Wholesale',
POR1.Quantity, OPOR.DocDate, OPOR.DocDueDate, INV10.LineText
FROM OPOR, ITM1, POR1, OCRD, INV10
GROUP BY OCRD.CardCode, POR1.ItemCode, OPOR.DocNum, POR1.Quantity, OPOR.DocDate, OPOR.DocDate, OPOR.DocDueDate, INV10.LineText

This my whole code..It works fine when I leave out whats highlighted in bold..but if i add it i get that error

You cant use GROUP BY over text fields. try casting it to varchar and then using if you're sure if data falls within certain length.

i am not too familar with casting and is it possible to automatic cast the lenght? i dont know the lenght..

I tryed adding
CAST( INV10.LineText AS VARCHAR(8000))in the select statement and still got the same error

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 13:16:32

You should use the same expression in Group by Clause also

Madhivanan

Failing to plan is Planning to fail

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-06-23 : 13:37:00

yea^ thank you to all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources