Doing LOOP in Stored Procedure

Code

DECLARE v_start int default 0;--Start month
DECLARE v_end int default 12;--End month

set v_start = 3;
set v_start = 6;
--[BEGIN] LOOP
L1: loop

if (v_start > v_end  ) then leave L1;
end if;
set v_start = v_start+1; 
--DO SOMETHING 
END FOR;
--[END] LOOP

Is there an equivalent DB2 syntax for the Oracle DECODE function?

QUESTION POSED ON:
Is there an equivalent DB2 syntax for the Oracle DECODE function?

EXPERT RESPONSE

Well, first of all, let's explain the Oracle DECODE expression for those not familiar with Oracle. A DECODE expression will look like this:

DECODE(expr,search,result,default)

There can be multiple search values and results, and default is optional. To evaluate this expression, Oracle compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, null is returned. If expr and search contain character data, Oracle compares them using nonpadded comparison semantics. The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255.

So, basically, DECODE changes the value of an expression if the expression is equal to one of the values in the searched list. For example, this expression decodes the value deptno. If deptno is 10, the expression evaluates to 'ACCOUNTING'; if deptno is 20, it evaluates to 'RESEARCH'; etc. If deptno is not 10, 20, 30, or 40, the expression returns 'NONE'.

DECODE (deptno,10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES',
40, 'OPERATION',
'NONE')

In DB2 this can be accomplished using CASE expression. To write the equivalent of the above using DB2 you can write the following SQL statement:

SELECT CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
WHEN 40 THEN 'OPERATIONS'
ELSE 'NONE'
END CASE
FROM EMP;

ref1,ref2

Query by pagination

ref

Code

-- IBM DB2
SELECT * FROM
(
SELECT a.* , rownumber() over () AS rn
FROM
(
SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
ORDER BY OrderDate DESC, ShippingDate DESC
) AS a
) AS rs
WHERE rs.rn between
(((pageNumber-1) * pageSize)+1)
AND
(pageNumber * pageSize)

Posted by AntiHuman at

How to trim leading zeroes when converting decimal() to char()

I need to convert a decimal field to char() but also trim the leading
zeroes. Any idea? I could not find any function to do that.

Re: How to trim leading zeroes when converting decimal() to char()

---

I'm sure there's a better way, but:

with sample table testdec:

create table testdec (col1 dec(10,5))

some sample data:

insert into testdec values (0005.500),(50.5),(12345.12345)

We then take a substring on a char of the first 5 characters (the
precision of the decimal value), convert it into an int to drop the
leading zeros, convert it back into a char, and then perform a right
trim (rtrim) on it to get rid of the padded blanks. We concatenate this
with the substring of the decimal separator and the digits to the right
of the decimal point (the scale), and convert this into a char.

select rtrim
( char(int(substr(char(col1),1,5))) )
concat
substr(char(col1),6,6) from testdec

1
-----------------
5.50000
50.50000
12345.12345

Like any example I provide, please test extensively with boundary
conditions.

ref

Code for conversion Dec(15,2):

 SELECT
rtrim(
char(
int(
substr(
char( cast('3333310.2' as dec(15,2)) ),
1,13)
)
)
)
concat
substr(
char( cast('3333310.2' as dec(15,2)) )
,14,3)
from sysibm.sysdummy1

RESULT=> 3333310.20

Or simpler solution

replace(replace(rtrim(ltrim(replace(char(), '0', ' '))), ' ', '0'), '.', '')
ref

RoA From Malaysia Beat T_T?

RoA From Malaysia Beat T_T?

ROA was the winner for Sino dota
sinodota.wordpress.com

Check this out http://www.meetyourmakers.com

Javascript Copy Selected Text Box Select All Highlight Text

The below link is
Javascript Copy Selected Text Box Select All Highlight Text

www.wallpaperama.com

But this will not work in firefox
because document.selection.createRange() is undefined
You need something like

Code:

 if (window.getSelection)
{
txt = window.getSelection();
foundIn = 'window.getSelection()';
}
else if (document.getSelection)
{
txt = document.getSelection();
foundIn = 'document.getSelection()';
}
else if (document.selection)
{
txt = document.selection.createRange().text;
foundIn = 'document.selection.createRange()';
}
else return;

Too bad later i found that window.getSelection will not work in textarea.. and yet this is bug for firefox according
http://www.squarefree.com

Code:

 function copyit(theField) {

var selectedText = document.selection;

var txt;
var foundIn;

if (window.getSelection)
{
txt=getSelectionInfo(theField);
//alert('ori='+theField.value.length);
   //alert('new='+txt.length);
if(txt.length==theField.value.length )
theField.value='';
}
else if (document.getSelection)
{
alert('2='+document.getSelection);
txt = document.getSelection();
foundIn = 'document.getSelection()';
theField.focus();
theField.value = txt;
}
else if (document.selection)
{
alert('3='+document.selection);
if (document.selection.type == 'Text') {

 txt = document.selection.createRange().text;
 alert('ori='+theField.value.length);
 alert('3='+txt.length);
 if(theField.value.length==txt.length)
 alert('clearAll!!');
 foundIn = 'document.selection.createRange()';
 theField.focus();
 theField.value = txt;
}
}
else return;

;

}
function getSelectionInfo(theField)
{
var rv = "";
var i,x;

x = theField;

 rv +=  x.value.substr(x.selectionStart, x.selectionEnd - x.selectionStart);

if(x.selectionStart==0 && x.selectionEnd==x.value.length)
return rv;
}

Selecting from an Insert, Update, Delete statement

Selecting from an Insert, Update, Delete statement

