Fill blank rows data with above row ID.

Hi,
I have a table that has data in following format..

Database : SYBASE

Data_ID      Translation                    PID
-------              ----------                    ----
36424      Very satisfied           1744
36425      Sooner satisfied        1744
36426      Yes                   1745
36427      test txt1      
36428      test txt2      
36429      Policy decisions         1747
36430      others1.3             
36431      No                              1748
36432      Training attractive       1746
36433      vocational training       1746
36434      others1.5      
36435      Yes                      1749
36436      sharing experience      1751
36437      developing  project       1751
36438      others 2.2

I want output following way

Data_ID      Translation                    PID        updatedPID
-------              ----------                    ----        ------------
36424      Very satisfied            1744           1744
36425      Sooner satisfied         1744           1744
36426      Yes                    1745           1745
36427      test txt1                                      1745
36428      test txt2                                        1745      
36429      Policy decisions          1747           1747
36430      others1.3                                      1747
36431      No                               1748          1748
36432      Training attractive        1746          1746
36433      vocational training        1746          1746
36434      others1.5                                       1746
36435      Yes                        1749         1749
36436      sharing experience      1751         1751
36437      developing  project       1751         1751
36438      others 2.2                                      1751

Basically, the blank PID should be filled be above row PID.

Please suggest me some queries in sybase. It is urgent,
LVL 5
sivachirravuriTeam Lead Asked:
Who is Participating?
 
carsRSTCommented:
If last question is true, then might try this.  Added the alias to the subquery (might make a difference)



select a.data_id, a.translation, a.PID, 

when isnull(a.PID) or PID = "" then (select b.PID from [table] b where b.data_id = (select max(c.data_id) FROM [table] c where c.data_id < a.data_id and c.PID is not null))
else PID
end as updatedPID

from [table]

Open in new window

0
 
Jan FranekCommented:

SELECT Data_ID, Translation, PID, 
  ( SELECT TOP 1 PID 
    FROM YourTable T2  
    WHERE T2.Data_ID <= T1.Data_ID
    AND PID is not null
    ORDER BY Data_ID DESC ) as updatedPID
FROM YourTable T1

Open in new window

0
 
carsRSTCommented:
Try this sql (and logic)....

select a.data_id, a.translation, a.PID, 

when isnull(a.PID) or PID = "" then (select b.PID from [table] b where b.data_id = (select max(c.data_id) FROM [table] c where c.data_id < a.data_id and PID is not null))
else PID
end as updatedPID

from [table]

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sivachirravuriTeam Lead Author Commented:
Thanks a lots for Quick reply.

Jan_Franek:
I tired to implement this solution and it throws following error.
Error:  Incorrect syntax near the keyword 'top'.  

carsRST:
I tried your solution before as well. It gives wrong output.

Wrong output is shown due to usage of MAX..

Following is the output:

Data_ID      Translation                    PID        updatedPID
-------              ----------                    ----        ------------
36424      Very satisfied                1744           1744
36425      Sooner satisfied            1744           1744
36426      Yes                                1745           1745
36427      test txt1                                             1745  
36428      test txt2                                             1745      
36429      Policy decisions              1747          1747
36430      others1.3                                           1747
36431      No                                   1748          1748
36432      Training attractive           1746          1746
36433      vocational training           1746          1746
36434      others1.5                                           1748     <<<->>>> this row should have 1746 not 1748
36435      Yes                                  1749         1749
36436      sharing experience           1751         1751
36437      developing  project           1751         1751
36438      others 2.2                                          1751

0
 
carsRSTCommented:
Sorry "max" was supposed to be "min"



select a.data_id, a.translation, a.PID,

when isnull(a.PID) or PID = "" then (select b.PID from [table] b where b.data_id = (select min(c.data_id) FROM [table] c where c.data_id < a.data_id and PID is not null))
else PID
end as updatedPID

from [table]
0
 
carsRSTCommented:
Sorry ignore last statement.  won't work.  Trigger happy.
0
 
carsRSTCommented:
So my output only gave the one row incorrect, right?
0
 
Jan FranekCommented:
What version of Sybase server are you running (select @@version) ? Top functionality is supported for quite long time.
0
 
sivachirravuriTeam Lead Author Commented:
Jan_Franek, I use Adaptive Server Enterprise 15.0.1 version. Yes "Top" works in other queries. I am not sure why it haven't worked here. I tried to solve it. But, was not successful.

Anywaz, I found the solution now. I have modified little bit from the carsRST query and it worked correctly. Here is what i have done.

select a.data_id, a.translation, a.PID,

when isnull(a.PID) or PID = "" then (select b.PID from [table] b where b.data_id = (select max(c.data_id) FROM [table] c where c.data_id <= a.data_id and c.PID is not null))
else PID
end as updatedPID

from [table]
 
I have changed "c.data_id < a.data_id" AS "c.data_id <= a.data_id" in subquery.

I really appreciate your quick replies. Thanks a lot.
0
 
sivachirravuriTeam Lead Author Commented:
Answer was partially incorrect. I have correct it. plz check my comments.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.