[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

temporary function in db2 sql v5r3m0

Posted on 2011-05-09
8
Medium Priority
?
1,175 Views
Last Modified: 2012-05-11
In my sql view code,
I frequently have to use this function value: week(date( concat('12/31/', char( year( now() ) ) ) ) )
that returns the number of weeks of the current year.
I would like to use it with a name, with a variable... is it possible?
(UDF doesn't work in my as400 due to a unknow cause.
thanks
0
Comment
Question by:bobdylan75
8 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35719324
udf is not working?
if you solve that problem, this problem will be solved as well
why aren't you contacting IBM support?
0
 

Author Comment

by:bobdylan75
ID: 35719349
you are in right auch!
Any way.. there are a way to declair a variable in a view code, a nd to use it
after to have assign to it a value?
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 200 total points
ID: 35719862
no
you can't have variables in views
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 200 total points
ID: 35723969
What are you expecting to use the "variable" for? Can you post an example?

At V5R3, your only options are to upgrade to V5R4 or to ensure that you have the latest DB2 group PTFs installed. IBM can't help beyond that for V5R3. If you are up to date on PTFs, it's likely that we can help with UDFs. It will probably take asking some questions and getting some responses from you.

Tom
0
 
LVL 36

Accepted Solution

by:
Gary Patterson earned 1600 total points
ID: 35734739
STRSQL or open a Run SQL Scripts window and run this DDL statement:

CREATE FUNCTION PRIORITY(indate DATE) RETURNS CHAR(7)  
LANGUAGE SQL                                            
BEGIN                                                  
RETURN(                                                
      CASE   WHEN indate>CURRENT DATE-3 DAYS THEN 'HIGH'
         WHEN indate>CURRENT DATE-7 DAYS THEN 'MEDIUM'  
         ELSE 'LOW'                                    
      END                                              
      );                                                
END                                      
             

What happens?  If you got an error message, post it.

Then run the UDF you just created:

select distinct priority(current_date) from qiws/qcustcdt

What result did you get?  I tested both on a V5R3 machine and they worked fine.

- Gary Patterson
0
 

Author Comment

by:bobdylan75
ID: 35736012
Gary your function works properly!
I used the operationnavigator tool in order to make my function,
it's possible that the issue was there..
0
 

Author Comment

by:bobdylan75
ID: 35736020
ops sorry... I'm using at the moment a v5r4m0...
sorry but I use several mainframe and I often mistake.
But I will try this function yet in a v5r3m0 system soon
0
 

Author Closing Comment

by:bobdylan75
ID: 35736024
thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question