| Author |
Message
|
| Meow |
 Posted: Wed Dec 31, 2003 9:23 am Post subject: Problem using ORDER BY Clause |
 |
|
Voyager
Joined: 25 Jun 2003
Posts: 95
|
I am having problem in using the ORDER BY Clause. I want to order the results based on a particular column in the database. This is what i am executing
EVAL('SET Environment.Variables.Query.Response[] = (SELECT T.* FROM Database."SAMPLE.EMPLOYEE" AS T '||' '||Stmt||')' );
Stmt = WHERE T.EMP_DEPT = '10' ORDER BY T.EMP_NO DESC
I am getting the follwoing error
(0x1000000)RecoverableException = (
(0x3000000)File = ''
(0x3000000)Line = 0
(0x3000000)Function = ''
(0x3000000)Type = ''
(0x3000000)Name = ''
(0x3000000)Label = ''
(0x3000000)Text = 'Expected ')''
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2401
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '1'
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '161'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ')'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = 'integer ORDER'
)
)
Thanks |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Wed Dec 31, 2003 9:27 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
The ESQL statement "Select" does not have an ORDER BY clause, at least in 2.1.
You'll have to use PASSTHRU.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Meow |
| Posted: Wed Dec 31, 2003 9:40 am Post subject: |
|
|
Voyager
Joined: 25 Jun 2003
Posts: 95
|
i knew that but how do i sort my results then.
Please let me know. If i am using PASSTHRU it throwing me the Database undefined error.
we are having the database on mainframes and our broker has the access to those tables.
I tried these
PASSTHRU (Stmt)
where Stmt = SELECT T.* FROM SAMPLE.EMPLOYEE AS T WHERE T.EMP_DEPTNO = '10' ORDER BY T.EMP_NO DESC
this is the error
(0x1000000)DatabaseException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbOdbc.cpp'
(0x3000000)Line = 154
(0x3000000)Function = 'ImbOdbcHandle::checkRcInner'
(0x3000000)Type = ''
(0x3000000)Name = ''
(0x3000000)Label = ''
(0x3000000)Text = 'Root SQL exception'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2321
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '-1'
)
(0x1000000)DatabaseException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbOdbc.cpp'
(0x3000000)Line = 255
(0x3000000)Function = 'ImbOdbcHandle::checkRcInner'
(0x3000000)Type = ''
(0x3000000)Name = ''
(0x3000000)Label = ''
(0x3000000)Text = 'Child SQL exception'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2322
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = '42S02'
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '-204'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = '[IBM][CLI Driver][DB2] SQL0204N "SAMPLE.EMPLOYEE" is an undefined name. SQLSTATE=42704
'
)
)
thanks |
|
| Back to top |
|
|
| EddieA |
| Posted: Wed Dec 31, 2003 10:07 am Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Looks kinda like DB2 couldn't find a table called EMPLOYEE with a schema of SAMPLE.
Also, if DESC is one of the columns coming back, shouldn't it be T.DESC.
Hint: Try the command natively at a DB2 Command Prompt to make sure it's syntactically correct, and does what you expect. And connect as the WMQI user to do this.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| Meow |
| Posted: Wed Dec 31, 2003 11:27 am Post subject: |
|
|
Voyager
Joined: 25 Jun 2003
Posts: 95
|
DESC stands for Descending.
the schema and the table are present in DB2. It works fine when tried at the command prompt.
Say :
Schema name : Sample
Table name : Employee
is the passthru statement that i am using correct? WMQI Broker id has the access to that table.
My Code works fine with EVAL statement but i dont know how to incorporate the "ORDER BY" clause with the select statement as it is not supported with the select statement.
Please let me know.
Thanks |
|
| Back to top |
|
|
| EddieA |
| Posted: Wed Dec 31, 2003 1:39 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
OK, so I'm not a DB2 person. 
Looks like you may have the 'Quotes' wrong/missing in you Stmt clause. Please post the EXACT statement you use to build Stmt. And the PASSTHRU.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Dec 31, 2003 2:10 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
When working with databases and WMQI, I have usually found it at least as helpful to look at the database error logs as I have the WMQI error logs.
That is, try your statement and trace it on the DB2 side to see what DB2 thinks is coming through, rather than trying to "guess" at what WMQI needs to send.
Also, don't forget that your ESQL (even the passthru) will be executed by the WMQI database userId - which may have a different idea of schemas than the user you ran the commands manually has.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| seeknee |
| Posted: Fri Jan 09, 2004 3:22 am Post subject: |
|
|
Apprentice
Joined: 08 Aug 2002
Posts: 41
Location: Melbourne, Australia
|
Have you tried somthing like this
SET Environment.Stmt[] = PASSTHRU('SELECT * FROM SAMPLE.EMPLOYEE WHERE EMP_DEPTNO = ? ORDER BY EMP_NO DESC', '10' );
We use the PASSTHRU to order our resultset and ours looks something
like this
 |
|
| Back to top |
|
|
|
|
