| Author |
Message
|
| petervh1 |
 Posted: Wed Nov 11, 2020 10:42 pm Post subject: SELECT statement not returning rows |
 |
|
Centurion
Joined: 19 Apr 2010
Posts: 140
|
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 |
|
 |
| abhi_thri |
| Posted: Wed Nov 11, 2020 11:10 pm Post subject: Re: SELECT statement not returning rows |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
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 |
|
|
| petervh1 |
| Posted: Wed Nov 11, 2020 11:12 pm Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 140
|
Apologies - the ESQL should read:
| Code: |
| SET AuthKey.Key[] = (SELECT S FROM Database.EVENT AS S WHERE S.ID = 'AuthorisationKey') |
|
|
| Back to top |
|
|
| petervh1 |
| Posted: Wed Nov 11, 2020 11:36 pm Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 140
|
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 |
|
|
| abhi_thri |
| Posted: Thu Nov 12, 2020 1:37 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
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 |
|
|
| petervh1 |
| Posted: Thu Nov 12, 2020 1:47 am Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 140
|
| 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 |
|
|
| abhi_thri |
| Posted: Thu Nov 12, 2020 1:56 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
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 |
|
|
| petervh1 |
| Posted: Thu Nov 12, 2020 2:12 am Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 140
|
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 |
|
|
| abhi_thri |
| Posted: Thu Nov 12, 2020 2:27 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
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 |
|
|
| petervh1 |
| Posted: Thu Nov 12, 2020 2:34 am Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 140
|
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 |
|
|
| abhi_thri |
| Posted: Thu Nov 12, 2020 2:52 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
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 |
|
|
|
|
