ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » esql statement issue

Post new topic  Reply to topic
 esql statement issue « View previous topic :: View next topic » 
Author Message
Raj2000
PostPosted: Wed Sep 16, 2009 9:13 am    Post subject: esql statement issue Reply with quote

Apprentice

Joined: 03 Aug 2009
Posts: 47

I have an issue regarding using esql statement to query the database.

issue:
Actually the database table has 3 coloumns they are:
C1 is c_ts which records current timestamp
C2 is source which records the source(CHARACTER TYPE)
C3 is status which records the status(CHARACTER)

I want to query the table such that it retrieves the row which contains source modified very recently and this should be done for different Source(C2)

the above is what i have to achieve

what i have tried:(SOURCESTATUS is the table)

SET Environment.X[]= SELECT R FROM Database.SOURCESTATUS AS R WHERE R.SOURCE= 'S_ID' AND R.C_TS=(SELECT MAX(R.CURRENT_TS)FROM Database.SOURCESTATUS AS R);

but it doen't work.

Do you think is there anything wrong with the above code???
even i can't use group function.So any suggestions???

note: the above task i think can be achieved in java using the group keyword.
Back to top
View user's profile Send private message
Raj2000
PostPosted: Wed Sep 16, 2009 9:46 pm    Post subject: Reply with quote

Apprentice

Joined: 03 Aug 2009
Posts: 47

Can anyone suggest me the workaround to come across this issue as we cannot use the group functionality in esql and without using the group is ther anyother way we can achieve the above task?
Back to top
View user's profile Send private message
elvis_gn
PostPosted: Wed Sep 16, 2009 9:57 pm    Post subject: Reply with quote

Padawan

Joined: 08 Oct 2004
Posts: 1905
Location: Dubai

Hi Raj2000,

First, your SQL statement.
1. Do you really need another select....wouldn't a direct MAX on the field work ? i.e MAX(R.C_TS)
2. Should it be SELECT R FROM or SELECT * FROM or SELECT R.* FROM...i could be wrong, been some time since i've coded

Second, on options.
1. If nothing works, you could try using PASSTHRU.
2. If the SQL is complex, I always prefer using a stored procedure.

Regards.
Back to top
View user's profile Send private message Send e-mail
Raj2000
PostPosted: Thu Sep 17, 2009 9:15 am    Post subject: Reply with quote

Apprentice

Joined: 03 Aug 2009
Posts: 47

Yes elvis, i have tried the following statement as you have said but it says syntax error

statement:

SET Environment.X[]=SELECT MAX(R.CURRENT_TS), R.SOURCE_ID, R.STATUS FROM Database.SOURCESTATUS AS R WHERE R.SOURCE_ID = 'S_ID';

and it point at ',' saying as syntax error.

