September 8, 2004 at 12:58 am
I have a table whose last record has some columns that are null. I want to retrieve this last record and in place of null put 0. At the end i want a union between all records and the last one that i altered.The problem is i have a text column!!! Here is my query: can u tell me how to resolve the text part?? Declare @curResult cursor Declare @tblResult TAble(SendToLab_ID int, Lab_ID int,SampleRep_ID int,SendToLab_Date datetime,Result_Date nvarchar(10),Result_ID int, Result_IsSuitable bit, NotSuitableType_ID int, Result_IsMinisterApproved bit, Result_ApprovalDate nvarchar(10), Result_Remark varbinary(16)) Declare @tblResultNull Table (SendToLab_ID int, Lab_ID int,SampleRep_ID int,SendToLab_Date datetime, Result_Date nvarchar(10),Result_ID int, Result_IsSuitable bit, NotSuitableType_ID int, Result_IsMinisterApproved bit, Result_ApprovalDate nvarchar(10), Result_Remark varbinary(16)) Insert into @tblResult select Top 1 S.SendToLab_ID , S.Lab_ID,S.SampleRep_ID,S.SendToLab_Date, convert(nvarchar(10), R.Result_Date,103) Result_Date,R.Result_ID, R.Result_IsSuitable, R.NotSuitableType_ID, R.Result_IsMinisterApproved, convert(nvarchar(10), R.Result_ApprovalDate,103) Result_ApprovalDate, TextPtr(Result_Remark) as Result_Remark from SendToLabImport S inner join SampleRepImport Smp on S.SampleRep_ID=Smp.SampleRepImport_ID left outer join ResultImport R on R.SendToLab_ID=S.SendToLab_ID where Smp.PaperREg_ID=1 order by S.SendToLab_ID desc set @curResult = cursor forward_only static for select * from @tblResult Open @curResult FETCH NEXT FROM @curResult into @sendToLabID , @labID , @SampleRepID , @SendToLabDate , @resultDate , @resultID , @isSuitable , @NotSuitableTypeID , @isMinisterApproved , @approvalDate , @remarkPtr
WHILE (@@Fetch_Status =0) BEGIN Insert into @tblResultNull values (@sendToLabID , @labID , @SampleRepID , @SendToLabDate , @resultDate , 0 , 0 , @NotSuitableTypeID , @isMinisterApproved , @approvalDate, @remarkPtr) FETCH NEXT FROM @curResult into @sendToLabID , @labID , @SampleRepID , @SendToLabDate , @resultDate , @resultID , @@isMinisterApproved , @approvalDate , @remarkPtr
END
select S.SendToLab_ID , S.Lab_ID,S.SampleRep_ID,S.SendToLab_Date, convert(nvarchar(10), R.Result_Date,103) Result_Date,R.Result_ID, R.Result_IsSuitable, R.NotSuitableType_ID, R.Result_IsMinisterApproved, convert(nvarchar(10), R.Result_ApprovalDate,103) Result_ApprovalDate , R.Result_Remark from SendToLabImport S inner join SampleRepImport Smp on S.SampleRep_ID=Smp.SampleRepImport_ID inner join ResultImport R on R.SendToLab_ID=S.SendToLab_ID where Smp.PaperREg_ID=1 union all select SendToLab_ID , Lab_ID ,SampleRep_ID , SendToLab_Date ,Result_Date ,Result_ID , Result_IsSuitable , NotSuitableType_ID , Result_IsMinisterApproved , Result_ApprovalDate , /***HERE IS THE PROBLEM***////ReadText Result_Remark @remarkPtr 1 0 from @tblResultNull
CLOSE @curResult DEALLOCATE @curResult
|
September 8, 2004 at 5:48 am
Have you tried converting the ReadText to be like the following?
CAST(ReadText AS VARCHAR(8000)) or CONVERT(VARCHAR(8000), ReadText)
You can also add that to the WHERE or wherever else you reference the TEXT field.
Unfortunately, if the data contained in the TEXT field goes over it will either be TRUNCATED or blow-up the proc... I forget which when dealing with TEXT.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 8, 2004 at 6:17 am
| what do i do if my text goes over 8000? which will probably do. ???????//// |
September 8, 2004 at 5:23 pm
that my friend could get very ugly. You would need to build multiple variables and parse the text field ..... Please dont ask me how to do that. I have very limited experience with text fields. I generally used VARCHAR(8000) fields in the past...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 9, 2004 at 1:42 am
| It's ok. ISNULL function has all the solution for me. I had forgotton all about it. ISNULL works great in my case. |
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply