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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.