elvis as i pretty new to this can you give me an example of usage of passthru in this context (i have researched on this statment but it helps to someextent from where i don't get any idea ) sorryy
Back to top
View user's profile Send private message
mgk
PostPosted: Thu Sep 17, 2009 9:35 am    Post subject: Reply with quote

Padawan

Joined: 31 Jul 2003
Posts: 1642

Quote:
but it doen't work.

What doesn't work? Is there an error message? If so post it...

Quote:
Do you think is there anything wrong with the above code???

Well both your SELECTS use the same name "R" which will confuse things... try renaming the nested SELECT to be "AS X" or some other name...

Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.
Back to top
View user's profile Send private message
Raj2000
PostPosted: Thu Sep 17, 2009 10:06 am    Post subject: Reply with quote

Apprentice

Joined: 03 Aug 2009
Posts: 47

i don't get any error the onlything is environment variable is blank

hello MGK i have tried what you have said this time it executes the query but it doesn't yield any result as query searches for the whole max timestamp and for that row S_ID is not the sourceid so it gives no result.

But what is wrong with the following statement asit says syntax error:

SET Environment.X[]=SELECT MAX(R.CURRENT_TS), R.SOURCE_ID, R.STATUS FROM Database.SOURCESTATUS AS R WHERE R.SOURCE_ID = 'S_ID';

i am still not able to solve the issue that i have said initially
is ther any workaround so that i can solve the issue without using the group statement (as this is not supoorted in ESQL)???
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Thu Sep 17, 2009 12:16 pm    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY

You really should review your standard SQL.
Quote:
SET Environment.X[]=SELECT MAX(R.CURRENT_TS), R.SOURCE_ID, R.STATUS FROM Database.SOURCESTATUS AS R WHERE R.SOURCE_ID = 'S_ID';
is missing the mandatory group by r.source_id, r.status for this type of select...

Enjoy
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
Raj2000
PostPosted: Thu Sep 17, 2009 1:35 pm    Post subject: Reply with quote

Apprentice

Joined: 03 Aug 2009
Posts: 47

Quote:
the mandatory group by r.source_id, r.status for this type of select...


Yes i do undersatnd that saper but Esql doesnot support "group" statement.
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Thu Sep 17, 2009 7:29 pm    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY

Raj2000 wrote:
Quote:
the mandatory group by r.source_id, r.status for this type of select...


Yes i do undersatnd that saper but Esql doesnot support "group" statement.

So you need a pass through. ESQL never pretended it could do all a SQL statement could... when talking about databases...
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
Raj2000
PostPosted: Fri Sep 18, 2009 9:41 am    Post subject: Reply with quote

Apprentice

Joined: 03 Aug 2009
Posts: 47

I am really sorry Saper i shoudn't have asked this but can you give me a example of pass thru which can be used in this context??
Back to top
View user's profile Send private message
smdavies99
PostPosted: Fri Sep 18, 2009 9:58 am    Post subject: Reply with quote

Jedi Council

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

Try something like this
Code:


 declare SQLCommand character;
 set SQLCommand = 'SELECT MAX(R.CURRENT_TS), R.SOURCE_ID, R.STATUS FROM Database.SOURCESTATUS AS R WHERE R.SOURCE_ID = 'S_ID';

 PASSTHRU (SQLCommand);
 IF SQLERROR <> 0 Then
  ...
 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.
Back to top
View user's profile Send private message
Luke
PostPosted: Fri Sep 18, 2009 10:00 am    Post subject: Reply with quote

Centurion

Joined: 10 Nov 2008
Posts: 128
Location: UK

Hi Raj

Did you take a look at the example in the infocenter? ...

Code:
SET OutputRoot.XML.Data.SelectResult.Row[] =
  PASSTHRU('SELECT R.* FROM Schema1.Table1 AS R WHERE R.Name = ? OR R.Name =
           ? ORDER BY Name'
   TO Database.DSN1
   VALUES ('Name1', 'Name4'));


Shouldn't be too difficult to adapt this to your needs?
Back to top
View user's profile Send private message
mqjeff
PostPosted: Fri Sep 18, 2009 10:03 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

Steven - that's ESQL SELECT syntax in an SQL statement.

Raj2000 - If you modify your original nested select to use different Database qualifiers "AS A" "AS B", you may have better luck.

I think you want something like
SELECT A.* Database.SOURCESTATUS AS A WHERE A.SOURCE= 'S_ID' AND A.C_TS=(SELECT MAX(B.CURRENT_TS) FROM Database.SOURCESTATUS AS B WHERE B.SOURCE='S_ID') ;
Back to top
View user's profile Send private message
smdavies99
PostPosted: Fri Sep 18, 2009 12:14 pm    Post subject: Reply with quote

Jedi Council

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

Thanks mqjeff for pointing out my elementary mistake.

Luckily, I'm off on holiday tomorrow. I guess I need it
_________________
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.
Back to top
View user's profile Send private message
Raj2000
PostPosted: Fri Sep 18, 2009 6:46 pm    Post subject: Reply with quote

Apprentice

Joined: 03 Aug 2009
Posts: 47

Quote:
you may have better luck.


Jeff I had LUCK!!! you did it!! It works!!!!
Thank you much!!

Thanks alot guys you all made my day i had waited almost 2 days for this result and i had skipped this part of work and proceeded for the other work which is linked to this problem Hoping that someone would help me out here . this forum ROCKZZZ and you people too!!!

Saper,Luke,Davies thanks alot for your valuable input..

NOW i can have nice weekend!!!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » esql statement issue
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
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.