Convert or Cast ntext to Nvarchar

Shawn Kogan shared this question 11 months ago
Answered

I am receiving an error that states cannot perform a distinct on ntext. In YF, can I convert an ntext datatype to nvarchar? I have tried several different formats and nothing seems to be working. If this conversion is possible, what is the correct format?

CAST(Resolution as nvarchar(4000))

CAST("Resolution" as nvarchar(4000))

CAST("HPD:Help Desk"."Resolution" as nvarchar(4000))


Whatever I do results in an "Unsupported construct in column names" error.

Comments (3)

photo
1

Hi Shawn,

did you try the Convert function, it worked for me - I created an ntext column in a SQL Server db:

/bq7gSzvtQTYAAAAASUVORK5CYIIA


then I was able to convert it to nvarchar in a calc field in a Yellowfin report:

/AUmeXyCJDISSAAAAAElFTkSuQmCCAA==

and then finally use a DISTINCT on it:

/uQW0gqN8AWIAAAAASUVORK5CYIIA

Please let me know how you get on with this.

regards,

David

photo
1

Thanks for the response Big Dave! I tried exactly that. I tried the convert. I tried the cast. Nothing works. Let me also state, I am working with BMC Remedy Smart Reporting. It is my understanding this sits on top of Yellow Fin. Perhaps they have stripped out some functionality. I finally heard back from BMC Support. They are saying I need to create a new DB field with a different data type (other than ntext).

photo
1

Hi Shawn,

ah yes, BMC Remedy Smart Reporting! That makes sense because I know they have their own bespoke JDBC driver, whereas I was using the Microsoft SQL Server JDBC driver.

Well, if BMC recommend you need to create a new DB field with a different data type then I would definitely follow their advice.

regards,

David