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.

Create dynamic cursor got ERROR: Invalid use of an aggregate function or ...

Hi All,

I am new in SQL/PL, I wrote following procedure to return min and max value (type: TIMSTAMP)in DB2 v9.5. IT is a dynamic SQL since i would like pass fieldname and table name as parameter.

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE get_min_max_time( 
  2.   IN p_schemaname ANCHOR maint.archive_cntl.del_schema,
  3.   IN p_tablename  ANCHOR maint.archive_cntl.del_table,
  4.   IN p_fieldname   ANCHOR maint.archive_cntl.del_table_time_col_name,
  5.   OUT p_min_ts  TIMESTAMP,
  6.   OUT p_max_ts  TIMESTAMP)
  7. LANGUAGE SQL
  8. BEGIN 
  9.  
  10.   DECLARE stmt VARCHAR(1000);
  11.  
  12.   SET stmt = 'SELECT ' || MIN(p_fieldname) || ',' || MAX(p_fieldname) || ' from ' || trim(p_schemaname) || '.' || trim(p_tablename);
  13.  
  14.  
  15.   PREPARE S1 FROM stmt;
  16.  
  17.   DECLARE c1 CURSOR FOR S1;
  18.  
  19.   OPEN c1;
  20.   FETCH c1 into v_min_ts, v_max_ts;
  21.  
  22.   CLOSE C1;
  23. END@
-------------
when compiling, i got following error:


DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0120N Invalid use of an aggregate function or OLAP function. LINE
NUMBER=1. SQLSTATE=42903

Any idea what is wrong with the above code?

thanks.
Jul 14 '13 #1
1 2907
Hi,
I fixed this issue. the Dynamic sql should be written as:

SET stmt = 'SELECT MIN(' || p_fieldname || ') , MAX (' || p_fieldname || ') from ' || trim(p_schemaname) || '.' || trim(p_tablename);
Jul 19 '13 #2

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

Similar topics

2
by: Claudio Lapidus | last post by:
Hello I would like to know how can I define/create a new aggregate function. I need a custom function that operate on a set of text strings and return a certain string aggregate based on certain...
3
by: Wiggy | last post by:
Hi, It's probably easiest if I describe what I'm trying to do: I have several tables I want to base a query on. In addition I have some dynamic data that I want to join against that consists...
1
by: Najib Abi Fadel | last post by:
Hi i have an ordered table of dates let's say: 1/1/2004 8/1/2004 15/1/2004 29/1/2004 5/2/2004 12/2/2004
5
by: peppi911 | last post by:
Hi, is it possible to create a cursor from a dynamic string? Like: DECLARE @cursor nvarchar(1000) SET @cursor = N'SELECT product.product_id FROM product WHERE fund_amt > 0' ...
1
by: R.A.M. | last post by:
Hello, I am learning SQL Server 2005. I have (correctly) written in .NET assembly DemoSQLServer with aggregate function AvgNoMinMax in class Demo and I have added assembly to database...
0
by: JimSnyder | last post by:
I am trying to learn how to use a basic natural dynamic cursor for use in Pro*C and am getting the following error I cannot get past: "Missing IN or OUT parameter at index:: 1" Here is the query...
5
by: BillCo | last post by:
I just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: ...
3
by: Aaron | last post by:
I have been searching the boards trying to find an answer to this question and no luck. I am using a query similar to this: Select count(col1) from table1 I was having a hard time accessing...
3
by: ncsthbell | last post by:
I am pulling my hair out on this! Seems like it should be easy, I just can not get it to work like I want. I know I am doing something wrong, so I hope someone can be so kind to guide me!!! I...
1
by: Sandro997 | last post by:
Ok. I have a bit of a dilemma here. First, please consider the following function: CREATE OR REPLACE FUNCTION recent_lab(text, labs, treatments) RETURNS float8 AS $BODY$select...
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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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...
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
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
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.