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 » OUTER JOIN, PASSTHRU

Post new topic  Reply to topic
 OUTER JOIN, PASSTHRU « View previous topic :: View next topic » 
Author Message
svu
PostPosted: Fri May 04, 2007 5:29 am    Post subject: OUTER JOIN, PASSTHRU Reply with quote

Voyager

Joined: 30 Jan 2006
Posts: 99

Hi all

A couple of questions regarding ESQL and database.

I have to do OUTER JOIN in my SQL statement executed in ESQL. Initially I tried using WHERE NOT EXISTS (SELECT ....) - it does not work (getting internal broker error). Tried OUTER JOIN - the result was same.

So, the question #1 - is it possible without PASSTHRU and doing it manually (by executing two independent SELECTs and iterating through the results)? I'm afraid I know the answer.

So, for a moment I put it all into PASSTHRU. And I found I cannot use the VALUE clause without specifying the database source (like in "... TO Database.DSN ..."). Syntax diagrams show that TO clause is optional. In reality if I add VALUES without TO - I get the red mark (non-compilable). If I remove VALUES - it is all fine. If I add database source - it is all fine as well... But I do not want to hardcode the data source name in ESQL - it is specified as the property of the compute node (and can be changed once the code is deployed into production).

My question #2: Anyone encountered that problem? Anyone knows anything about it?

Thanks in advance
Back to top
View user's profile Send private message
jefflowrey
PostPosted: Fri May 04, 2007 5:41 am    Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

#1 - Yes, it's possible. Sounds painful, though.

#2 - there are some bugs in the Toolkit in this area, I believe. If I recall, it depends on if you're trying to assign the result to something or not.

After completely confirming that your syntax matches the documentation, you can open a PMR.

Or you can put your statement in Executeable Comments to avoid the Toolkit complaining.
_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
svu
PostPosted: Fri May 04, 2007 5:47 am    Post subject: Reply with quote

Voyager

Joined: 30 Jan 2006
Posts: 99

jefflowrey wrote:
#1 - Yes, it's possible. Sounds painful, though.

Could you please provide an example? I tried all syntaxes I could invent.

Quote:
#2 - there are some bugs in the Toolkit in this area, I believe. If I recall, it depends on if you're trying to assign the result to something or not.

Yes, I am trying to assign the result (why would I do SELECT?

Quote:
After completely confirming that your syntax matches the documentation, you can open a PMR.

Well, may be we'll do it after all

Quote:
Or you can put your statement in Executeable Comments to avoid the Toolkit complaining.

But I would have to compile it anyway, wouldn't I? I will try of course...

Thanks for the food for thought..

UPD: Tried to find anything about Executable Comments in the help - could not. What kind of beast is it?
Back to top
View user's profile Send private message
jefflowrey
PostPosted: Fri May 04, 2007 7:03 am    Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/index.jsp?topic=/com.ibm.etools.mft.doc/ak04870_.htm

Executeable comments, down at the bottom.

I meant you could perform regular join using ESQL Select, and then manually pull out the stuff you don't want. Or do anyother select that only pulled out rows where all columns from one table or the other were not null.

But I haven't done it.
_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
svu
PostPosted: Fri May 04, 2007 7:11 am    Post subject: Reply with quote

Voyager

Joined: 30 Jan 2006
Posts: 99

jefflowrey wrote:
http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/index.jsp?topic=/com.ibm.etools.mft.doc/ak04870_.htm
Executeable comments, down at the bottom.

Thanks, I'll have a look.

Quote:
I meant you could perform regular join using ESQL Select, and then manually pull out the stuff you don't want. Or do anyother select that only pulled out rows where all columns from one table or the other were not null.

Yes, I already mentioned "manual outer join". Sure I have this option - but I'd prefer not to examine it.

So it seems the only realistic way for me so far is PASSTHRU without VALUE (i.e. composing entire query string, including parameters, manually). Or with the VALUE and executable comments (if it works).
Back to top
View user's profile Send private message
jefflowrey
PostPosted: Fri May 04, 2007 7:14 am    Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

Or open a PMR. There may be a fix available.
_________________
I am *not* the model of the modern major general.
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 » OUTER JOIN, PASSTHRU
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.