Help query text - 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 2000 Forums

SQL Server Development (2000)

Help query text

Author

Topic

mariechristine
Starting Member

27 Posts

Posted - 2004-09-08 : 02:48:48

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

mariechristine
Starting Member

27 Posts

Posted - 2004-09-09 : 03:11:44

So no solution guys?????????!!!!!!!!!!!!!!!!!!!!!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-09 : 03:20:31

suggestion, instead of trying to look for null, can you just add a default value to your table?

if not, then can you use ISNULL ( check_expression , 0 ) to replace null with 0.

for the text column, what is the exact problem so that we can better help you find a solution?

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-09 : 13:20:23

Not sure this will work, but the idea is to have 2 unions where you check for a value in result_remark and return '' if there is is no value.

...
union all

select SendToLab_ID , Lab_ID ,SampleRep_ID ,
SendToLab_Date ,Result_Date ,Result_ID ,
Result_IsSuitable , NotSuitableType_ID ,
Result_IsMinisterApproved , Result_ApprovalDate , Cast('No Remark' as text) Result_Remark
from @tblResultNull

where Len(Result_Remark) > 0

union all

select SendToLab_ID , Lab_ID ,SampleRep_ID ,
SendToLab_Date ,Result_Date ,Result_ID ,
Result_IsSuitable , NotSuitableType_ID ,
Result_IsMinisterApproved , Result_ApprovalDate , Result_Remark
from @tblResultNull

where Len(Result_Remark) = 0

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources