MQSeries.net :: View topic - Select statement in esql with oracle Date filter.

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Select statement in esql with oracle Date filter.

Select statement in esql with oracle Date filter.

« View previous topic :: View next topic »

Author

Message

mzm.shan

Posted: Wed Sep 25, 2013 9:26 am Post subject: Select statement in esql with oracle Date filter.

Apprentice

Joined: 25 Aug 2013
Posts: 39

The following code snipped is not working for me. "tl_sys_time" is the oracle column of date. the first query runs fine and give me a date and in next query i tried to get the records based on that resulted date of first query. BUT the second query runs infinite where as it should return 2 records in actual.
The funnest thing is if a uncomment the PAN filter in second query it works but i need it without PAN filter in second query which is gng infinite......... PLEASE HELP!!!!!!!!
I am using IIB v9.

1) SET Environment.Variables.ExecutionDate = THE (select t.tl_sys_time FROM Database.iris.tlog_view as t
WHERE t.PAN = '4025830268001271');

DECLARE dateExe TIMESTAMP;
SET dateExe = CAST(Environment.Variables.ExecutionDate.tl_sys_time as TIMESTAMP );

2) SET OutputRoot.XMLNSC.Data.abcd[] = (select t.tl_sys_time
FROM Database.iris.tlog_view as t
WHERE --t.PAN = '4025830268001271' and

CAST(t.tl_sys_time as TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss') = CAST(dateExe as TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss')
);

lancelotlinc

Posted: Wed Sep 25, 2013 9:38 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

Have you read this InfoCentre article where it says Oracle datatype for ESQL TIMESTAMP is a DATE ?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

mzm.shan

Posted: Wed Sep 25, 2013 9:42 am Post subject:

Apprentice

Joined: 25 Aug 2013
Posts: 39

lancelotlinc,, many thanks for your prompt help... i have read this article in fact this artical leads me to cast my given date in timestamp to oracle query for DATE column which have some these formatted values "9/24/2013 9:19:05 PM"

lancelotlinc

Posted: Wed Sep 25, 2013 9:45 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

Use interactive TOAD or equivalent to execute your queries outside of WMB proving that your queries are sound.

If so, please post each query and response here so we can see the actual data being returned.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

mzm.shan

Posted: Wed Sep 25, 2013 9:55 am Post subject:

Apprentice

Joined: 25 Aug 2013
Posts: 39

I am using TOAD... and it is giving me the following result

select t.tl_sys_time
FROM iris.tlog_view t
WHERE t.PAN = '4025830268001271'

TL_SYS_TIME

9/24/2013 9:19:05 PM
9/25/2013 11:10:00 AM
9/25/2013 11:27:06 AM
9/25/2013 11:25:30 AM
9/24/2013 11:01:04 PM

and on any given date from above dates to below query, TOAD return me this which is correct result.

select t.tl_sys_time
FROM iris.tlog_view t
WHERE
t.tl_sys_time = to_date('9/24/2013 9:19:05','MM/dd/yyyy HH24:mi:ss')

TL_SYS_TIME

9/24/2013 9:19:05 AM
9/24/2013 9:19:05 AM
9/24/2013 9:19:05 AM
9/24/2013 9:19:05 AM
9/24/2013 9:19:05 AM

lancelotlinc

Posted: Wed Sep 25, 2013 9:59 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

Ok, now split the query into two Compute nodes with a Trace file between them. Also put a Trace node after the second Compute node. Then post back here the output of the Trace nodes Environment tree.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

mzm.shan

Posted: Wed Sep 25, 2013 10:21 am Post subject:

Apprentice

Joined: 25 Aug 2013
Posts: 39

First Esql
=======

SET Environment.Variables.ExecutionDate = THE (select t.tl_sys_time
FROM Database.iris.tlog_view as t
WHERE t.PAN = '4025830268001271');

-- FORMAT 'yyyy-MM-dd HH:mm:ss

DECLARE dateExe TIMESTAMP;
SET dateExe = CAST(Environment.Variables.ExecutionDate.tl_sys_time as TIMESTAMP );
SET OutputRoot.XMLNSC.aDate = dateExe;

Trace after it with Root
===============

======= Filter 2013-09-25 23:15:02.944060==============

( ['MQROOT' : 0x29199910]
(0x01000000:Name ):Properties = ( ['MQPROPERTYPARSER' : 0x2b941530]
(0x03000000:NameValue):MessageSet = NULL
(0x03000000:NameValue):MessageType = NULL
(0x03000000:NameValue):MessageFormat = NULL
(0x03000000:NameValue):Encoding = NULL
(0x03000000:NameValue):CodedCharSetId = NULL
(0x03000000:NameValue):Transactional = NULL
(0x03000000:NameValue):Persistence = NULL
(0x03000000:NameValue):CreationTime = NULL
(0x03000000:NameValue):ExpirationTime = NULL
(0x03000000:NameValue):Priority = NULL
(0x03000000:NameValue):ReplyIdentifier = NULL
(0x03000000:NameValue):ReplyProtocol = 'MQ' (CHARACTER)
(0x03000000:NameValue):Topic = NULL
(0x03000000:NameValue):ContentType = NULL
(0x03000000:NameValue):IdentitySourceType = NULL
(0x03000000:NameValue):IdentitySourceToken = NULL
(0x03000000:NameValue):IdentitySourcePassword = NULL
(0x03000000:NameValue):IdentitySourceIssuedBy = NULL
(0x03000000:NameValue):IdentityMappedType = NULL
(0x03000000:NameValue):IdentityMappedToken = NULL
(0x03000000:NameValue):IdentityMappedPassword = NULL
(0x03000000:NameValue):IdentityMappedIssuedBy = NULL
)
(0x01000000:Folder):XMLNSC = ( ['xmlnsc' : 0x6f1b4d0]
(0x03000000:PCDataField):aDate = TIMESTAMP '2013-09-24 21:19:05' (TIMESTAMP)
)
)

