Problem in creating View while converting to DB2

vivek.vivek · #1 (**permalink**) 01-20-09, 07:01

Please help me to convert this view form Oracle to Db2 9.5. I am facing problem in the Time stamp area.

query1:='CREATE OR REPLACE VIEW Step45_Flap1 AS
SELECT time_stamp FROM ( SELECT time_stamp FROM(
SELECT time_stamp FROM (SELECT time_stamp' ||query1||' FROM '||Param5||' GROUP BY time_stamp) WHERE time_stamp '||Param4||' TO_DATE('''||Param3||''',''MM/DD/YYYY HH24:MI

S'')))';

Thanks and Regards,
Vivek

JAYANTA_DATTA · #2 (**permalink**) 01-20-09, 08:03

I can see few posts from you today regarding Oracle to DB2 migration for SQL code. May be the following Redbook will help you to get the answer of most of the questions you asked:

IBM Redbooks | Oracle to DB2 Conversion Guide for Linux, UNIX, and Windows

vivek.vivek · #3 (**permalink**) 01-20-09, 08:21

Sir/Madam,

I have this book and am refering this. But its not giving me a full idea. That is the reason why i am posting so many mails.
I am new to this DB2.

Peter.Vanroose · #4 (**permalink**) 01-20-09, 14:25

Quote:

Originally Posted by vivek.vivek

TO_DATE(Param3,'MM/DD/YYYY HH24:MI: SS')

This is essentially a datatype cast from a textual expression to a date/time field.
DB2 has the datatype TIMESTAMP for this purpose.
In DB2, casting from text to timestamp can be done through the standard CAST(... AS ...), provided the text is in the format yyyy-mm-dd-hh.mm.ss
So you'll first have to convert Param3 to a rearranged textual form before passing it to the CAST:

Code:

CAST(   SUBSTR(P,7,4)||'-'||SUBSTR(P,1,2)||'-'||SUBSTR(P,4,2)||'-'
     || SUBSTR(P,12,2)||'.'||SUBSTR(P,15,2)||'.'||SUBSTR(P,18,2)
     AS TIMESTAMP)

Plugging this in into your meta-query (after doubling the single quotes and replacing "P" by "'||Param3||'") should give what you want.
(See also page 84 of the DB2 for LUW v9.5 SQL Reference, Volume 1 for info about using the TIMESTAMP datatype.)

Internet Services

Web Development

Digital Marketing

Consumer Tech