More problems with updateText

Hi,

Basically I am trying to add 2 ntext fields together sandwhiched by a literal ( '<BR /><BR />' ) in a SP for a report I will be running.

First step is to add <BR /><BR />, which I have done with some help from this forum, using a cursor and temp tables.

The last step is add the second ntext column (if it exists for the case only though). So like the 1st step I am using cursor and updatetext to amend the temporary table. The problem is that rather than update the temp table, the query section I have highlighted is run and nothing at all gets added to temptable. Since the column I am trying to add is a ntext I can't create a local variable. Does anyone know where I am going wrong?

Thanks in advance

Declare @.value2 varbinary(16)
DECLARE TEXTPTR_CURSOR2 CURSOR FOR
SELECT TEXTPTR(RT.Status) FROM #TempReport RT
DECLARE @.CurrentCase int

OPEN TEXTPTR_CURSOR2
FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.CurrentCase = caseid from #tempreport rt
where TEXTPTR(RT.Status) = @.value2

If ( select count(*) from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8) > 0
BEGIN

UPDATETEXT #tempreport.Status @.value2 null 0
(select IsNull(thevalue,'')
from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8)

END

FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

END

CLOSE TEXTPTR_CURSOR2
DEALLOCATE TEXTPTR_CURSOR2

For those interested, I managed to crack this by using another text pointer:

Declare @.value2 varbinary(16)
DECLARE TEXTPTR_CURSOR2 CURSOR FOR
SELECT TEXTPTR(RT.Status) FROM #TempReport RT
DECLARE @.CurrentCase int

OPEN TEXTPTR_CURSOR2
FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.CurrentCase = caseid from #tempreport rt
where TEXTPTR(RT.Status) = @.value2

If ( select count(*) from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8) > 0
BEGIN
declare @.value3 varbinary(16)
select @.value3 = textptr(thevalue) from tbl_memo m where caseid = @.currentcase and memotypeid = 8

UPDATETEXT #tempreport.Status @.value2 null 0 tbl_memo.thevalue @.value3

END

FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2
END

CLOSE TEXTPTR_CURSOR2
DEALLOCATE TEXTPTR_CURSOR2

more empty fields

I will explain the problem as best I can(bad English, sorry)
There are 2 tables in the database, there is 1 field with the same name in both tables.
The report shows information based on this common field witch I use to filter the data.
As soon as the common field is not yet in the second table because the user has not yet placed data there with the common field the reports flips totally blank.

I hope this explanation helps, this problem bugs me for some time and I have to make more reports based on this database structure.

ThanksYou need a left outer join from the first table to the second one.

monitoring changes on a view

I have a number of tables, each with many fields. I put together
information by a view onto the relevant tables and fields. Now I'd like
to monitor if data in my view has been updated or inserted. I don't care
about the fields not included in the view. If something changes, the ID
of the corresponding dataset should be inserted into a log table.
I have no idea how to handle this with a trigger. If I put a trigger on
the basetables, it will fire on every change. On the other hand i can't
put a trigger on the view. Any ideas?
Thanks in advance
Michael
Example:
Table1
a1
a2
a3
a4
Table2
b1
b2
b3
b4
Table3
c1
c2
c3
c4
c5
View combines
a1,a2,b1,c4,c5Michael Schroeder" wrote:

> I have a number of tables, each with many fields. I put together
> On the other hand i can't
> put a trigger on the view. Any ideas?
>
Do you mean that certain business rules prevent you from putting a trigger
on the view? You can put an INSTEAD OF trigger on the view, and put logic
into the trigger to update the underlying base tables, as well as put an
entry into an audit log.|||I don't know what type of events you are wanting to audit, but you can
create an insert, update, or delete triggers on the base tables. Whether or
not this presents a problem depends on how complex the logic of the trigger
is. Below is an example of a simple and low cost implementation of an
auditing trigger that I have used in the past. In this case, the table
EmployeeAudit has the same column layout of Employee but with the addition
of a column named AuditDate and AuditType (delete or insert). An update is a
delete immediately followed by an insert.
CREATE trigger EmployeeAudit on Employee
AFTER INSERT, UPDATE, DELETE
AS
insert into EmployeeAudit select getdate(), 'D', * from deleted
insert into EmployeeAudit select getdate(), 'I', * from inserted
GO
"Michael Schroeder" <schroeder@.idicos.[germany]> wrote in message
news:%23zqtnQ1JGHA.1544@.TK2MSFTNGP11.phx.gbl...
>I have a number of tables, each with many fields. I put together
>information by a view onto the relevant tables and fields. Now I'd like to
>monitor if data in my view has been updated or inserted. I don't care about
>the fields not included in the view. If something changes, the ID of the
>corresponding dataset should be inserted into a log table.
> I have no idea how to handle this with a trigger. If I put a trigger on
> the basetables, it will fire on every change. On the other hand i can't
> put a trigger on the view. Any ideas?
> Thanks in advance
> Michael
> --
> Example:
> Table1
> a1
> a2
> a3
> a4
> Table2
> b1
> b2
> b3
> b4
> Table3
> c1
> c2
> c3
> c4
> c5
> View combines
> a1,a2,b1,c4,c5|||Mark Williams schrieb:
> Michael Schroeder" wrote:
>
> Do you mean that certain business rules prevent you from putting a trigger
> on the view? You can put an INSTEAD OF trigger on the view, and put logic
> into the trigger to update the underlying base tables, as well as put an
> entry into an audit log.
No. I can put an INSTEAD OF trigger on that view. But it does not fire
when something in the underlying basetables changes or something is
beeing inserted/deleted.
Maybe I have to mention that the basetables a beeing updated by
replication and the view and its trigger are just for monitoring and
reporting.
Putting triggers on each basetable is not a good idea, because there are
many of them and just a fraction of their data is interesting for
processing.
Thanks for your reply
Michael

Mon- Fri Bit fields Query

I am having trouble figuring out how to complete this any help is appreiciated.

I have Mon , Tues , Weds , Thurs , Fri as bit fields I need to write a query for a report to see if the value is true, and display M, T, W, Th, F in a column Days if they are scheduled that day

example:

Mon and Wed I got on the bus the checkboxes are set to True, I want one column in the report Days: to display M, W

Ray:

Is something like this sufficient?

set nocount on

declare @.monStr varchar (11) set @.monStr = 'M,'
declare @.tueStr varchar (11) set @.tueStr = 'T,'
declare @.wedStr varchar (11) set @.wedStr = 'W,'
declare @.thuStr varchar (11) set @.thuStr = 'Th,'
declare @.friStr varchar (11) set @.friStr = 'F,'

declare @.recurringEvent table
( eventName varchar (20) not null,
Mon bit,
Tues bit,
Wed bit,
Thur bit,
Fri bit
)

insert into @.recurringEvent values ('Ride Bus', 1,0,1,0,0)
insert into @.recurringEvent values ('Go to Classes', 1,0,1,1,0)
insert into @.recurringEvent values ('Sleep @. night', 1,1,1,1,1)
insert into @.recurringEvent values ('Feed Dog', 1,0,1,0,1)
insert into @.recurringEvent values ('Feed Gator', 0,0,0,0,0)

select eventName,
case when convert (tinyint, mon) + convert (tinyint, tues)
+ convert (tinyint, wed) + convert (tinyint, thur)
+ convert (tinyint, fri) = 0
then ''
else left (left (@.monStr, 2 * mon)
+ left (@.tueStr, 2 * tues)
+ left (@.wedStr, 2 * wed)
+ left (@.thuStr, 3 * thur)
+ left (@.friStr, 2 * fri),
datalength (left (@.monStr, 2 * mon)
+ left (@.tueStr, 2 * tues)
+ left (@.wedStr, 2 * wed)
+ left (@.thuStr, 3 * thur)
+ left (@.friStr, 2 * fri)) - 1
)
end as Days
from @.recurringEvent

-- -
-- S A M P L E O U T P U T
-- -

-- eventName Days
-- -- -
-- Ride Bus M,W
-- Go to Classes M,W,Th
-- Sleep @. night M,T,W,Th,F
-- Feed Dog M,W,F
-- Feed Gator

|||

I prefer the case construct

case when mon=0 and tues=0 and wed=0 and thur=0 and fri=0

over

case when convert (tinyint, mon) + convert (tinyint, tues)
+ convert (tinyint, wed) + convert (tinyint, thur)
+ convert (tinyint, fri) = 0

Sorry. I just don't really like the look of this particular query.

Dave

