#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    1
    Rep Power
    0

    A strange procedure bug?


    Hi there, I'm working on a project involving a postgresql database and I run into a strange error that I'm not sure how to resolve.

    I have two basic queries done differently that return the same row.

    -- base queries returning a row I'm looking for
    select * from medscheck where medscheck_id = 3;
    select * from medscheck where resident_id = 73373 limit 1 ;


    I use these queries in the stored procedure, first query works and the second one doesn't. Have no idea how that's possible unless there is some underlying corruption in the table. Not sure how to fix that considering the table is quite large and with many FK constrains.


    _last_annual_completed medscheck%ROWTYPE;
    ...

    -- the variable gets a row assigned to it
    select into _last_annual_completed * from medscheck where medscheck_id = 1574;

    -- Fails, the variable contains an empty row without data
    select into _last_annual_completed * from medscheck where resident_id = 73373 limit 1 ;


    Any ideas or help would be greatly apprecited, thank you very much.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Are you sure there is a record with residentid=73373?
    What does FOUND say after you've run the query?
    And does the other query begin to fail when you add LIMIT 1 to it?

IMN logo majestic logo threadwatch logo seochat tools logo