473,320 Members | 1,883 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Nested use of found doesn't seem to be working

I have a simple plpgsql function that loops using a cursor and performs inserts/updates on a couple of tables. When I run the SQL piecemeal in pgAdmin's SQL editor everything looks OK. However, the IF statement that uses FOUND within the loop seems to always false. Any constructive insights would be appreciated.

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.    dcursor CURSOR (tdate date) IS SELECT * FROM recon.sp_ubsdaily_asof(tdate) AS u WHERE u.maturity_date >= tdate AND u.qty <> 0
  3.       AND NOT EXISTS(SELECT 1 FROM recon.ubs_daily_portfolio AS p WHERE u.serial_number = p.serial_number);
  4.    udaily recon.ubsdaily%ROWTYPE; 
  5.    umark recon.ubsmark%ROWTYPE;
  6.    ubscount bigint;
  7.  
  8. BEGIN
  9.    SELECT COUNT(*) INTO ubscount FROM recon.ubs_daily_portfolio;
  10.  
  11.    IF ubscount = 0 THEN
  12.       EXIT;
  13.    END IF;
  14.  
  15.    OPEN dcursor(asof);
  16.  
  17.    LOOP
  18.       FETCH dcursor INTO udaily;
  19.       EXIT WHEN NOT FOUND;
  20.  
  21.       IF (udaily.modified_date <> asof) THEN
  22.          INSERT INTO recon.ubsdaily (serial_number, modified_date, trade_date, maturity_date, deal_price, strike, trade_type, market,
  23.             product_code, product_name, acct_number, qty, ccy_code, strategy, tp_id, arcim_id)
  24.             VALUES(udaily.serial_number, asof, udaily.trade_date, udaily.maturity_date, udaily.deal_price, udaily.strike, 
  25.             udaily.trade_type, udaily.market, udaily.product_code, udaily.product_name, udaily.acct_number, 0, udaily.ccy_code, 
  26.             udaily.strategy, udaily.tp_id, udaily.arcim_id);
  27.       ELSE 
  28.          UPDATE recon.ubsdaily SET qty = 0 WHERE id = udaily.id;
  29.       END IF;
  30.  
  31.       SELECT m.* INTO umark FROM recon.ubsmark AS m WHERE m.serial_number = udaily.serial_number 
  32.          AND m.mark_date = (SELECT MAX(mm.mark_date) FROM recon.ubsmark AS mm WHERE mm.serial_number = udaily.serial_number AND mm.mark_date <= asof);
  33.  
  34.       IF NOT FOUND THEN
  35.          INSERT INTO recon.ubsmark (serial_number, mark_date, mark, implied_pnl, variation_margin, premium_value, realized_pnl) 
  36.             VALUES(udaily.serial_number, asof, 0, 0, 0, 0, 0);
  37.       ELSE 
  38.  
  39.          IF (umark.mark_date = asof) THEN
  40.             UPDATE recon.ubsmark set implied_pnl = 0, variation_margin = 0, premium_value = 0, realized_pnl = umark.implied_pnl 
  41.                WHERE id = umark.id;
  42.          ELSE
  43.             INSERT INTO recon.ubsmark (serial_number, mark_date, mark, implied_pnl, variation_margin, premium_value, realized_pnl) 
  44.                VALUES(udaily.serial_number, asof, 0, 0, 0, 0, umark.realized_pnl);
  45.          END IF;
  46.  
  47.       END IF;
  48.  
  49.    END LOOP;
  50.  
  51.    CLOSE dcursor;
  52. END;
  53.  
Feb 16 '07 #1
1 1613
Speculation here,
The Manual says that FOUND is a local variable initially set to False in PLPGsql functions. Maybe you can't access it inside the nested instance. Perhaps wrapping the nested part in a begin-end block of it's own will get you an instance
Apr 3 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Andy Baker | last post by:
Hi there, I'm learning Python at the moment and trying to grok the thinking behind it's scoping and nesting rules. I was googling for nested functions and found this Guido quote:...
9
by: OKB (not okblacke) | last post by:
For a variety of reasons, I'm interested in putting together some code that will allow me to created structures out of nested classes, something like: class class1: def methA(self): print...
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
8
by: Robert W. | last post by:
I've almost completed building a Model-View-Controller but have run into a snag. When an event is fired on a form control I want to automatically updated the "connnected" property in the Model. ...
0
by: hammad.awan_nospam | last post by:
Hello, I am using ASP.NET 2.0. What I have done is nested a gridview inside another column of a gridview using a template data field column declaritively in my web form. Inside this child...
78
by: Josiah Manson | last post by:
I found that I was repeating the same couple of lines over and over in a function and decided to split those lines into a nested function after copying one too many minor changes all over. The only...
1
by: asilverpeach | last post by:
Hey Guys! Found some great scripts here on this topic but have to make to changes to the code that I can't seem to figure out. First, In the following code clicking on the headers shows the...
2
by: GISmatters | last post by:
I have unbound checkboxes in a nested gridview to allow multi-selection of "child" rows. For context, the parent gridview rows are for large "reports", the child rows are for various specific files...
4
by: henry | last post by:
Folks: Using Dreamweaver CS3... Consider a home page, "index.php" which conditionally REQUIREs one of 'N' HTML files of pure content. All site styles are specified in a master CSS file,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.