Login or Register to Ask a Question and Join Our Community


Programming

SQL Syabse - return rows where certain value changes

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming SQL Syabse - return rows where certain value changes
# 1  
Old 04-14-2011
SQL Syabse - return rows where certain value changes
Hi,
I need help selecting rows from a table when a certain column value (City) changes.

In this example of table rows:
Code:
ID City Updated_By Updated_Date
99999 PARIS JOHN 04/05/2010 11:28:09.813
99999 PARIS JOHN 04/05/2010 11:32:05.240
99999 PARIS JOHN 04/05/2010 11:32:20.750
99999 PARIS GREG 05/05/2010 11:16:24.433
99999 PARIS GREG 05/05/2010 11:16:24.433
99999 BERLIN DAVID 18/06/2010 12:13:43.546
99999 PARIS DAVID 18/06/2010 12:13:43.546
99999 BERLIN ADAM 30/06/2010 15:20:13.266
99999 BERLIN ADAM 30/06/2010 15:20:13.266
99999 BERLIN KATH 30/06/2010 15:20:17.313
99999 BERLIN KATH 30/06/2010 15:20:17.313
99999 BERLIN SUSAN 04/02/2011 15:14:19.250

...I need to return only the following rows (ie when city value changes):
Code:
ID City Updated_By Updated_Date
99999 PARIS JOHN 04/05/2010 11:28:09.813
99999 BERLIN DAVID 18/06/2010 12:13:43.546
99999 PARIS DAVID 18/06/2010 12:13:43.546
99999 BERLIN ADAM 30/06/2010 15:20:13.266

Any help greatly appreciated.
Thanks!!
Last edited by pludi; 04-14-2011 at 02:57 PM..
# 2  
Old 04-15-2011
Selecting occurs when you submit the query. Triggers can watch columns and act on changes, but they are on someone else's connection, the updating process connection. You could copy their keys, the date-time and the old city or row to a history table using a trigger, then use that in your query.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell Variables passed to awk to return certain rows

Hi Forum. I have the following test.txt file and need to extract certain rows based on "starting position", "length of string" and "string to search for": 1a2b3d 2a3c4d ..... My script accepts 3 parameters: (starting col pos, length to search for, string to search for) and would like to... (4 Replies)
Discussion started by: pchang
4 Replies

2. Shell Programming and Scripting

Assign the return value of the SQL to a variable in UNIX

Hi I am new to UNIX. I am trying the below and getting the error. I am trying to assign the variable with the value of the query result. I want this value to use in the next steps. Created UNIX file (Batch.sh) as below #!/bin/ksh sqlplus callidus/callidus4u@attstcal @Batch.sql ... (2 Replies)
Discussion started by: KrishBalu
2 Replies

3. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

4. Shell Programming and Scripting

Assigning return value of an embedded SQL in a shell script variable

I've a script of the following form calling a simple sql that counts the no of rows as based on some conditions. I want the count returned by the sql to get assigned to the variable sql_ret_val1. However I'm finding that this var is always getting assigned a value of 0. I have verified by executing... (1 Reply)
Discussion started by: MxC
1 Replies

5. Shell Programming and Scripting

calling pl/sql procedure from shell and return values

How could I call an Oracle PL/SQL procedure from any shell (bash) and catch returning value from that procedure (out param) or get a returning value if it's a function. also, I got into trouble when I tried to send a number as a param #!/bin/bash -e username=$1 pwd=$2 baza=$3... (0 Replies)
Discussion started by: bongo
0 Replies

6. Shell Programming and Scripting

Shell script to catch PL/SQL return values

I tried searching the forum for similar posts but its closed now. Would appreciate any help on this. I am trying to capture return value from a select query into a variable. DB is Oracle I am able to spool it to a file but I donot intend to use it. Here is my script that does not work ;) I... (27 Replies)
Discussion started by: monie2717
27 Replies

7. Shell Programming and Scripting

Return rows from Oracle package

I need help . I am not getting anything back from my setup. I have defined an oracle package which I am calling from within my sql call, but is returning no rows. If I run my sql from sqlplus works find, does not work whe I try to run from UNIX shell script My korn script test.ksh #!bin/ksh... (6 Replies)
Discussion started by: TimHortons
6 Replies

8. Shell Programming and Scripting

Shell script to catch PL/SQL return values

Hello, I need some help from the experts on PL/SQL and Shell scripting. I need a shell script that runs a PL/SQL procedure and gets the values returned from the PL/SQL procedure into the shell variables. The PL/SQL procedure returns multiple values. I was able to assign a single return value... (1 Reply)
Discussion started by: Veera_Raghav
1 Replies

9. Shell Programming and Scripting

return number of rows selected

Hi all, i am doing a perl script to read from a db. I am able to retrieve the rows, but i am unable to return the number of rows selected. i tried $selectedrows = $sth->numrows; i got the error msg: Can't locate object method "numrows" via package "DBI::st" i changed it to $selectedrows =... (7 Replies)
Discussion started by: new2ss
7 Replies

10. Shell Programming and Scripting

return variable from PL/SQL procedure to shell

Hi i'm calling a pl/sql procedure which is returning one variable. i'm trying to assing this value to variable in shell script the code i wrote is ** in shell script** var= 'sqlplus user/pass @ret.sql' echo $var ** and variable dum_var number exec rt_test(:DUM_VAR); exit; in... (4 Replies)
Discussion started by: ap_gore79
4 Replies
Login or Register to Ask a Question