SQL help needed

[570669]

My table project has 20 columns. One of the column called 'operation' has 'U' for Update and 'I' for Insert showing that something has been updated or inserted in that particular row. I have to compare the rows and show both what the old value was and the new value that was updated is. So my table should look something like this:

updateddate updatedby old-value new-value

with date prompt for user to enter.

Any help with be greatly appreciated.

 

[LKBrwnDBA]

DB?



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[570669]

ORACLE 9i

 

[LKBrwnDBA]

Does a row with 'U' mean there exist a corresponding row with 'I'?

What do you mean by 'with date prompt for user to enter.'?
PL/SQL is not an interactive language.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[570669]

You are right. There is a corresponding row with I.

That date prompt was an error.

 

[Luzian]

One of the column called 'operation' has 'U' for Update and 'I' for Insert showing that something has been updated or inserted in that particular row.

You didn't specify what this have to do with your query.

I have to compare [red]the[/red] rows

"the rows" is an ambiguous statement because you did not specify which row you'll compare with which.

and show both what the old value was and the new value that was updated is

[tt]newvalue[/tt] and [tt]oldvalue[/tt] are columns that already exist in your table?

I think you should post all the columns of your table so we'll have a better understanding of what you want.

 

[570669]

My table has something like below:

Projectid projectoppdesc statusid updateddate oper
111 my proj. 1 4/2006 I
111 your proj. 1 4/2006 U


Here Projectoppdesc has been updated, so the new value will be "your proj." and the old value is "my proj". There are other many columns that have been changed and they put down U for the recent updates.

My table should look like this:

updated date Old value new value
4/2006 projectoppdesc(my proj.) proj.(your proj.)


populating in a similar way.

 

[Luzian]

Ah, yes. I've encountered something similar when creating a bug-tracking system where I used auditing. The results you want, looks like my "History" table.

I'm not sure if this is ANSI SQL

SELECT project_updates.updateddate,
	project_inserts.projectoppdesc AS old_value,
	project_updates.projectoppdesc AS new_value,
FROM ProjectHistory project_updates
JOIN ProjectHistory project_inserts
	ON project_updates.ProjectId = project_inserts.ProjectId
WHERE project_updates.oper = 'U'
AND project_inserts.oper = 'I'

 

[570669]

thanks a bunch