November 9, 2001 at 7:17 am
Hi,
I have two tables as below.
mls table
id(int), photo(varchar)
mls_photo table
id(int), medium_photo(image),medium_photo2(image)...,medium_photo9(image)
The mls.photo column is filled by the app when photos are added to the photo db.
I would like mls.photo calculated instead so that any of the columns that are Not NULL will cause a corresponding number to be concatenated to mls.photo.
For example:mls.id = 234567, and mls_photo.medium_photo,mls_photo.medium_photo2 are the only ones with info, then mls.photo should = '12'.
If mls_photo.medium_photo3 is added later, the mls.photo would now be '123'.
Here is a script I run periodically to make sure the field is reflecting correctly. This is really tough to run on the customers system since we are talking 50000 records so I would like to change to a calculated field if possible.
update mls set photo=0
go
update mls set photo='1' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo is not null)
go
update mls set photo=photo + '2' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo2 is not null)
go
update mls set photo=photo + '3' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo3 is not null)
go
update mls set photo=photo + '4' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo4 is not null)
go
update mls set photo=photo + '5' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo5 is not null)
go
update mls set photo=photo + '6' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo6 is not null)
go
update mls set photo=photo + '7' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo7 is not null)
go
update mls set photo=photo + '8' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo8 is not null)
go
update mls set photo=photo + '9' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo9 is not null)
go
update mls set photo=replace (photo,'0',NULL) where photo = '0'
Thanks for you help!
November 9, 2001 at 7:49 am
Just about the most convoluted statement I've producted, but will product the required string in one line, which is the required aim, I believe.
select isnull(nullif('1',isnull(medium_photo,'1')),'') +
isnull(nullif('2',isnull(medium_photo2,'2')),'') + ....etc
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 9, 2001 at 8:12 am
You could do this with a bit map
1 + 2 + 4 + 8 + ...
Probably more convoluted but
update mls
set photo = coalesce(left('1' + char(convert(binary(8),medium_photo1)),1),'')
+ coalesce(left('2' + char(convert(binary(8),medium_photo2)),1),'')
+
from [mlshuphoto].[dbo].[mls_photo] ph
where mls.id = ph.id
It is probably more readable to
case when medium_photo1 is null then '' else '1' end
+ case when medium_photo2 is null then '' else '2' end
+ ...
Cursors never.
DTS - only when needed and never to control.
November 9, 2001 at 8:13 am
Hi Paul,
That's absolutely disgusting. It'll take me hours just to figure out! I love functions like this one! Hope your brain stops hurting soon...
That will be great for fixing the field, but I cant use this as a computed field because of the subquery. I was hoping for a computed field in the mls table to reference the mls_photo table. If I cant, then how can I change this to be a computed field in the mls_photo table?
Thank you!
November 9, 2001 at 8:40 am
Hi nigelrivett,
I used your case statement and created a computed field in the mls_photo table. It works great.
But I would like to add a calc field to my mls table. This is what I have but it doesnt work:
alter table mls add photoc AS (
case when vreb_photo.mls_photo.medium_photo is null then '' else '1' end
+ case when vreb_photo.mls_photo.medium_photo2 is null then '' else '2' end
+ case when vreb_photo.mls_photo.medium_photo3 is null then '' else '3' end
+ case when vreb_photo.mls_photo.medium_photo4 is null then '' else '4' end
+ case when vreb_photo.mls_photo.medium_photo5 is null then '' else '5' end
+ case when vreb_photo.mls_photo.medium_photo6 is null then '' else '6' end
+ case when vreb_photo.mls_photo.medium_photo7 is null then '' else '7' end
+ case when vreb_photo.mls_photo.medium_photo8 is null then '' else '8' end
+ case when vreb_photo.mls_photo.medium_photo9 is null then '' else '9' end)
I get the error:"Invalid column 'medium_photo' specified in constraint definition."
Thanks!
November 9, 2001 at 8:51 am
Try adding a trigger to the vreb_photo table which updates mls..photoc field.
Note in your defintion you are not saying which row in vreb_photo to get the values from which should be a hint that it isn't going to work.
Edited by - nigelrivett on 11/09/2001 08:52:40 AM
Cursors never.
DTS - only when needed and never to control.
November 9, 2001 at 9:51 am
vreb_photo is the database,
mls_photo is the table,
medium_photo is the column.
The 2 tables have an [id] column in common.
Could I get some help on a trigger? This is my first attempt (as you can see).
CREATE TRIGGER photo_numbers
ON mls_photo
FOR DELETE , INSERT , UPDATE
AS vreb.mls.photoc = mls_photo.dbo.photoc, vreb.mls.p_mod_date_time = getdate() where mls_photo.id = vreb.mls.id
IF COLUMNS_UPDATED() > 0
November 9, 2001 at 10:24 am
Phred,
my solution is not so bad as it seems. Ignore the Select - that was for illustration - this code can be used as a computed column. However, admittedly the case statement is (a lot!) more clear.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 9, 2001 at 10:39 am
Paul,
Dont get me wrong. I thought your solution was great - intricate but great.
alter table mls add [photoc] AS (
isnull(nullif('1',isnull([vreb_photo].[mls_photo].[medium_photo],'1')),'') +
isnull(nullif('2',isnull([vreb_photo].[mls_photo].[medium_photo2],'2')),'') +
isnull(nullif('3',isnull([vreb_photo].[mls_photo].[medium_photo3],'3')),'') +
isnull(nullif('4',isnull([vreb_photo].[mls_photo].[medium_photo4],'4')),'') +
isnull(nullif('5',isnull([vreb_photo].[mls_photo].[medium_photo5],'5')),'') +
isnull(nullif('6',isnull([vreb_photo].[mls_photo].[medium_photo6],'6')),'') +
isnull(nullif('7',isnull([vreb_photo].[mls_photo].[medium_photo7],'7')),'') +
isnull(nullif('8',isnull([vreb_photo].[mls_photo].[medium_photo8],'8')),'') +
isnull(nullif('9',isnull([vreb_photo].[mls_photo].[medium_photo9],'9')),''))
I get the same problem however. (Invalid column 'medium_photo' specified in constraint definition.)
Is it possible to have a calculated field that references a column in another table?
November 9, 2001 at 11:24 am
Can you post up your table definitions - I can look at it later if it's still not solved by then.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 9, 2001 at 11:55 am
Here are the table defs I inherited. The mls table is truncated down to 3 fields. I did not add the calculated field of course to the mls table yet. But if I have to go with the calc field on the mls_photo database and use a trigger, then this will be what has to be done. Obviously I'm a novice and I do appreciate everyones help - VERY much.
CREATE TABLE [dbo].[mls] (
[id] [int] NOT NULL Clusterd Unique,
[p_mod_date_time] [smalldatetime] NULL ,
[photo] [varchar] (11) NULL ,
) ON [PRIMARY]
GO
id and p_mod_date_time are indexed.
CREATE TABLE [dbo].[mls_photo] (
[id] [int] NOT NULL ,
[large_photo] [image] NULL ,
[medium_photo] [image] NULL ,
[small_photo] [image] NULL ,
[medium_photo2] [image] NULL ,
[medium_photo3] [image] NULL ,
[medium_photo4] [image] NULL ,
[medium_photo5] [image] NULL ,
[medium_photo6] [image] NULL ,
[medium_photo7] [image] NULL ,
[medium_photo8] [image] NULL ,
[medium_photo9] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
large_photo and small_photo are only used on the central database, not in client machines.
November 11, 2001 at 2:48 pm
phred,
this code below works ok and is a shortened version to do what you want. However, it has dbo as owner of the tables, so I think the problem may be one of ownership of tables - in your case mls_photo is the table owner in the alter table statement, but the script for the tables is dbo - is mls_photo the dbo? If not that could explain it. If they are the same user, then try my code above and if this works on your system it can be extended with the other columns.
CREATE TABLE mls_photo
(
id int NOT NULL ,
large_photo image NULL ,
medium_photo image NULL ,
small_photo image NULL
)
alter table mls_photo add xxx as
(case when medium_photo is null then '' else '1' end)
+ (case when small_photo is null then '' else '1' end)
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 12, 2001 at 10:30 am
Thanks Paul, that does work fine.
I was wondering though, if it is possible to put this calculated field into the mls table and reference the mls_photo table from there? Can you do a cross table calculated field?
If not, one of the last posts shows a trigger (my very first) that would trigger when ANY of the mls_photo fields are inserted, updated, deleted. Do I have this right?
CREATE TRIGGER photo_numbers
ON mls_photo
FOR DELETE , INSERT , UPDATE
AS vreb.mls.photoc = mls_photo.dbo.photoc, vreb.mls.p_mod_date_time = getdate() where mls_photo.id = vreb.mls.id
IF COLUMNS_UPDATED() > 0
Thanks again!
November 12, 2001 at 2:48 pm
phred,
here's an insert/update trigger for you - (will leave you to create the corresponding delete one!)
CREATE TRIGGER photo_numbers
ON mls_photo
FOR INSERT , UPDATE
as
declare @PhotoState varchar(20)
set @PhotoState = case when inserted.medium_photo is null then '' else '1' end
+ case when inserted.medium_photo2 is null then '' else '2' end
+ case when inserted.medium_photo3 is null then '' else '3' end
+ case when inserted.medium_photo4 is null then '' else '4' end
+ case when inserted.medium_photo5 is null then '' else '5' end
+ case when inserted.medium_photo6 is null then '' else '6' end
+ case when inserted.medium_photo7 is null then '' else '7' end
+ case when inserted.medium_photo8 is null then '' else '8' end
+ case when inserted.medium_photo9 is null then '' else '9' end
insert into mls(p_mod_date_time, photo)
values getdate(), @PhotoState
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 12, 2001 at 2:56 pm
That is awesome Paul! Thank you for all the help. And for nigelrivett's help as well.
I didn't get any response at the Microsoft SQL forum. I think I'll stick around here and see if I can help anyone in return...Thanks again!
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply