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 IndexWebSphere Message Broker SupportSELECT statement not returning rows

Post new topicReply to topic
SELECT statement not returning rows View previous topic :: View next topic
Author Message
petervh1
PostPosted: Wed Nov 11, 2020 10:42 pm Post subject: SELECT statement not returning rows Reply with quote

Acolyte

Joined: 19 Apr 2010
Posts: 64

Hello

IIB 10.0.0.15 and Oracle

I am getting an error when I try to SELECT a row from an Oracle DB:

Code:
BIP2570W: There were no items in the FROM clause satisfying the WHERE clause.


My ESQL is:
Code:
SET AuthKey.Key[] = (SELECT S FROM Database.EVENT AS S WHERE S.MESSAGETYPE = 'AuthorisationKey')


There is definitely a single row in the Oracle DB with a column entitled ID which has a value of 'AuthorisationKey'.

If I change my ESQL to:
Code:
SET AuthKey.Key[] = PASSTHRU ('SELECT * FROM ( SELECT * FROM event ORDER BY created desc ) WHERE ROWNUM <= 10' TO Database.ESBDB);


I get back 10 rows, as expected, so there's nothing wrong with the Data source connection.

I'm probably making a basic error, but I just can't see it.

TIA
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Wed Nov 11, 2020 11:10 pm Post subject: Re: SELECT statement not returning rows Reply with quote

Partisan

Joined: 17 Jul 2017
Posts: 398
Location: UK

petervh1 wrote:


My ESQL is:
Code:
SET AuthKey.Key[] = (SELECT S FROM Database.EVENT AS S WHERE S.MESSAGETYPE = 'AuthorisationKey')


There is definitely a single row in the Oracle DB with a column entitled ID which has a value of 'AuthorisationKey'.


hi...any specific reason that all the columns need to be retrieved from the table, usually it is recommended just to retrieve the ones you need to make the query more optimal. For eg:- there may be columns you won't use, timestamp ones or if some one adds new columns in future that will also get pulled into even though it is not used in the code.

Anyway, the Select query is using 'MESSAGETYPE' in where clause but your futher comment says Table has this value at column ID...If that is correct isn't it just the matter of replacing MESSAGETYPE with ID in the Select query.

Also if the above doesn't work I suggest you remove the Where clause altogher and see what happens.
Back to top
View user's profile Send private message
petervh1
PostPosted: Wed Nov 11, 2020 11:12 pm Post subject: Reply with quote

Acolyte

Joined: 19 Apr 2010
Posts: 64

Apologies - the ESQL should read:
Code:
SET AuthKey.Key[] = (SELECT S FROM Database.EVENT AS S WHERE S.ID = 'AuthorisationKey')
Back to top
View user's profile Send private message
petervh1
PostPosted: Wed Nov 11, 2020 11:36 pm Post subject: Reply with quote

Acolyte

Joined: 19 Apr 2010
Posts: 64

I ran:
Code:
SET AuthKey.Key[] = (SELECT S.ID FROM Database.EVENT AS S WHERE S.ID = 'AuthorisationKey')


Same result:
Code:
BIP2570W: There were no items in the FROM clause satisfying the WHERE clause.


I can't remove the WHERE clause entirely as there are too many rows in the table.
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Thu Nov 12, 2020 1:37 am Post subject: Reply with quote

Partisan

Joined: 17 Jul 2017
Posts: 398
Location: UK

hmmm...can you please try the below and see,

- You might've already checked this multiple times but still crosscheck the DSN configured at the ComputeNode to ensure that is the same one as used in the PASSTHRU... just to ensure that you are hitting the same database.

- Try executing the same query directly on the database to ensure that it is returning a row,
Code:
SELECT ID FROM EVENT WHERE ID = 'AuthorisationKey';


- What if you change the where clause to a different column

- Collect the user trace for the current query and post the results here (use <code> tags please)
Back to top
View user's profile Send private message
petervh1
PostPosted: Thu Nov 12, 2020 1:47 am Post subject: Reply with quote

Acolyte

Joined: 19 Apr 2010
Posts: 64

Quote:
Try executing the same query directly on the database to ensure that it is returning a row,


Returns:
Code:
AuthorisationKey      2      ABC   4   5   45   04-NOV-20 09.56.27.545122000 AM   N   TISS   AFR   dynamicVariable1   detailLarge   Y


Quote:
What if you change the where clause to a different column


I get the same result

Quote:
Collect the user trace for the current query and post the results here (use <code> tags please)


Code:
2020-11-12 12:35:52.577272     7818   UserTrace   BIP2544I: Node 'MF_TISS_T1.Compute': Executing database SQL statement ''SELECT S.ID FROM EVENT S WHERE (S.MESSAGETYPE)=('ABC')'' derived from ('', '1.1'); expressions ''''; resulting parameter values ''''.
2020-11-12 12:35:52.577284     7818   UserTrace   BIP12074I: Executing a database statement for ''ESBDB''.
                                       Executing a database statement against data source ''ESBDB''.
2020-11-12 12:35:52.577488     7818   UserTrace   BIP12075I: Executed a database statement for ''ESBDB''.
                                       Executed a database statement against data source ''ESBDB''.
2020-11-12 12:35:52.577500     7818   UserTrace   BIP2539I: Node '': Evaluating expression ''DATABASE()'' at ('', '1.1'). This resolved to ''SELECT S.ID FROM EVENT S WHERE (S.MESSAGETYPE)=('ABC')''. The result was ''Complex result''.
2020-11-12 12:35:52.577520     7818   UserTrace   BIP2570W: Node 'MF_TISS_T1.Compute': ('.MF_TISS_T1_Compute.Main', '10.31') : There were no items in the FROM clause satisfying the WHERE clause.
2020-11-12 12:35:52.577528     7818   UserTrace   BIP2562I: Node 'MF_TISS_T1.Compute': Assigning a list to ''AuthKey.Key[]''
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Thu Nov 12, 2020 1:56 am Post subject: Reply with quote

Partisan

Joined: 17 Jul 2017
Posts: 398
Location: UK

petervh1 wrote:

Code:

2020-11-12 12:35:52.577500     7818   UserTrace   BIP2539I: Node '': Evaluating expression ''DATABASE()'' at ('', '1.1'). This resolved to ''SELECT S.ID FROM EVENT S WHERE (S.[b]MESSAGETYPE[/b])=('ABC')''. The result was ''Complex result''.
2020-11-12 12:35:52.577520     7818   UserTrace   BIP2570W: Node 'MF_TISS_T1.Compute': ('.MF_TISS_T1_Compute.Main', '10.31') : There were no items in the FROM clause satisfying the WHERE clause.
2020-11-12 12:35:52.577528     7818   UserTrace   BIP2562I: Node 'MF_TISS_T1.Compute': Assigning a list to ''AuthKey.Key[]''


hi...the actual deployed code is still using MESSAGETYPE in the Where clause
Back to top
View user's profile Send private message
petervh1
PostPosted: Thu Nov 12, 2020 2:12 am Post subject: Reply with quote

Acolyte

Joined: 19 Apr 2010
Posts: 64

Sorry, I should have run it again with the original SELECT statement:

Code:
2020-11-12 13:09:12.588740     7818   UserTrace   BIP2538I: Node 'MF_TISS_T1.Compute': Evaluating expression ''(SELECT S.EVENT.ID AS ID FROM Database.EVENT AS S WHERE S.EVENT.ID = 'AuthorisationKey')'' at ('.MF_TISS_T1_Compute.Main', '9.31').
2020-11-12 13:09:12.588749     7818   UserTrace   BIP2573W: Node 'MF_TISS_T1.Compute': ('.MF_TISS_T1_Compute.Main', '9.31') : Finding first SELECT result.
2020-11-12 13:09:12.588767     7818   UserTrace   BIP2544I: Node 'MF_TISS_T1.Compute': Executing database SQL statement ''SELECT S.ID FROM EVENT S WHERE (S.ID)=('AuthorisationKey')'' derived from ('', '1.1'); expressions ''''; resulting parameter values ''''.
2020-11-12 13:09:12.588796     7818   UserTrace   BIP12074I: Executing a database statement for ''ESBDB''.
                                       Executing a database statement against data source ''ESBDB''.
2020-11-12 13:09:12.590393     7818   UserTrace   BIP12075I: Executed a database statement for ''ESBDB''.
                                       Executed a database statement against data source ''ESBDB''.
2020-11-12 13:09:12.590428     7818   UserTrace   BIP2539I: Node '': Evaluating expression ''DATABASE()'' at ('', '1.1'). This resolved to ''SELECT S.ID FROM EVENT S WHERE (S.ID)=('AuthorisationKey')''. The result was ''Complex result''.
2020-11-12 13:09:12.590468     7818   UserTrace   BIP2570W: Node 'MF_TISS_T1.Compute': ('.MF_TISS_T1_Compute.Main', '9.31') : There were no items in the FROM clause satisfying the WHERE clause.
2020-11-12 13:09:12.590480     7818   UserTrace   BIP2562I: Node 'MF_TISS_T1.Compute': Assigning a list to ''AuthKey.Key[]''.
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Thu Nov 12, 2020 2:27 am Post subject: Reply with quote

Partisan

Joined: 17 Jul 2017
Posts: 398
Location: UK

petervh1 wrote:

Code:
2020-11-12 13:09:12.588740     7818   UserTrace   BIP2538I: Node 'MF_TISS_T1.Compute': Evaluating expression ''(SELECT S.EVENT.ID AS ID FROM Database.EVENT AS S WHERE S.EVENT.ID = 'AuthorisationKey')'' at ('.MF_TISS_T1_Compute.Main', '9.31').


hi...the above query doesn't look correct, shouldn't it be the below one instead

Code:
(SELECT S.ID AS ID FROM Database.EVENT AS S WHERE S.ID = 'AuthorisationKey')
Back to top
View user's profile Send private message
petervh1
PostPosted: Thu Nov 12, 2020 2:34 am Post subject: Reply with quote

Acolyte

Joined: 19 Apr 2010
Posts: 64

Hi

I think that's just the way that the trace utility 'reformats' the query in the trace log. The actual ESQL statement that I used was:
Code:
SET AuthKey.Key[] = (SELECT S.ID FROM Database.EVENT AS S WHERE S.ID = 'AuthorisationKey');


Thanks for your help so far
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Thu Nov 12, 2020 2:52 am Post subject: Reply with quote

Partisan

Joined: 17 Jul 2017
Posts: 398
Location: UK

hmmm...strange, you've already done the main checks,
- The Database table dp have the row that the Select statement is querying for
- The DSN as such works

More queries,

- Is this a fresh IIB installation at all? if you have ran the 'iib verify all' command, also worth running the 'mqsicvp' command for DSN verification.

- Can you post the obdc DSN stanza here

- Are there other flows which uses SELECT queries, if so are those working?

- Are there any other table you can just try the Select without the where clause?
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportSELECT statement not returning rows
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.