create function

kirant
Padawan

hello, 

 

i'm trying to run the create function for passing my data into chunks to a query, but i'm unable to do so.

is create function option same as create stored procedure, any example/tutorial should be really helpful. I'm unable to start on my own.

 

kirant_0-1635322129338.png

VARIABLE= SELECT COUNT (*) FROM TABLE1
RUN_LOOP= (VARIABLE / 10) +1
COUNTER = 1
start= 0
END_ALL =1000

BEGIN
   WHILE @COUNTER <@RUN_LOOP
       SELECT * FROM TABLE2
       WHERE RN BETWEEN @start+1  AND @END_ALL 
     
    COUNTER = COUNTER+1
    @start=END_ALL + 1
    @END_ALL = END_ALL+1000

END

 

 

want to achieve something like this in the function, 

How loop values should change:

VARIABLE    RUN_LOOP    COUNTER start    END_ALL
3125           4           1      0      1000
3125           4           2      1001   2000
3125           4           3      2001   3000
3125           4           4      3001   4000

 

2 REPLIES 2

mwellbro
Xpert

Hi @kirant ,

for your first point ( I think that UI is DBViz ? ), see here under "known issues" : https://docs.exasol.com/connect_exasol/sql_clients/db_visualizer.htm

mwellbro_0-1635348054149.png

For the second part I´ll have to write a bit more, will get back to you on that.

Cheers,
Malte

P.S.: Welcome to the community 😀



mwellbro
Xpert

Hi again,

for the second part of your question, I think this might be informative for you: https://community.exasol.com/t5/discussion-forum/view-based-on-udf-result/m-p/6167/highlight/true#M1...

The syntax in your example seems like it´s thought along the lines of a cursor iterating over a data set and reading the result "into your function" in order to do something with it - this would probably not be the way you´d want to handle something like this, I think.

If the goal is to "chunk up" TABLE2, maybe a "pure SQL-way" might be of use to you ?

with 
  base as (select ROW_NUMBER() over(order by level) as id , 'test' as foo from dual connect by level<=4000)
, meta as (select 4 as run_loop,(select count(*) from base) as variable )
select 
variable,run_loop,ceil(id/ (variable/run_loop) ) as counter,min(id),max(id),count(*)
 from base a cross join meta
group by 1,2,3
order by 1,2,3
;

mwellbro_0-1635368595791.png


Cheers,
Malte