SELECT * FROM FINAL TABLE (INSERT INTO X VALUES …..)
This statement has three modes you can select from. The are

• OLD TABLE: before the data change statement operation has been executed
  
• NEW TABLE: just after the data change statement operation has been executed, but before both referential integrity evaluation and the firing of defined after-triggers.
  
• FINAL TABLE: after the operation has been executed, and after all referential integrity evaluations and after-triggers have been fired.

/blogs.ittoolbox.com

SQL6036N START or STOP DATABASE MANAGER command is already in progress.

db2stop
SQL6036N START or STOP DATABASE MANAGER command is already in
progress.
$


Is there any way I can fix this without restarting the OS. I am
running DB2 UDB 8.1 on AIX box?

ans:
Well ... you can either open up a support incident or if you want to
proceed at your own risk ... try using the db2stop force command and if
all else fails, db2_kill. Sounds like the previous stop is hung.

ref=webservertalk.com

FOR v2 AS
c2 CURSOR FOR
select distinct
a.userid,
RTRIM(char(MONTH(a.SESSIONSTARTON) ))
|| RTRIM(char(YEAR(a.SESSIONSTARTON))) as createdMth,
case
(
select distinct 1
from IBSADMIN.ActionAuditTrail aa,UserLoginAuditTrail ll
where
aa.LOGINTRAILID = ll.LOGINTRAILID
and aa.action ='auditAction.login'
and month(aa.createdOn) = 3
and year(aa.createdOn) = 2007
and ll.userid = a.userid
) when (1) then 1 else 0 end as status,
date(a.SESSIONSTARTON) as createdOn
from IBSADMIN.anonymousactionaudittrail a
where a.action ='auditAction.registration'
and month(a.SESSIONSTARTON) = 3
and year(a.SESSIONSTARTON) = 2007
and not exists
(
select 1
from ibsadmin.rpt_reguser_login rr
where rr.userid = a.userid
and rr.createdon =
RTRIM(char(MONTH(a.SESSIONSTARTON) ))
|| RTRIM(char(YEAR(a.SESSIONSTARTON)))
)and a.userid is not null
--end of Cursor

DO
--Inserting into Rpt table
INSERT INTO IBSADMIN.RPT_REGUSER_LOGIN
(USERID,CREATEDMTH,STATUS,CREATEDON)
VALUES (userid,createdMth,status,createdOn);
END FOR;
COMMIT;

Transact-SQL

query uses EXISTS

SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT *
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');

query uses IN

SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE a.LastName IN
(SELECT a.LastName
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');

Here is the result set.

FirstName                                          LastName
-------------------------------------------------- ----------
Barry                                              Johnson
David                                              Johnson
Willis                                             Johnson
(3 row(s) affected)

select i.userid,i.action,i.createdOn,count(*)
from
(

select
a.action,
l.userid,
RTRIM(char(MONTH(createdOn) )) || RTRIM(char(YEAR(createdOn))) as createdon
from IBSADMIN.ActionAuditTrail a,UserLoginAuditTrail l
where a.action ='auditAction.registration'
and a.LOGINTRAILID = l.LOGINTRAILID
and month(a.createdOn) = 3
and EXISTS
   (
   select 1
   from IBSADMIN.ActionAuditTrail aa,UserLoginAuditTrail ll
   where aa.action ='auditAction.login'
   and month(aa.createdOn) = 3
   and l.userid = ll.userid
   )

)
i group by  i.userid,i.action,i.createdOn

ref=technet.microsoft.com

DB2 Basics: Fun with Dates and Times

YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)

concatenate date or time values with other text?
LTRIM(RTRIM(char(MONTH(createdOn)) ))
|| LTRIM(RTRIM(char(YEAR(createdOn))))

output=> 32007

** LTRIM=Left Trim
 RTRIM=Right Trim


ref:developerWorks

Error creating simple procedure

Check this out.

"I'm trying to create a procedure and encountering all sorts of errors. I initially tried an example from Osborne's Complete DB2 Reference, an then resorted to something even simpler but still no luck.

CREATE PROCEDURE test (
IN x smallint,
OUT y smallint)
DYNAMIC RESULT SETS 0
READS SQL DATA
LANGUAGE SQL
BEGIN
SET y=x+1
END

returns the error
SQL0104N An unexpected token "END" was found following "QL BEGIN SET y=x+1 ". Expected tokens may include: "". LINE NUMBER=8. SQLSTATE=42601

Sounds like it wants a semi-colon, so I add one after x+1 and get two errors:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "SQL BEGIN SET y=x+1". Expected tokens may include: "". LINE NUMBER=8. SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN ". SQLSTATE=42601"

It all due to the reason:
How you are compiling that procedure.if you put it in an script file,
use @ as a signal to the end of procedure and use ; as the end of statements
with in the procedure, then use

db2 -td@ -f

any time you misses the t option db2 takes ; as the end of statement.

You will have to change the Statement Delimiter in your tool to @...
OK, at the bottom of the command editor window is a tiny textbox.
In English versions it says "termination character" in blue in front of
it. When you bring up command editor the text box should contain a
semicolon (;).
All you need to do is edit the textbox to be a "@" or perhaps a dollar
"$" sign.


By default, most tools use ;

DB2 Sql

How to convert VARCHAR to SMALLINT using DB2?
ans:CAST(B.mySmallIntAsString AS DECIMAL)


How to run sql script from db2 console?
db2 -tvf script_name.sql -z logfile_name.log

Prevent db2 attempts to process all the SQL in the file
and posts errors as they occur.
Use -s option. It will stop at the first error.
db2 -s -tvf