arithmetic function
The poster wanted a single SELECT that returns a column's current-row value plus the difference between it and the previous row's value. The suggested approach is a self-join of the table to itself, joining on whatever defines "previous row" (e.g. a.value - b.value). Respondents warned against using ROWID for this: rowids aren't sequential integers, gaps appear from concurrent inserts and rollbacks, and they can change on reorg, in-place-impossible ALTERs or clustered index rebuilds, so rowid is unreliable beyond the very short term. Consensus: the self-join works if the user supplies his own key-based ordering/sequencing columns. No follow-up from the original poster confirming success.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, Can someone please help. Is there a way to retrieve the value of some column before/after the curent column matching some 'where clause'. My goal is to retrieve in one shot, with one 'select statement', for instance the value of the current row and the difference (for the same column) between the value of the current row and the value of the previous row ie for the same column : value of current row - value previous row Regards
Gilles,
If I understand your question correctly, you need to tie the table back to
itself with a join criteria which will link to your definition of previous
row. Something like...
select a.rowid, a.value, a.value - b.value
from mytable a, mytable b
where a.rowid -1 = b.rowid
--GILLES TCHAPPI
--
--Hi,
--
--Can someone please help.
--Is there a way to retrieve the value of some column before/after the curent
--column matching some 'where clause'. My goal is to retrieve in one shot, with
--one 'select statement', for instance the value of the current row and the
--difference (for the same column) between the value of the current row and the
--value of the previous row ie for the same column : value of current row -
value
--previous row
--
--Regards
But rowids are not sequential integers, at least not in IDS. The real
trick will be defining correct arithmetic on rowids (equality comparison
and subtraction in this case). Given that rows can move around, I wonder
how that might work. Anyone? It would be an interesting object (type and
relevant methods.)
It's a simpler problem if you're talking about the sequence of rows in an
ordered result set, but still not trivial. An unordered result set
(remembering that SQL results are not inherently ordered) is much harder.
Cheers,
Dick Snoke
Executive IT Specialist
IBM Software Group - ChannelWorks
Tel: (404) 487-1595
Email: dsnoke@us.ibm.com
From:
"DAVE GRIFFEN" <dgriffen@finishline.com>
To:
ids@iiug.org
Date:
12/11/09 03:28 PM
Subject:
Re: arithmetic function [18362]
Sent by:
ids-bounces@iiug.org
Gilles,
If I understand your question correctly, you need to tie the table back to
itself with a join criteria which will link to your definition of previous
row. Something like...
select a.rowid, a.value, a.value - b.value
from mytable a, mytable b
where a.rowid -1 = b.rowid
--GILLES TCHAPPI
--
--Hi,
--
--Can someone please help.
--Is there a way to retrieve the value of some column before/after the
curent
--column matching some 'where clause'. My goal is to retrieve in one shot,
with
--one 'select statement', for instance the value of the current row and
the
--difference (for the same column) between the value of the current row
and
the
--value of the previous row ie for the same column : value of current row
-
value
--previous row
--
--Regards
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
As I stated previously, Gilles will need to use join criteria which will link
back to HIS DEFINITION of previous row. I would not rely on the database to
define row sequencing for this type of comparison.
>But rowids are not sequential integers, at least not in IDS. The real
>trick will be defining correct arithmetic on rowids (equality comparison
>and subtraction in this case). Given that rows can move around, I wonder
>how that might work. Anyone? It would be an interesting object (type and
>relevant methods.)
>It's a simpler problem if you're talking about the sequence of rows in an
>ordered result set, but still not trivial. An unordered result set
>(remembering that SQL results are not inherently ordered) is much harder.
>Cheers,
>Dick Snoke
>Executive IT Specialist
>IBM Software Group - ChannelWorks
>Tel: (404) 487-1595
>Email: dsnoke@us.ibm.com
>>From:
>>"DAVE GRIFFEN" <dgriffen@finishline.com>
>>To:
>>ids@iiug.org
>>Date:
>>12/11/09 03:28 PM
>>Subject:
>>Re: arithmetic function [18362]
>>Sent by:
>>ids-bounces@iiug.org
>>
>>Gilles,
>>If I understand your question correctly, you need to tie the table back to
>>itself with a join criteria which will link to your definition of previous
>>row. Something like...
>>
>>select a.rowid, a.value, a.value - b.value
>>from mytable a, mytable b
>>where a.rowid -1 = b.rowid
--GILLES TCHAPPI
--
--Hi,
--
--Can someone please help.
--Is there a way to retrieve the value of some column before/after the
curent
--column matching some 'where clause'. My goal is to retrieve in one shot,
with
--one 'select statement', for instance the value of the current row and
the
--difference (for the same column) between the value of the current row
and
the
--value of the previous row ie for the same column : value of current row
-
value
--previous row
--
--Regards
Save, you cannot use ROWID in that way. There is no absolute relationship
between one rowid and another and the order in which rows were added. If
mulitple processes are inserting rows, consecutive rows can be inserted onto
different pages causing an rowid that's far more than one off from the
previous row. Also if a row were to be inserted and rolled back after
another row was added there would be that gap of at least two between the
two rows and the next row might even have a smaller rowid.
On the other hand, your method is sound, as long as Gilles can use a key or
set of key columns to determine the 'consecutiveness' of two rows for such a
comparison.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Fri, Dec 11, 2009 at 3:27 PM, DAVE GRIFFEN <dgriffen@finishline.com>wrote:
> Gilles,
> If I understand your question correctly, you need to tie the table back to
> itself with a join criteria which will link to your definition of previous
> row. Something like...
>
> select a.rowid, a.value, a.value - b.value
> from mytable a, mytable b
> where a.rowid -1 = b.rowid>
> --GILLES TCHAPPI
> --
> --Hi,
> --
> --Can someone please help.
> --Is there a way to retrieve the value of some column before/after the
> curent
> --column matching some 'where clause'. My goal is to retrieve in one shot,
> with
> --one 'select statement', for instance the value of the current row and the
> --difference (for the same column) between the value of the current row and
> the
> --value of the previous row ie for the same column : value of current row -
> value
> --previous row
> --
> --Regards
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747b10434d4f8047a9482b2
When a row is moved, say a variable length row moving due to outgrowing its
home page, it keeps its rowid, because the original slot location (which is
the actual address identified by the rowid) on the home page is replaced
with a forwarding pointer to the new location. However, if the table were
to be reorganized or altered with an operation that can't be performed in
place, or if a CLUSTERED index were to be (re)created on the table, then
most rows' rowids would indeed change, so ultimately you are correct, rowid
is a poor identifier for data except in the VERY short term.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Fri, Dec 11, 2009 at 4:02 PM, Richard Snoke <dsnoke@us.ibm.com> wrote:
> But rowids are not sequential integers, at least not in IDS. The real
> trick will be defining correct arithmetic on rowids (equality comparison
> and subtraction in this case). Given that rows can move around, I wonder
> how that might work. Anyone? It would be an interesting object (type and
> relevant methods.)
>
> It's a simpler problem if you're talking about the sequence of rows in an
> ordered result set, but still not trivial. An unordered result set
> (remembering that SQL results are not inherently ordered) is much harder.
>
> Cheers,
> Dick Snoke
> Executive IT Specialist
> IBM Software Group - ChannelWorks
>
> Tel: (404) 487-1595
> Email: dsnoke@us.ibm.com
>
> From:
> "DAVE GRIFFEN" <dgriffen@finishline.com>
> To:
> ids@iiug.org
> Date:
> 12/11/09 03:28 PM
> Subject:
> Re: arithmetic function [18362]
> Sent by:
> ids-bounces@iiug.org
>
> Gilles,
> If I understand your question correctly, you need to tie the table back to
>
> itself with a join criteria which will link to your definition of previous
>
> row. Something like...
>
> select a.rowid, a.value, a.value - b.value
> from mytable a, mytable b
> where a.rowid -1 = b.rowid>
> --GILLES TCHAPPI
> --
> --Hi,
> --
> --Can someone please help.
> --Is there a way to retrieve the value of some column before/after the
> curent
> --column matching some 'where clause'. My goal is to retrieve in one shot,
>
> with
> --one 'select statement', for instance the value of the current row and
> the
> --difference (for the same column) between the value of the current row
> and
> the
> --value of the previous row ie for the same column : value of current row
> -
> value
> --previous row
> --
> --Regards
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747859a145282047a948f8b