Error in SQL Query The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. for the query

[Former Member]

hi Experts,

while running SQL Query i am getting an error as

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

select  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price ,

T2.LineText

from OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN

QUT10 T2 ON T1.DocEntry = T2.DocEntry where T1.DocEntry='590'

group by  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price

,T2.LineText

how to resolve the issue

[Former Member]

Dear Meghanath,

Please use the following query, Hope your purpose will serve.

select  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price ,

CAST(T2.LineText as nvarchar (MAX))[LineText]

from OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN

QUT10 T2 ON T1.DocEntry = T2.DocEntry --where T1.DocEntry='590'

group by  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price

,CAST(T2.LineText as nvarchar (MAX))

Regards,

Amit

[kothandaraman_nagarajan]

Hi,

Try this:

select  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price ,

   Cast(T2.LineText as nvarchar (200)) as Details

from OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN

QUT10 T2 ON T1.DocEntry = T2.DocEntry where T1.DocEntry='590'

group by  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price,

Cast(T2.LineText as nvarchar (200))

Thanks & Regards,

Nagarajan

[Former Member]

same issue repeating 10 items duplication is happening

[KennedyT21]

Try This

select  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price ,

(select  isnull(T2.LineText,'') from QUT10 t2 where t2.DocEntry=t0.docentry and and t1.linenum=t2.aftlinenum) as Ltext

from OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry

where T1.DocEntry='590'

[Former Member]

Dear mKennedy,

Ltext field is Empty not getting value as we are having in document

[Former Member]

Dear MS, 

Have you try my query ?

You will get output like as per  document and you have to put text field in below of itemcode .

Example :

AP....

Please check...

-Rajesh NS

[Former Member]

ya rajesh tried but i am having 5 items in row level but i am getting only one item

[Former Member]

Hi MS,

I done small changes try this ,

select  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price , isnull(T2.LineText,'') from OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry left  JOIN QUT10 T2 ON T1.DocEntry = T2.DocEntry and t1.linenum=t2.aftlinenum where T1.DocEntry='590'

-Rajesh NS

[kothandaraman_nagarajan]

Hi,

Try this:

select  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price, Cast(T2.LineText as nvarchar (200)) as Details

FROM OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN QUT10 T2 ON T0.DocEntry = T2.DocEntry and T1.[LineNum] =  T2.[AftLineNum]

WHERE t0.docentry = [%0]

Thanks & Regards,

Nagarajan

[Former Member]

line text not coming

[former_member229757]

Hi

    Run this query.

select  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price ,CAST(T2.LineText as nvarchar (200)) as LineText

from OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN

QUT10 T2 ON T1.DocEntry = T2.DocEntry where T1.DocEntry='590'

group by  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price

,cast(T2.LineText as nvarchar (200))

Regards

Vinoth

[Former Member]

Hi,

Try this,

select  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price , isnull(T2.LineText,'') from OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN QUT10 T2 ON T1.DocEntry = T2.DocEntry and t1.linenum=t2.aftlinenum where T1.DocEntry='590'

-Rajesh N

[KennedyT21]

Hi Meghanath...

Try This

select  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price ,

isnull(T2.LineText,'')

from OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN

QUT10 T2 ON T1.DocEntry = T2.DocEntry where T1.DocEntry='590'

Regards

Kennedy

[Former Member]

dear kennedy ,

i am getting record twice

[KennedyT21]

Try This

select  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price ,

isnull(T2.LineText,'')

from OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN

QUT10 T2 ON T0.DocEntry = T2.DocEntry

Rgds,

Kennedy

[Former Member]

dear kennedsy

samne issue repeating

[Former Member]

PFA

[KennedyT21]

Try This

select  T1. Dscription,T1.docEntry,T1.Quantity,T1.Price ,

(select  isnull(T2.LineText,'') from QUT10 t2 where t2.DocEntry=t0.docentry and and t1.linenum=t2.aftlinenum) as Ltext

from OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry

where T1.DocEntry='590'