[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