CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2coldfusion-9
Geo
Geo

Reputation: 3200

Select statement does not pull all the data from a field - SQL Server 2008 R2

I have this query to get results out of my database. My problem is that the selectedanswer part of the select statement does not pull all of the data from r.textboxmulti.

Below is my query.

     <cfquery name="fullResults" datasource="#variables.dsn#">
    select s.id, s.name surveyname, q.question, subq.answer subquestion, isnull(isnull(isnull(isnull(a.answer, **r.textboxmulti**),r.other),r.textbox),r.truefalse) as **selectedanswer**, a.*, r.*
    from results r
    join questions q on r.questionidfk = q.id
    left join answers subq on r.itemidfk = subq.id
    left join answers a on r.answeridfk = a.id
    join surveys s on q.surveyidfk = s.id
    where owneridfk = <cfqueryparam value="#arguments.ownerid#" cfsqltype="CF_SQL_VARCHAR" maxlength="35">
    order by s.id, owneridfk, q.rank, subq.rank desc
   </cfquery

Upvotes: 0

Views: 2470

Answers (3)

anon
anon

Reputation:

Try using COALESCE instead of ISNULL. These lead to different data type precedence rules - I suspect a.answer is varchar(255). Also can't hurt to explicitly convert the first argument:

COALESCE(CONVERT(VARCHAR(MAX), a.answer),
  r.textboxmulti, r.other, r.textbox, r.truefalse)

Upvotes: 2

idstam
idstam

Reputation: 2878

In SQL Server management studio there is a setting that tells it how many characters to show for a column in the grid, or in text view.

If you're going to use the data in an application; are you seing all the data there? That is a sure sign that you use the default setting in SSMS to show only 255 characters.

Upvotes: 0

TimothyAWiseman
TimothyAWiseman

Reputation: 14873

Could you explain what you mean by "all the data"? If you mean that it is not pulling data from r.textboxmulti for some rows, that mean that it is null for those rows and that fact is being masked by by the isnulls you have it wrapped in (incidentally, you could consider using a coalesce instead of nesting isnull....)

If you mean that it is getting data but leaving some of it off then one possibility is that it is being truncated. This can happen if SQL Server (or the next stage of processing where you are veiwing it...) sees it as a data type that is too small to hold the return value, this can sometimes happen due to implicit type conversions. You can deal with that by explicitly casting towards a large enough data type.

A final, rare, possibility is that r.textboxmulti containst an ascii null character. Many programs (including the SQL Server print command...) take that as a command to stop displaying anything from that string afterwards. This does not come up often, but I have encountered it myself.

Upvotes: 1

Related Questions