Hi!!

I have a table with no unique ID, I created an Id with ROW_NUMBER ()
OVER (ORDER BY column asc) rank
and created a stored procedure with the rank and table.
In this stored procedure I want to create an extra column that checks
if the value from another columns contains value = 100

rank,Ifnummer,followingnr,productnr testing
2313,'6497269','001' ,412 , NULL
2314,'6497269','002' ,413 , NULL
2315,'6497269','003' ,100 , yes
2316,'6497269','004' ,430 ,NULL
2317,'6497269','005' ,436 ,NULL

What I want is replacing NULL values in the rest of this column if the
condition is YES in a previous row. But how can I fix this ?

this is wat I want

rank,Ifnummer,followingnr,productnr testing
2313,'6497269','001' ,412 , NULL
2314,'6497269','002' ,413 , NULL
2315,'6497269','003' ,100 , yes
2316,'6497269','004' ,430 , yes
2317,'6497269','005' ,436 , yes

Whenever you want to compare one row against the previous row, you can
use two windows: one window whose range is

BETWEEN CURRENT ROW AND CURRENT ROW

and the other

BETWEEN 1 PRECEDING AND 1 PRECEDING

There is an example of doing this in the whitepaper I co-authored with
Brendan Lucier on OLAP support in SQL Anywhere. You can find it at

http://iablog.sybase.com/paulley/whitepapers

Glenn

janis wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

i need a querysolution cause im using it in a application , 2 viewing
windows will not help.

I tried it with the following query

select *, (if productnr = 426 then 426 endif) as pnr,
isnull(pnr, (select top 1 pnr from procedure() where rank < t.rank and
pnr is not null ORDER BY rank desc)) as test
from procedure() t
where ifnummer = 6497269

but it wont work unfortunately. It just shows the results of the
previous column.

I did not mean an application window - I meant a WINDOW function in SQL.

Take a look at that whitepaper.

Glenn

janis wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport