combine 3 record into 1 record by stored in different field

combine 3 record into 1 record by stored in different field
original
GameID CODESTATUS COUNT
====== ========== =======
GAMEA A 5
GAMEA T 10
GAMEA R 15

TO

GAMEID A_STATUS T_STATUS R_STATUS
======== ======== ========= ==========
GAMEA 5 10 15

===================================================

select gameid, count(gameid) as count, sum(aCount) as Available,sum(tCount) As Taken,sum(rCount) as Reserved from (
select gameid, codestatus,
case when codestatus='A' then 1 else 0 end as aCount,
case when codestatus='T' then 1 else 0 end as tCount,
case when codestatus='R' then 1 else 0 end as rCount
from tibsadmin.gamename_code
) as a group by (gameid)

Check index db2

db2 "describe indexes for table tibsadmin.fundtrasnferhistory"

SELECT colnames,tbname FROM SYSIBM.SYSINDEXES
where tbname ='ECEREMITTANCE';

Usefull sql script

sql to create backup table

CREATE TABLE UserBackup like User NOT LOGGED INITIALLY;
INSERT INTO UserBackup (SELECT * FROM User );

SQL UNION and UNION ALL

UNION

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.

UNION ALL

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

Use "union all" when u wan all data for sql1 & sql2.
:)
"union" sometime might cause data missing if data from sql1 & sql2 are same.

ref

Cursor closes at COMMIT unless you use the WITH HOLD option on cursor.

Cursor closes at COMMIT unless you use the WITH HOLD option on cursor.

Example
OPEN UPDATE_CURSOR WITH HOLD;

-- Declare cursor
FOR vl AS
c1 CURSOR with hold FOR
 SELECT customer_id as id,full_name from customer where full_name like 'test%'
DO
   update customer set status ='A' where customer_id = id;
   COMMIT;   <======= Can commit inside Loop if you choose WITH HOLD 

END FOR;
COMMIT;

ref ,
ibm

alter table in stored procedure

CREATE PROCEDURE ppg_gc (IN expiryTime INTEGER, IN batchSize INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN


declare v_stmt varchar(20);
--now store ur query inside this variable,then prepare the statement.

v_stmt = 'alter table emp drop primary key';
prepare S1 from v_stmt;
EXECUTE IMMEDIATE S1;

END @


ref

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

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

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