473,326 Members | 2,013 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,326 software developers and data experts.

I have a function that modifies data and returns a scalar. The function got created,

I tried using a function which returns a table as
Returns (date)
return value (actual_return)

as I dint see any way to call a scalar function from a select statement, I thought of using a table function and calling it in the select statement but this gives an error and searches for a column name after Returns (date..

Bellow is the function

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE  FUNCTION ACHINST1.AGING (
  2.     X    DATE,
  3.     Y    INTEGER )
  4.   returns table(date)
  5.  
  6.   LANGUAGE SQL
  7.  
  8.  
  9.   MODIFIES SQL DATA
  10. begin
  11.  
  12. declare counter_insert int;
  13. declare counter_tat int;
  14. declare store int;
  15. declare i int;
  16. declare creation_day int;
  17. declare creation_month int;
  18. declare creation_year int;
  19. declare expairy_day int;
  20. declare tat int;
  21. declare actual_month int;
  22. declare tat_date varchar(20);
  23. declare month_days int;
  24. declare expairy_id int;
  25. declare return_value varchar(20);
  26. declare holiday_counter int;
  27. declare actual_return date;
  28.  
  29. declare c1 cursor for
  30. select length(B.days|| B1.days) from ACHINST1.DMSBUSINESSCALENDAR as B inner join ACHINST1.DMSBUSINESSCALENDAR as B1 
  31. ON B1.BC_MONTH = (CASE WHEN B.BC_MONTH <  11 THEN B.BC_MONTH+1 ELSE 0 END)
  32.  AND B1.BC_YEAR = (CASE WHEN B.BC_MONTH =  11 THEN B.BC_YEAR+1  ELSE B.BC_YEAR END) 
  33.  where  B.STATUS = 'ACTIVE' and B.calendarkey = 'INR' and B1.CALENDARKEY = 'INR' and B1.status = 'ACTIVE'
  34.  and  b.bc_month = actual_month and b.bc_year = creation_year;
  35. set counter_insert  = 1;
  36. set creation_day = day(x);
  37. set creation_month = month(x);
  38. set actual_month = creation_month -1;
  39. set creation_year = year(x);
  40. set expairy_day = creation_day;
  41. set expairy_id=creation_day;
  42.  
  43. set tat = y;
  44. set counter_tat = 1;
  45.  
  46.  
  47. if actual_month = 0
  48. then set month_days = 31;
  49.  
  50. elseif actual_month = 1
  51. then 
  52. if ((mod(creation_year,4) = 0) AND ((mod(creation_year,100) != 0) OR (mod(creation_year,400) = 0)))
  53. then set month_days = 29;
  54. else
  55. set month_days = 28;
  56. end if;
  57.  
  58. elseif actual_month = 2
  59. then set month_days = 31;
  60.  
  61. elseif actual_month = 3
  62. then set month_days = 30;
  63.  
  64. elseif actual_month = 4
  65. then set month_days = 31;
  66.  
  67. elseif actual_month = 5
  68. then set month_days = 30;
  69.  
  70. elseif actual_month = 6
  71. then set month_days = 31;
  72.  
  73. elseif actual_month = 7
  74. then set month_days = 31;
  75.  
  76. elseif actual_month = 8
  77. then set month_days = 30;
  78.  
  79. elseif actual_month = 9
  80. then set month_days = 31;
  81.  
  82. elseif actual_month = 10
  83. then set month_days = 30;
  84.  
  85. elseif  actual_month = 11
  86. then set month_days = 31;
  87. end if;
  88.  
  89.  
  90.  
  91.  
  92.  
  93.  
  94. open c1;
  95. fetch c1 into store;
  96. close c1;
  97.  
  98.  
  99.  
  100. while (counter_insert <= store) do
  101.  
  102. insert into achinst1.calender values (counter_insert, (select substr((B.days|| B1.days),counter_insert,1)  from ACHINST1.DMSBUSINESSCALENDAR as B inner join ACHINST1.DMSBUSINESSCALENDAR as B1 
  103. ON B1.BC_MONTH = (CASE WHEN B.BC_MONTH <  11 THEN B.BC_MONTH+1 ELSE 0 END)
  104.  AND B1.BC_YEAR = (CASE WHEN B.BC_MONTH =  11 THEN B.BC_YEAR+1  ELSE B.BC_YEAR END) 
  105.  where  B.STATUS = 'ACTIVE' and B.calendarkey = 'INR' and B1.CALENDARKEY = 'INR' and B1.status = 'ACTIVE'
  106.  and  b.bc_month = actual_month and b.bc_year = creation_year));
  107. set counter_insert = counter_insert+1;
  108. end while;
  109.  
  110.  
  111. while (counter_tat <= tat) do
  112.  
  113.  
  114. if exists (select value from calender where id = expairy_id and value = 1)
  115. then
  116. set counter_tat = counter_tat+1;
  117. end if;
  118. if expairy_day = month_days
  119. then
  120.     if creation_month = 12
  121.     then
  122.     set expairy_day = 1;
  123.     set creation_month = 1;
  124.     set creation_year = creation_year+1;
  125.     else
  126.     set expairy_day = 1;
  127.     set creation_month = creation_month + 1;
  128.     end if; 
  129. else
  130. set expairy_day = expairy_day+1;
  131. end if;
  132. set expairy_id =  expairy_id+1;
  133.  
  134. end while;
  135.  
  136. set holiday_counter = counter_tat + 1;
  137.  
  138. while exists (select value from calender where id = expairy_id and value = 0) do
  139.  
  140.  
  141.         if expairy_day = month_days
  142.         then
  143.             if creation_month = 12
  144.             then
  145.             set expairy_day = 1;
  146.             set creation_month = 1;
  147.             set creation_year = creation_year+1;
  148.             else
  149.             set expairy_day = 1;
  150.             set creation_month = creation_month + 1;
  151.             end if; 
  152.         else
  153.         set expairy_day = expairy_day+1;
  154.        end if;
  155.        set expairy_id =  expairy_id+1;
  156. end while;
  157.  
  158. set return_value = (creation_year || '-' ||creation_month || '-' || expairy_day);
  159.  delete from achinst1.CALENDER;
  160.  
  161. set actual_return =  date(to_date(return_value,'DD-MM-YYYY'));
  162.  
  163. return values (actual_return);
  164. commit;
  165. end;
May 29 '14 #1
0 1260

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

Similar topics

0
by: Sachin Narasimhan via .NET 247 | last post by:
(Type your message here) Hi, I am at my wits end. It would be great if someone can help. Wehave a Stored Function(Oracle) that returns a boolean that wecannot change to return something else and we...
2
by: db2sysc | last post by:
I am getting SQL0628N when I run function with INSERT and MODIFIES SQL DATA Version: DB2 v8.1.7.445 Fixpack 7. Create function test1( a int, b int) returns integer language sql
11
by: Sathyaish | last post by:
In a thread on JoS, Quickie asked, "Write a function in C that returns a function that adds x to its argument." http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=146457 ...
5
by: Daz | last post by:
Hi everyone. My query is very straight forward (I think). What's the difference between someFunc.blah = function(){ ; } and
0
by: ZXDALMA | last post by:
Dear colleagues, I'm programming in ADS-OnLine Language attached to a DB2 V7R1 server which acts as a data repository. I need to know how to use the scalar function IDENTITY_VAL_LOCAL in order...
0
by: mav | last post by:
I've created an external scalar function (Java) on our iSeries v5r3 that connects to an MS SQL server to call a stored procedure. This function is incredibly slow because it needs to connect to the...
1
by: myschelle848691 | last post by:
Hi Guys, So i've just been told that SELECT INTO cannot be used within a scalar function. Can anyone please tell me why? Secondly, can I use a cursor on a view? and all within a function? ...
3
by: =?Utf-8?B?cm9kY2hhcg==?= | last post by:
hey all, is it possible to call a sql server scalar function from c#? thanks, rodchar
3
by: Yogesh Sharma | last post by:
FIRST EXAMPLE--CREATE FUNCTION dbo.myFunction() RETURNS INT AS BEGIN DECLARE @myInt INT SET @myInt = 1 RETURN @myInt END select dbo.myFunction() as 'Simple Number'
8
by: =?ISO-8859-1?Q?m=E9choui?= | last post by:
Problem: - You have tree structure (XML-like) that you don't want to create 100% in memory, because it just takes too long (for instance, you need a http request to request the information from...
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.