|||Yea I'm not able to get this to work I'm going to look into it more this has to be a common scenerio.|||

Mugambo's code will return NULL if one of the data fields is NULL for any day of the week. This code should always work because NULL's and 0's will be caught by the ELSE for each test and an empty string will be returned.

set nocount on

declare @.recurringEvent table
( eventName varchar (20) not null,
Mon bit,
Tues bit,
Wed bit,
Thur bit,
Fri bit
)

insert into @.recurringEvent values ('Ride Bus', 1,NULL,1,0,0)
insert into @.recurringEvent values ('Go to Classes', 1,0,1,1,0)
insert into @.recurringEvent values ('Sleep @. night', 1,1,1,1,1)
insert into @.recurringEvent values ('Feed Dog', 1,0,1,0,1)
insert into @.recurringEvent values ('Feed Gator', 0,0,0,0,0)

Select EventName,
Replace(RTrim(
Case When Mon = 1 Then 'M ' Else '' End
+ Case When Tues = 1 Then 'T ' Else '' End
+ Case When Wed = 1 Then 'W ' Else '' End
+ Case When Thur = 1 Then 'TH ' Else '' End
+ Case When Fri = 1 Then 'F ' Else '' End)
, ' ', ',')
From @.RecurringEvent

|||Ok I just kind of dont understand how to use the code?|||

Thanks for picking me up Matros; I knew I didn't like what I had but I was too busy to continue with it.

Dave

|||

Ray,

The code posted here uses a table variable (which you won't need) so that the code can be tested. Table variable exist in memory only for the duration of the query, so no table structure changes occur. This means you can copy/paste the code, as is, in to a query window so you can run it and, more importantly, learn from it.

In your query, you will need to add this part...

Replace(RTrim(
Case When Mon = 1 Then 'M ' Else '' End
+ Case When Tues = 1 Then 'T ' Else '' End
+ Case When Wed = 1 Then 'W ' Else '' End
+ Case When Thur = 1 Then 'TH ' Else '' End
+ Case When Fri = 1 Then 'F ' Else '' End)
, ' ', ',') As WeekdayUsage

as though it were another field being returned from the query. Of course, this assumes that the bit field you use to store Monday usage is named Mon, and Tuesday's field is named Tue, etc..

If you continue to have problems implementing this solution, perhaps it would be best to post the existing query so that we may assist further.

|||

I was playing around with the code, and I don't believe this is what I am trying to do let me explain a lil more.

I have a table that does a transportation schedule. It has Mon, Tues, Wed, Thurs, and Fri as bit data types so that I may have checkboxes on the front-end. I need to build a report in reporting services that will list the days attend like so:

Data Types:

Rec# int Not Null PK Indentity Seed

Name Varchar(25) Null

Pickup Varchar(25) Null

Time Smalldatetime Null

Mon Bit Null

Tues Bit Null

Wed Bit Null

Thurs Bit Null

Fri Bit Null

Report Ex:

Name: Pickup: Time: (Days Attend: "Virtual Column doesn't exist in database")

Bill Center City 9:00am (M,W "True Value in database is True or 1")

Robert West Ave. 9:30am (W,Th,F "True Value in database is True or 1")

Can I do a case statement for each, and cancatenate them to display together, also I need them all to display in the same column?

|||That is exactly what mastros' code does:

Replace(RTrim(
Case When Mon = 1 Then 'M ' Else '' End
+ Case When Tues = 1 Then 'T ' Else '' End
+ Case When Wed = 1 Then 'W ' Else '' End
+ Case When Thur = 1 Then 'TH ' Else '' End
+ Case When Fri = 1 Then 'F ' Else '' End)
, ' ', ',') As DaysAttend

The output of this will be a column called DaysAttend which has a value of "M,W".

|||

If I build a view for the report how would I use the code?

Select PickupTime, Vehicle#, Destination, Mon, Tues, Wed, Thurs, Fri

Replace(RTrim(

Case When Mon = 1 Then 'M ' Else '' End

+ Case When Tues = 1 Then 'T ' Else '' End

+ Case When Wed = 1 Then 'W ' Else '' End

+ Case When Thurs = 1 Then 'TH ' Else '' End

+ Case When Fri = 1 Then 'F ' Else '' End)

, ' ', ',') As DaysAttend

From Schedule

|||Sweet I got it works great thanks..... Alot everyone. I