*
The moose likes JDBC and the fly likes Sql Query to find the change in Column Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Sql Query to find the change in Column" Watch "Sql Query to find the change in Column" New topic
Author

Sql Query to find the change in Column

Shahir Deo
Ranch Hand

Joined: Mar 19, 2013
Posts: 80

Hi Guys

I have a table which consists of:

select * from tabel;

Time----------------ID---------------Value------
Something 123456789 54.0
Something 123456789 null
Something 456789456 78
Something 121218928 null
Something 421312333 65



So i need to select ID whose value changed from Null to Some value (54.0 or 78..)
The Query should return: 123456789 (first value was null then 54.0)


Knowledge enlivens the soul.
fred rosenberger
lowercase baba
Bartender

Joined: Oct 02, 2003
Posts: 11229
    
  16

What have you tried?


There are only two hard things in computer science: cache invalidation, naming things, and off-by-one errors
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

You want to find records whose ID column used to be null at some time in the past, but now is no longer null?
Allen Hamilton
Greenhorn

Joined: Jan 25, 2014
Posts: 9
Shahir Deo wrote:Hi Guys

I have a table which consists of:

select * from tabel;

Time----------------ID---------------Value------
Something 123456789 54.0
Something 123456789 null
Something 456789456 78
Something 121218928 null
Something 421312333 65



So i need to select ID whose value changed from Null to Some value (54.0 or 78..)
The Query should return: 123456789 (first value was null then 54.0)


I'm not sure what you mean. Are you trying to select a range of Values?

If so, then you can use the BETWEEN condition:

SELECT * FROM Table
WHERE Value = NULL
OR Value BETWEEN 0 AND 54;
Shahir Deo
Ranch Hand

Joined: Mar 19, 2013
Posts: 80

Paul Clapham wrote:You want to find records whose ID column used to be null at some time in the past, but now is no longer null?



Yes!

My Solution is to get all records whose value is Null and Union all the record whose values are not null.

May be i am not correct , but hope you all got my point.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Shahir Deo wrote:
Paul Clapham wrote:You want to find records whose ID column used to be null at some time in the past, but now is no longer null?



Yes!


Okay...

My Solution is to get all records whose value is Null and Union all the record whose values are not null.


In other words, No! You are only interested in records whose value is null at the current moment.

...hope you all got my point.


Not really, no.
Shahir Deo
Ranch Hand

Joined: Mar 19, 2013
Posts: 80

...hope you all got my point.


Not really, no.


Okay.


There is a Table whcih stores all the ID , Value , Date and Unique ID(Sequence)

Now , I need to select only those ID and Date whose value was null and Then it became Not Null;

Ex:

ID-----------DATE---------VALUE-----------UniqueID
1234-----------12/1/13-----------125-----------008
1234-----------4/2/14-----------65 -----------007
1234-----------6/5/14-----------NULL -----------006
1234-----------7/05/12-----------NULL-----------005


So Now I need to Select Previous ID and Select Previous Date where Value of ID became Not Null (65).

Expected OP : 1234-----6/5/14 (Blue Line.) Because 1234-----4/2/14 value is 65.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41489
    
  53
I almost thought I understood it. But the value did not become "not null" on 4/2/14, it became "not null" on "12/1/13" - all those records go together since they all have the same ID, no? At least that's how I understood the first post.


Ping & DNS - my free Android networking tools app
Shahir Deo
Ranch Hand

Joined: Mar 19, 2013
Posts: 80

Ulf Dittmer wrote:I almost thought I understood it. But the value did not become "not null" on 4/2/14, it became "not null" on "12/1/13" - all those records go together since they all have the same ID, no? At least that's how I understood the first post.


It became Not Null on Both "12/1/13" and "4/2/14" But the Immediate date after it became Not Null Is : 4/2/14,

So Displaying Previous ID with Date i.e 1234-----6/5/14
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41489
    
  53
I'm confused: how did it become not null on 4/2/14? 4/2/14 is before 6/5/14, so its value would have been 125 on that day, no?
Shahir Deo
Ranch Hand

Joined: Mar 19, 2013
Posts: 80

Ulf Dittmer wrote:I'm confused: how did it become not null on 4/2/14? 4/2/14 is before 6/5/14, so its value would have been 125 on that day, no?


Oh..::P

I had Given Date for Sample . Check the unique ID, It is Increasing. That Means it is IN DESC Order.


NEW DATA:

ID---------------DATE------------VALUE-----------UniqueID
1234-----------4/05/12-----------125-------------008
1234-----------3/05/12-----------65--------------007
1234-----------2/05/12-----------NULL-----------006
1234-----------1/05/12-----------NULL-----------005

On 1/05/12 Value was Null

On 2/05/12 Value was Null

On 3/05/12 Value was 65

On 4/05/12 Value was 125

AND MY REQUIREMENT IS TO FECTH 1234 , 02/05/12 (Because On 03/05/12 value became Not NuLL);


UFF
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Shahir Deo wrote:I had Given Date for Sample . Check the unique ID, It is Increasing. That Means it is IN DESC Order.

I'm sure you're aware of it yourself by now, but let me emphasize that when providing the specification by an example, it is vitally important for the example to be correct. It's problematic enough when the example doesn't cover all cases, as in this question.

I'll try to reformulate your specification:

You have a sequences of values in a table, distinguished by a column (ID) and ordered by a date (DATE). You want to select all IDs of all series that at any one time contained NULL and at any later time contained a non-NULL value. So, for example, all these sequences would meet your criteria:

Sequence 1: NULL, 1 (obvious)
Sequence 2: NULL, NULL, 1 (obvious)
Sequence 3: 1, NULL, 1 (am I right?)
Sequence 4: NULL, 1, NULL (am I right?)

You would NOT want these sequences:

Sequence 5: 1, 1 (never contained a NULL)
Sequence 6: NULL, NULL (never contained a value)
Sequence 7: 1, NULL (never contained a change from a NULL to a non-NULL)

Is this formulation correct?

Edit: you also want to select a date at which the value changed from NULL to non-NULL. Can the value change from NULL to non-NULL several times? Assuming it can, do you want to select any date when the NULL to non-NULL change occurred, or some specific date (such as the first, or the last one)?

Also, what database are you using? This is quite a complicated requirement and we might need to use some database specific functions to come up with an answer.
Shahir Deo
Ranch Hand

Joined: Mar 19, 2013
Posts: 80

Martin Vajsar wrote:
Sequence 1: NULL, 1 (obvious)
Sequence 2: NULL, NULL, 1 (obvious)
Sequence 3: 1, NULL, 1 (am I right?)
Sequence 4: NULL, 1, NULL (am I right?)


Sequence 1 and 2 is correct. and We dont get the 3 , 4 Sequence.

Martin Vajsar wrote:

You would NOT want these sequences:

Sequence 5: 1, 1 (never contained a NULL)
Sequence 6: NULL, NULL (never contained a value)
Sequence 7: 1, NULL (never contained a change from a NULL to a non-NULL)

Is this formulation correct?



Yes Information is Correct.


Martin Vajsar wrote:

Edit: you also want to select a date at which the value changed from NULL to non-NULL. Can the value change from NULL to non-NULL several times? Assuming it can, do you want to select any date when the NULL to non-NULL change occurred, or some specific date (such as the first, or the last one)?

Also, what database are you using? This is quite a complicated requirement and we might need to use some database specific functions to come up with an answer.


Once the Value is Changed from NULL to any Not Null value , We dont get Null again. (Assuming in DESC ORDER)

AS VALUE AFTER NOT NULL WILL NOT BE NULL AGAIN ,We ONLY get One DATE

DB: ORACLE 11g.

THANKS
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

That there can be only one change from non-NULL to NULL makes it much, much easier. I'll try to give you some hints on how to create the query:

1) To find the date, we'll simply find MIN(DATE) grouped by ID over records that don't have NULL values (a GROUP BY and WHERE clauses). We know that if any change from NULL to non-NULL occurred, it must be on that date.

2) The previous query will return us a list of all sequences*, even of those that didn't contain a NULL value at the start (so they never changed from non-NULL to NULL). To exclude these, we can join (a JOIN clause) this query to another query that will select only IDs of sequences that contain NULL values (with any date) - a simple WHERE clause.

I think it is really easy now, but certainly let us know how you're doing.

Edit:
* which have at least one non-NULL value
Shahir Deo
Ranch Hand

Joined: Mar 19, 2013
Posts: 80

Martin Vajsar wrote:I'll try to give you some hints on how to create the query:
* which have at least one non-NULL value


Thanks,

It worked , Actually after some time i got another requirement to display the Date whcih was null before the Value.

So i am working on it.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Glad to hear you got it working!

The latest requirement is achievable with only slight changes to both queries, but certainly let us know should you encounter any problems.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Sql Query to find the change in Column