| Author |
Message
|
| francoisvdm |
 Posted: Fri Feb 09, 2007 3:52 am Post subject: limit data from database |
 |
|
Partisan
Joined: 09 Aug 2001
Posts: 332
|
Hi all
MB V6 on Windows.
I'm not database skilled, so please forgive my "ignorance" of this subject. I did search this forum, but alas, no answer that I can understand.
I've got a messageflow that needs to retrieve records from a database. No problem there, but how do I limit the retrieval to the "latest 100" records that was added to the DB. One of the fields in the record is a message broker "TIMESTAMP" field, and the data in that fields looks like
"2007-02-09 12:35:26.234456"
I think I must use a complicated WHERE statement in the SELECT.... but with my limited understanding of SQL and databases I can't seem to get it right.
Anybody got a sample for me?
Thanks
_________________
If you do not know the answer or you get the urge to answer with "RTFM" or "Search better in this forum", please refrain from doing so, just move on to the next question. Much appreciated.
Francois van der Merwe |
|
| Back to top |
|
 |
| francoisvdm |
| Posted: Fri Feb 09, 2007 4:02 am Post subject: |
|
|
Partisan
Joined: 09 Aug 2001
Posts: 332
|
Is there maybe a field I can add to the DB to make this selection easier? Also, say I only want to select the last 2 days worth of data?
Thanks
_________________
If you do not know the answer or you get the urge to answer with "RTFM" or "Search better in this forum", please refrain from doing so, just move on to the next question. Much appreciated.
Francois van der Merwe |
|
| Back to top |
|
|
| AkankshA |
| Posted: Fri Feb 09, 2007 4:05 am Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
Hi
you need to use Pasthru here as MB does not support complex select queries..
your query should be like
Select * from tablename where where rownum < 101 ORDER BY LAST_STATUS_UPDATED_DATE;
_________________
Cheers |
|
| Back to top |
|
|
| AkankshA |
| Posted: Fri Feb 09, 2007 4:11 am Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
To select the data of last two days
Select * from tablename where where LAST_STATUS_UPDATED_DATE > '2 days before date'
_________________
Cheers |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Feb 09, 2007 4:18 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Okay.
You probably do have to use Passthru.
That means you have to use a database specific query, and can't use an ESQL query.
To return all rows newer than ten days old has different syntax for different databases. For Oracle 9/10, for example, you need to say
Select * from tablename where CURRENT_TIMESTAMP - tablename.datefield < Interval '10' day
For DB2 this is going to be slightly different, and so on.
To return the top X rows from a given database is even more difficult. In Oracle you need to compare ROWNUM (which is an oracle constant field, not a table column). SQLServer lets you use TOP x on the select, and other databases may do the same.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Feb 09, 2007 4:20 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Also, there is a bug in the Toolkit 6.0.2.0 (at least) on the syntax of the PASSTHRU statement in some cases. It will report a syntax error for any invocation of the PASSTHRU when used as function AND when the statement does not include a TO <database ref> clause.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| francoisvdm |
| Posted: Fri Feb 09, 2007 4:37 am Post subject: |
|
|
Partisan
Joined: 09 Aug 2001
Posts: 332
|
Thanks all. For now the DB is DB2, but next week I must change it to Oracle, so it is good to see both sides.
I've tried
PASSTHRU 'SELECT * FROM COL WHERE rownum < 5 ORDER BY TIMESTAMP' TO Database.MYDB;
but get
Text = [IBM][CLI Driver][DB2/NT] SQL0206N "ROWNUM" is not valid in the context where it is used. SQLSTATE=42703
Thanks
_________________
If you do not know the answer or you get the urge to answer with "RTFM" or "Search better in this forum", please refrain from doing so, just move on to the next question. Much appreciated.
Francois van der Merwe |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Feb 09, 2007 4:39 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Yeah.
You can't use ROWNUM on DB2. I think DB2 supports TOP?
This is the problem with having to use PASSTHRU - you're not isolated from the underlying database.
You can use user defined properties to hold your passthru statements and control what gets used at deploy time.
Otherwise you need to change your code for each database.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Feb 09, 2007 4:42 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
You need something like
select * from whatever UP TO X rows
Order timestamp descending
Exact syntax dependant on your DB.
Enjoy 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Feb 09, 2007 4:59 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
|
| Back to top |
|
|
| francoisvdm |
| Posted: Mon Feb 12, 2007 2:46 am Post subject: |
|
|
Partisan
Joined: 09 Aug 2001
Posts: 332
|
Thanks all, here is the solution for DB2.
DECLARE myVar CHARACTER;
SET myVar = 'SELECT * FROM DUP order by TIMESTAMPWEBSPHEREMB DESC fetch first 100 rows only';
SET OutputRoot.XMLNS.Data.Transaction[] = PASSTHRU(myVar)
now I must just get the Oracle portion sorted out.
_________________
If you do not know the answer or you get the urge to answer with "RTFM" or "Search better in this forum", please refrain from doing so, just move on to the next question. Much appreciated.
Francois van der Merwe |
|
| Back to top |
|
|
|
|
