[thelist] SQL Challenge Question
Ken Schaefer
ken at adOpenStatic.com
Tue Oct 28 23:32:41 CST 2003
As you can see - this is type of thing that you don't really want to be
running a lot of in your database...
Probably easier might have been:
a) store all the original values in hidden form fields (or requery the
database on form submit)
b) compare original values to newly submitted values (eg in a loop)
c) update the database, and once the data is succesfully committed,
generate/send the email
Then you don't need a yucky SQL query to do this for you.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Rob Smith" <rob.smith at THERMON.com>
Subject: RE: [thelist] SQL Challenge Question
: <snip Author="Jay Blanchard">
: If you are using an auto-increment number of some sort you could always
: get the latest one via the highest auto-increment number.
: </snip>
:
: Thanks Jay. I've done all that. Rudy and I've been going back and forth on
: this [off list] and he gave me:
:
: select P1.Project
: , '>' as Diffs
: , P1.ProjectID
: , case when P1.ProjectVersion
: <> P2.ProjectVersion
: then '*' else ' ' end as ProjectVersion
: , case when P1.ProjectOwner
: <> P2.ProjectOwner
: then '*' else ' ' end as ProjectOwner
: , case when P1.ProjectTitle
: <> P2.ProjectTitle
: then '*' else ' ' end as ProjectTitle
: , case when P1.ProjectDesc
: <> P2.ProjectDesc
: then '*' else ' ' end as ProjectDesc
: , case when P1.ProjectCompetition
: <> P2.ProjectCompetition
: then '*' else ' ' end as ProjectCompetition
: , case when P1.ProjectMemberID
: <> P2.ProjectMemberID
: then '*' else ' ' end as ProjectMemberID
: , case when P1.ProjectRemarkID
: <> P2.ProjectRemarkID
: then '*' else ' ' end as ProjectRemarkID
: , case when P1.ProjectAffiliateID
: <> P2.ProjectAffiliateID
: then '*' else ' ' end as ProjectAffiliateID
: , case when P1.ProjectTimeID
: <> P2.ProjectTimeID
: then '*' else ' ' end as ProjectTimeID
: , case when P1.ProjectEngID
: <> P2.ProjectEngID
: then '*' else ' ' end as ProjectEngID
: from GPT.dbo.Projects P1
: inner
: join GPT.dbo.Projects P2
: on P1.Project = P2.Project
: where P1.Project = 'AP-12345'
: and P1.ProjectVersion = '1'
: and P2.ProjectVersion = '2'
: union all
: select Project
: , ' '
: , ProjectID
: , cast(ProjectVersion as varchar(15))
: , ProjectOwner
: , ProjectTitle
: , ProjectDesc
: , ProjectCompetition
: , cast(ProjectMemberID as varchar(15))
: , cast(ProjectRemarkID as varchar(15))
: , cast(ProjectAffiliateID as varchar(15))
: , cast(ProjectTimeID as varchar(15))
: , cast(ProjectEngID as varchar(15))
: from GPT.dbo.Projects
: where Project = 'AP-12345'
: and ProjectVersion = '1'
: union all
: select Project
: , ' '
: , ProjectID
: , cast(ProjectVersion as varchar(15))
: , ProjectOwner
: , ProjectTitle
: , ProjectDesc
: , ProjectCompetition
: , cast(ProjectMemberID as varchar(15))
: , cast(ProjectRemarkID as varchar(15))
: , cast(ProjectAffiliateID as varchar(15))
: , cast(ProjectTimeID as varchar(15))
: , cast(ProjectEngID as varchar(15))
: from GPT.dbo.Projects
: where Project = 'AP-12345'
: and ProjectVersion = '2'
: order
: by 1 -- project
: , 2 -- diffs
: , 3 -- projectid
:
: Which creates a new row and places an * in the column where the two rows
are
: different per column.
More information about the thelist
mailing list