----------------------------------------------------

Second ESQL
=========

DECLARE newDate TIMESTAMP InputRoot.XMLNSC.aDate;

SET OutputRoot.XMLNSC.Data.abcd[] = (select t.tl_sys_time
FROM Database.iris.tlog_view as t
WHERE
CAST(t.tl_sys_time as TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss') = CAST(newDate as TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss')
);

Trace after it
----------------

Never reach on this trace......................................

i can get "newDate" on 2nd esql fine but dont know whats wrong with this simple query not returning result ever!!!!........

appreciate if you can help me to resolve this issue.......

lancelotlinc

Posted: Wed Sep 25, 2013 10:39 am Post subject:

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

I'm not sure about this line: you may try to rework it.

Code:

CAST(t.tl_sys_time as TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss') = CAST(newDate as TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss')

Instead of in-lining this SQL, use ESQL variables to contain the value before you get to the database operation and use those variables in the database operation. Also, I do not see why there is a need to CAST t.tl_sys_time. Remove that CAST. Just use t.tl_sys_time plainly.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER

dogorsy

Posted: Wed Sep 25, 2013 10:13 pm Post subject:

Knight

Joined: 13 Mar 2013
Posts: 553
Location: Home Office

mzm.shan wrote:

newDate is declared as TIMESTAMP, no need to CAST it.
tl_sys_time is probably defined in the database as a timestamp, so no need to CAST it.

Change the esql accordingly.
Use user trace and see why you are not getting anything.
It maybe that the condition is not met.
Try changing the equal sign to greater than or equal, less than or equal, see what you get.

mzm.shan

Posted: Wed Sep 25, 2013 11:43 pm Post subject:

Apprentice

Joined: 25 Aug 2013
Posts: 39

issue resolved...!!! the following code works after enabling the check in ODBC seting of "Enable SQLDescribParam".

DECLARE newDate TIMESTAMP InputRoot.XMLNSC.aDate;

SET newDate = CAST(newDate as TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss');

SET OutputRoot.XMLNSC.Data.abcd[] = (select t.tl_sys_time
FROM Database.iris.tlog_view as t
WHERE t.tl_sys_time = newDate

Thanks every one!

dogorsy

Posted: Thu Sep 26, 2013 12:03 am Post subject:

Knight

Joined: 13 Mar 2013
Posts: 553
Location: Home Office

mzm.shan wrote:

DECLARE newDate TIMESTAMP InputRoot.XMLNSC.aDate;

SET newDate = CAST(newDate as TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss');

I don't know what your InputRoot.XMLNSC.aDate looks like, but you do not need both statements above.
If your input aDate is a character string 'yyyy-MM-dd' then simply

Code:

DECLARE newDate TIMESTAMP CAST(InputRoot.XMLNSC.aDate AS TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss');

and don't need the SET.

If your input aDate is already a TIMESTAMP data type, then just

Code:

DECLARE newDate TIMESTAMP InputRoot.XMLNSC.aDate;

and again, don't need the SET

smdavies99

Posted: Thu Sep 26, 2013 12:24 am Post subject:

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.

Some people consider doing anything apart from setting an initial value on a declaration to be not the best practice, unless you are an old 'C' hacker who felt that the only challenge in coding was to use as few lines in the source code as possible.

Personally, I see nothing wrong with

Code:

DECLARE myThnigy CHARACTER '';

... {sometime later in the code}
if myThingy = '' then
SET myThingy ='Hello World';
end if;

_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995

Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions.

dogorsy

Posted: Thu Sep 26, 2013 12:40 am Post subject:

Knight

Joined: 13 Mar 2013
Posts: 553
Location: Home Office

smdavies99 wrote:

Code:

DECLARE myThnigy CHARACTER '';

... {sometime later in the code}
if myThingy = '' then
SET myThingy ='Hello World';
end if;

Apart from the misspelling of myThnigy...!!...

The point I was trying to make is that the OP needs to understand the ESQL data types and the CAST function, as in a few places he is CASTing timestamp into timestamp.

mzm.shan

Posted: Thu Sep 26, 2013 1:04 am Post subject:

Apprentice

Joined: 25 Aug 2013
Posts: 39

yes... i could initiate it on declaration.. but this was only for making myself sure with few more line of code...

I am a new bee IIB/WMB so i am in learning mode... so i will write and break more line of code just for being sure and understand more rather being very smart and efficient.....

in this case wanted to understand Oracle DATE datatype behavior with ESQL TIMESTAMP datatype.

Many thanks for your valuable comments.... its really helping me.

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Select statement in esql with oracle Date filter.

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP